Hologres：SELECT

命令介紹

命令格式

[ WITH with_query [, ...] ]
SELECT [ALL | DISTINCT [ON (expression [, ...])]]
  * | expression [[AS] output_name] [, ...]
  [FROM from_item [, ...]]
  [WHERE condition]
  [GROUP BY grouping_element [, ...]]
  [HAVING condition [, ...]]
  [{UNION | INTERSECT | EXCEPT} [ALL] select]
  [ORDER BY expression [ASC | DESC | USING operator] [, ...]]
  [LIMIT {count | ALL}]

命令用法

SELECT的常用方法如下：

1. FROM列表中的所有元素都會被計算（ FROM中的每一個元素都是一個真實表或者虛擬表）。如果在FROM列表中指定了多於一個元素，得到的結果做並集。

2. 如果指定了WHERE子句，所有不滿足該條件的行都會被從輸出中消除。

3. 如果指定了GROUP BY子句或者如果有聚集合函式，輸出會被組合成由在一個或者多個值上匹配的行構成的分組，並且在其上計算聚集合函式的結果。如果出現了HAVING子句，它會消除不滿足給定條件的分組。

4. 對於每一個被選中的行或者行組，會使用SELECT輸出運算式計算實際的輸出行。

5. 通過使用操作符UNION、INTERSECT和EXCEPT，多個SELECT語句的輸出可以被整合形成一個結果集。UNION操作符返回位於一個或者兩個結果集中的全部行。INTERSECT操作符返回同時位於兩個結果集中的所有行。EXCEPT操作符返回位於第一個結果集但不在第二個結果集中的行。在所有三種情況下，重複行都會被消除（除非指定ALL）。可以增加DISTINCT來顯式地消除重複行。注意雖然ALL是SELECT自身的預設行為，但這裡DISTINCT是預設行為。

6. 如果指定了ORDER BY子句，被返回的行會以指定的順序排序。如果沒有給定ORDER BY，系統會以能最快產生行的順序返回它們。

7. 如果指定了LIMIT（或FETCH FIRST） 或者OFFSET子句，SELECT語句只返回結果行的一個子集。

參數說明

• WITH列表

  • 命令簡介

    WITH列表是位於SELECT之前或者作為SELECT子句的subquery存在，通常位於SELECT之前，用於定義子查詢，並為子查詢聲明一個名字和返回的列名，定義每一個WITH子句為一個CTE（Common Table Expression），定義文法如下：

    with query_name [ ( column_name [, ...] ) ] AS ( select )

  • 參數說明

    參數	說明
    query_name	指定當前CTE的名字，可以是任意有效標識符。
    column_name	列表對應著子查詢傳回值的列名，類似於SELECT子句中的AS的語義，子查詢可以是一個常規的SELECT查詢。

    CTE之間通過逗號分隔，後面出現的CTE定義可以引用前面定義的CTE，但是目前暫時不支援遞迴的CTE調用，在之後的查詢中，可以直接將query_name作為一個視圖（view）出現在查詢中，如果沒有指定column_name列表，該view的column_name為對應子查詢返回列的列名，如果指定column_name列表，則需要使用定義column_name，並且column_name列表需要和SELECT傳回值列表個數相同。

• SELECT列表

  • 命令簡介

    SELECT列表（位於關鍵詞 SELECT和FROM之間）指定構成SELECT語句輸出行的運算式。這些運算式可以（並且通常確實會）引用FROM子句中計算得到的列。

  • 參數說明

    SELECT的每一個輸出資料行都有一個名稱。 在一個簡單的SELECT中，該名稱只用來標記要顯示的列，當SELECT為一個大型查詢的子查詢時，大型查詢會把該名稱看做子查詢產生的虛表的列名。要指定用於輸出資料行的名稱，在該列的運算式後面添加AS output_name（可以省略AS，但只能在期望的輸出名稱不匹配任何PostgreSQL關鍵詞時省略。為了避免和未來增加的關鍵詞衝突， 推薦您添加AS或者用雙引號引用輸出名稱）。如果不指定列名，PostgreSQL會自動選擇一個名稱。如果列的運算式是一個簡單的列引用，那麼被選擇的名稱就和該列的名稱相同。在使用函數或者類型名稱的更複雜的情況中，系統可能會產生諸如 ?column?之類的名稱。

    一個輸出資料行的名稱可以被用來在ORDER BY以及GROUP BY子句中引用該列的值，但是不能用於 WHERE和HAVING子句（在其中必須寫出運算式）。

    可以在輸出資料行表中寫*來取代運算式，它是被選中行的所有列的一種簡寫方式。還可以寫 %table_name*;，它是只來自那個表的所有列的簡寫形式。在這些情況中無法用AS指定新的名稱，輸出行的名稱將和表列的名稱相同。

• FROM子句

  • 命令簡介

    FROM子句為SELECT 指定一個或者更多源表。如果指定了多個源表，結果將是所有源表的笛卡爾積（交叉串連）。但是通常會增加限定條件（通過 WHERE）來把返回的行限制為該笛卡爾積的一個小子集。

  • 參數說明

    FROM子句可以包含下列元素：

    元素	說明
    table_name	一個現有表或視圖的名稱（可以限定表的schema模式）。
    alias	一個包含別名的FROM項的替代名稱。別名被用於讓書寫簡潔或者消除自串連中的混淆（其中同一個表會被掃描多次）。當提供一個別名時，表或者函數的實際名稱會被隱藏。例如，給定FROM foo AS f，SELECT的剩餘部分就必須以f而不是foo來引用這個 FROM項。
    select	一個子SELECT可以出現在 FROM子句中。這就好像把它的輸出建立為一個存在於該SELECT命令期間的暫存資料表。注意子SELECT必須用圓括弧包圍，並且必須為它提供一個別名。
    function_name	函數調用可以出現在FROM子句中（對於返回結果集合的函數特別有用，但是可以使用任何函數）。這就好像把該函數的輸出建立為一個存在於該SELECT命令期間的暫存資料表。 可以用和表一樣的方式提供一個別名。如果寫了一個別名，還可以寫一個列別名列表來為該函數的組合傳回型別的一個或者多個屬性提供替代名稱， 包括由ORDINALITY（如果有）增加的新列。 通過把多個函數調用包圍在ROWS FROM（。）中可以把它們整合在單個FROM-子句項中。這樣一個項的輸出是把每一個函數的第一行串接起來，然後是每個函數的第二行，以此類推。如果有些函數產生的行比其他函數少，則在缺失資料的地方放上 NULL，這樣被返回的總行數總是和產生最多行的函數一樣。
    join_type	包含如下5種類型： [ INNER ] JOIN 對於INNER和OUTER連線類型，必須指定一個串連條件，即 NATURAL、ON join_condition或者 USING （join_column [, ...]）之一（只能有一種）。其含義見下文。對於CROSS JOIN，上述子句不能出現。一個JOIN子句聯合兩個FROM項（為了方便我們稱之為“表“，儘管實際上它們可以是任何類型的FROM項）。如有必要可以使用圓括弧確定嵌套的順序。 在沒有圓括弧時，JOIN會從左至右嵌套。在任何情況下，JOIN的聯合比用逗號分隔FROM-清單項目更強。 LEFT [ OUTER ] JOIN LEFT OUTER JOIN返回被限制過的笛卡爾積中的所有行（即所有通過了其串連條件的組合行），以及左表中每行的一個副本，因為沒有右行通過了串連條件。通過在右手列中插入空值，這種左手行會被擴充為串連表的完整行。注意在決定哪些行匹配時，只考慮JOIN子句自身的條件。之後才應用外條件。 RIGHT [ OUTER ] JOIN RIGHT OUTER JOIN返回所有串連行，外加每一個沒有匹配上的右手行（在左端用空值擴充）。這隻是為了記號上的方便，因為可以通過交換左右表把它轉換成一個LEFT OUTER JOIN。 FULL [ OUTER ] JOIN FULL OUTER JOIN返回所有串連行，外加每一個沒有匹配上的左手行（在右端用空值擴充），再外加每一個沒有匹配上的右手行（在左端用空值擴充） CROSS JOIN CROSS JOIN和INNER JOIN 會產生簡單的笛卡爾積，也就是與在FROM列出兩個表得到的結果相同，但是要用串連條件（如果有）約束該結果。 CROSS JOIN與INNER JOIN ON （TRUE）等效，也就是說條件不會移除任何行。這些連線類型只是一種記號上的方便，因為沒有什麼是用純粹的FROM和 WHERE能做而它們不能做的。
    join_condition	join_condition是一個會得到boolean類型值的運算式（類似於一個WHERE子句），它說明一次串連中哪些行被認為相匹配。
    USING ( a, b, ... )	形式USING （a， b...）的子句是 ON left_table.a = right_table.a AND left_table.b = right_table.b ...的簡寫。還有， USING表示每一對相等列中只有一個會被包括在串連輸出中。
    NATURAL	列出在兩個表中所有具有相同名稱的列的USING的簡寫。

• WHERE子句

  • 命令簡介

    可選的WHERE子句的形式 ：

    WHERE condition

  • 參數說明

    參數	說明
    condition	condition是任一計算得到布爾類型結果的運算式。任何不滿足這個條件的行都會從輸出中被消除。如果用一行的實際值替換其中的變數引用後，該運算式返回真，則該行符合條件。

• GROUP BY子句

  • 命令簡介

    GROUP BY將會把所有被選擇的行中共用相同分組運算式值的那些行壓縮成一個行。可選的GROUP BY子句的形式：

    GROUP BY grouping_element [, ...]

  • 參數說明

    grouping_element中使用的expression可以是輸入列名、輸出資料行（SELECT清單項目） 的名稱或序號或者由輸入列值構成的任意運算式。在出現歧義時， GROUP BY名稱將被解釋為輸入列名而不是輸出資料行名。

    如果任何GROUPING SETS、ROLLUP或者CUBE作為分組元素存在，則GROUP BY子句整體上定義了數個獨立的分組集。其效果等效於在具有獨立分組集作為它們的GROUP BY子句的子查詢間構建一個UNION ALL。

    聚集合函式（如果使用）會在組成每一個分組的所有行上進行計算，從而為每一個分組產生一個單獨的值（如果有聚集合函式但是沒有GROUP BY子句，則查詢會被當成是由所有選中行構成的一個單一分組）。傳遞給每一個聚集合函式的行集合可以通過在聚集合函式調用附加一個FILTER子句來進一步過濾。當存在一個FILTER子句時，只有那些匹配它的行才會被包括在該聚集合函式的輸入中。

    當存在GROUP BY子句或者任何聚集合函式時， SELECT列表運算式不能引用非分組列（除非它出現在聚集合函式中或者它函數依賴於分組列），因為這樣做會導致返回非分組列的值時會有多種可能的值。如果分組列是包含非分組列的表的主鍵（ 或者主鍵的子集），則存在函數依賴。

    所有的聚集合函式都是在HAVING子句或者 SELECT列表中的任何“標量“運算式之前被計算。 這意味著一個CASE運算式不能被用來跳過一個聚集運算式的計算。

• CUBE子句

  • CUBE

    CUBE是自動對GROUP BY子句中列出的欄位進行分組匯總，結果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎行中的彙總值。它會為每個分組返回一行匯總資訊，使用者可以使用CUBE來產生交叉表值。例如，在CUBE子句中給出三個運算式（n = 3），運算結果為2ⁿ = 2³ = 8組。 以n個運算式的值分組的行稱為常規行，其餘的行稱為超級聚集行。具體運算式如下：

    CUBE ( { expression | ( expression [, …] ) } [, …] )

  • ROLLUP

    ROLLUP 函數是彙總函式，它是GROUP BY語句的簡單擴充。在資料統計和報表產生過程中，它可以為每個分組返回一個小計，同時為所有分組返回總計。具體運算式如下：

    ROLLUP ( { expression | ( expression [, …] ) } [, …] ) 

  • GROUPING SETS

    GROUPING SETS子句是GROUP BY子句的進一步擴充，它可以使使用者指定多個GROUP BY選項。 這樣做可以通過裁剪使用者不需要的資料群組來提高效率。 當使用者指定了所需的資料群組時，資料庫不需要執行完整CUBE或ROLLUP產生的彙總集合。具體運算式如下：

    GROUPING SETS ( grouping_element [, …] ) 

• DISTINCT子句

  如果指定了SELECT DISTINCT，所有重複的行會被從結果集中移除（為每一組重複的行保留一行）。

  說明

  暫不支援SELECT DISTINCT數組ARRAY類型。

  SELECT DISTINCT accountid FROM table;

• COUNT DISTINCT子句

  • 命令簡介

    COUNT DISTINCT支援計算去重之後的某一個column的個數，對於該列中出現多次的值只會被計算一次，和COUNT的計算類似，如果該列包含NULL值，它將不會計算在內。

  • 文法說明

    • 精確計算的文法樣本如下：

      SELECT c1, COUNT(DISTINCT c2) FROM table GROUP BY c1

    • 由於精確計算的COUNT DISTINCT需要消耗較大的資源，因此互動式分析還支援非精確的COUNT DISTINCT計算，文法樣本如下：

      SELECT c1, approx_count_distinct(c2) FROM table GROUP BY c1

• UNION子句

  • 命令簡介

    UNION子句文法如下：

    select_statement UNION [ ALL | DISTINCT ] select_statement

  • 參數說明

    參數	說明
    select_statement	select_statement是任何沒有ORDER BY、LIMIT、 FOR NO KEY UPDATE、FOR UPDATE、 FOR SHARE和FOR KEY SHARE子句的SELECT語句（如果子運算式被包圍在圓括弧內， ORDER BY和LIMIT可以被附著到其上。如果沒有圓括弧，這些子句將被應用到UNION的結果而不是右手邊的運算式上）。
    UNION	UNION操作符計算所涉及的SELECT語句所返回的行的並集。如果一至少出現在兩個結果集中的一個內，它就會在並集中。作為UNION兩個運算元的SELECT語句必須產生相同數量的列並且對應位置上的列必須具有相容的資料類型。 UNION的結果不會包含重複行，除非指定了ALL選項。ALL會阻止消除重複（因此，UNION ALL通常顯著地快於UNION，盡量使用ALL）。可以寫DISTINCT來顯式地指定消除重複行的行為。 除非用圓括弧指定計算順序，同一個SELECT語句中的多個UNION操作符會從左至右計算。

• INTERSECT子句

  • 命令簡介

    INTERSECT子句具有下面的形式：

    select_statement INTERSECT [ ALL | DISTINCT ] select_statement

  • 參數說明

    參數	說明
    select_statement	select_statement是任何沒有ORDER BY LIMIT子句的SELECT語句。
    INTERSECT	INTERSECT操作符計算所涉及的 SELECT語句返回的行的交集。如果一行同時出現在兩個結果集中，它就在交集中。 INTERSECT的結果不會包含重複行，除非指定了 ALL選項。如果有ALL，一個在左表中有m次重複並且在右表中有n 次重複的行將會在結果中出現min（m，n）次。 DISTINCT可以寫DISTINCT來顯式地指定消除重複行的行為。 除非用圓括弧指定計算順序， 同一個SELECT語句中的多個 INTERSECT操作符會從左至右計算。 INTERSECT的優先順序比UNION更高。也即， A UNION B INTERSECT C將被讀成A UNION（B INTERSECT C）。

• EXCEPT子句

  • 命令簡介

    EXCEPT子句具有下面的形式：

    select_statement EXCEPT [ ALL | DISTINCT ] select_statement

  • 參數說明

    參數	說明
    select_statement	select_statement是任何沒有ORDER BY LIMIT子句的SELECT語句。
    EXCEPT	EXCEPT操作符計算位於左SELECT語句的結果中但不在右SELECT語句結果中的行集合。 EXCEPT的結果不會包含重複行，除非指定了 ALL選項。如果有ALL，一個在左表中有 m次重複並且在右表中有n次重複的行將會在結果集中出現max（m-n，0） 次。 DISTINCT可以寫DISTINCT來顯式地指定消除重複行的行為。 除非用圓括弧指定計算順序， 同一個SELECT語句中的多個 EXCEPT操作符會從左至右計算。 EXCEPT的優先順序與 UNION相同。 當前，FOR NO KEY UPDATE、FOR UPDATE、 FOR SHARE和FOR KEY SHARE不能用於EXCEPT結果或者 EXCEPT的任何輸入。

• ORDER BY子句

  • 命令簡介

    可選的ORDER BY子句的形式如下：

    ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

  • 參數說明

    ORDER BY子句導致結果行被按照指定的運算式排序。 如果兩行按照最左邊的運算式是相等的，則會根據下一個運算式比較它們， 以此類推。如果按照所有指定的運算式它們都是相等的，則它們被返回的順序取決於實現。

    每一個expression 可以是輸出資料行（SELECT清單項目）的名稱或者序號，它也可以是由輸入列值構成的任意運算式。

    序號指的是輸出資料行的順序（從左至右）位置。這種特性可以為不具有唯一名稱的列定義一個順序。但這不是必要操作，因為可以使用 AS子句為輸出資料行賦予一個名稱。

    也可以在ORDER BY子句中使用任意運算式，包括沒有出現在SELECT輸出資料行表中的列。因此， 下面的語句是合法的：

    SELECT name FROM distributors ORDER BY code;

    這種特性的一個限制是一個應用在UNION、 INTERSECT或EXCEPT子句結果上的 ORDER BY只能指定輸出資料行名稱或序號，但不能指定運算式。

    如果一個ORDER BY運算式是一個既匹配輸出資料行名稱又匹配輸入列名稱的簡單名稱，ORDER BY將把它解讀成輸出資料行名稱。這與在同樣情況下GROUP BY會做出的選擇相反。這種不一致是為了與SQL標準相容。

    可以為ORDER BY子句中的任何錶達式之後增加關鍵詞 ASC（上升）或者DESC（下降）。如果沒有指定， ASC被假定為預設值。或者，可以在USING 子句中指定一個特定的排序操作符名稱。一個排序操作符必須是某個 B-樹操作符族的小於或者大於成員。ASC通常等價於 USING <而DESC通常等價於 USING >（但是一種使用者定義資料類型的建立者可以準確地定義預設排序次序是什麼，並且它可能會對應於其他名稱的操作符）。

    如果指定NULLS LAST，空值會排在非空值之後；如果指定NULLS FIRST，空值會排在非空值之前。如果都沒有指定， 在指定或者隱含ASC時的預設行為是NULLS LAST， 而指定或者隱含DESC時的預設行為是NULLS FIRST（因此，預設行為是空值大於非空值）。 當指定USING時，預設的空值順序取決於該操作符是否為小於或者大於操作符。

    注意順序選項只應用到它們所跟隨的運算式上。例如ORDER BY x， y DESC和ORDER BY x DESC，y DESC是不同的。

• LIMIT子句

  • 命令簡介

    LIMIT子句由兩個獨立的子句構成：

    LIMIT { count | ALL }
    OFFSET start

  • 參數說明

    count指定要返回的最大行數，而start指定在返回行之前要跳過的行數。在兩者都被指定時，在開始計算要返回的count行之前會跳過 start行。

    如果count運算式計算為NULL，它會被當成LIMIT ALL，即沒有限制。如果 start計算為 NULL，它會被當作OFFSET 0。

    在使用LIMIT時，用一個ORDER BY子句把結果行約束到一個唯一順序是個好辦法。否則將得到該查詢結果行的一個不可預測的子集 — 可能要求從第 10 到第 20 行，但是在什麼順序下的第10到第20呢？除非指定ORDER BY，否則是不知道順序的。

    查詢規劃器在產生一個查詢計劃時會考慮LIMIT，因此根據使用的LIMIT和OFFSET，很可能得到不同的計劃（得到不同的行序）。所以，使用不同的LIMIT/OFFSET值來選擇一個查詢結果的不同子集將會給出不一致的結果，除非用ORDER BY強制一種可預測的結果順序。這不是一個缺陷，它是SQL不承諾以任何特定順序（除非使用ORDER BY來約束順序）給出一個查詢結果這一事實造成的必然後果。

    如果沒有一個ORDER BY來強制選擇一個確定的子集， 重複執行同樣的LIMIT查詢甚至可能會返回一個表中行的不同子集。同樣，這也不是一種缺陷，再這樣一種情況下也無法保證結果的確定性。

使用樣本

• 兩表JOIN

  SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM 
  distributors d, films f WHERE f.did = d.did;

• WITH子句

  WITH distributor_name(name) AS (SELECT name from distributors)
  SELECT name FROM distributor_name ORDER BY name;

• GROUP BY分組

  SELECT kind, sum(length) AS total FROM films GROUP BY kind;

• HAVING過濾

  SELECT kind, sum(length) AS total FROM films GROUP BY kind 
  HAVING sum(length) < interval '5 hours';

• GROUP BY CUBE

  SELECT  l_returnflag
          ,l_shipmode
          ,SUM(l_quantity)
  FROM    public.lineitem
  GROUP BY cube((l_returnflag),(l_shipmode))
  ORDER BY l_returnflag
           ,l_shipmode;

• GROUP BY ROLLUP

  SELECT  l_returnflag
          ,l_shipmode
          ,SUM(l_quantity)
  FROM    public.lineitem
  GROUP BY ROLLUP ((l_returnflag),(l_shipmode))
  ORDER BY l_returnflag
           ,l_shipmode;

• GROUP BY GROUPING SETS

  SELECT  l_returnflag
          ,l_shipmode
          ,SUM(l_quantity)
  FROM    public.lineitem
  GROUP BY GROUPING SETS ((l_returnflag,l_shipmode),())
  ORDER BY l_returnflag
           ,l_shipmode;

• ORDER BY

  SELECT * FROM distributors ORDER BY name;