表運算式 - PolarDB

本文介紹了表運算式的相關內容。

簡介

表運算式計算一個表。該表運算式包含一個FROM子句，該子句後面可以根據需要選用WHERE、GROUP BY和HAVING子句。最簡單的表運算式只是引用磁碟上的一個表，一個所謂的基本表，但是我們可以用更複雜的表運算式以多種方法修改或組合基本表。

表運算式裡可選的WHERE、GROUP BY和HAVING子句指定一系列對源自FROM子句的表的轉換操作。所有這些轉換最後產生一個虛擬表，它提供行傳遞給挑選清單計算查詢的輸出行。

FROM子句

FROM子句從一個用逗號分隔的表引用列表中的一個或更多個其它表中產生一個表。

    FROM table_reference [, table_reference [, ...]]

表引用可以是一個表名字（可能有模式限定）或者是一個產生的表，例如子查詢、一個JOIN結構或者這些東西的複雜組合。如果在FROM子句中引用了多於一個表，那麼它們被交叉串連（即構造它們的行的笛卡爾積，見下文）。FROM列表的結果是一個中間的虛擬表，該表可以進行由WHERE、GROUP BY和HAVING子句指定的轉換，並最後產生全域的表運算式結果。

如果一個表引用是一個簡單的表名字並且它是表繼承層次中的父表，那麼該表引用將產生該表和它的後代表中的行，除非你在該表名字前面放上ONLY關鍵字。但是，這種引用只會產生出現在該命名表中的列 — 在子表中增加的列都會被忽略。

除了在表名前寫ONLY，你可以在表名後面寫上``來顯式地指定要包括所有的後代表。沒有實際的理由再繼續使用這種文法，因為搜尋後代表現在總是預設行為。不過，為了保持與舊版本的相容性，仍然支援這種文法。

串連表

一個串連表是根據特定的連線類型的規則從兩個其它表（真實表或產生表）中派生的表。目前支援內串連、外串連和交叉串連。一個串連表的一般文法是：

    T1 join_type T2 [ join_condition ]

所有類型的串連都可以被鏈在一起或者嵌套：T1和T2都可以是串連表。在JOIN子句周圍可以使用圓括弧來控制串連順序。如果不使用圓括弧，JOIN子句會從左至右嵌套。

連線類型

交叉串連

    T1 CROSS JOIN T2

對來自於T1和T2的行的每一種可能的組合（即笛卡爾積），串連表將包含這樣一行：它由所有T1裡面的列後面跟著所有T2裡面的列構成。如果兩個表分別有 N 和 M 行，串連表將有 N * M 行。

FROM ``T1`` CROSS JOIN ``T2等效於FROM ``T1`` INNER JOIN ``T2`` ON TRUE（見下文）。它也等效於FROM ``T1``,``T2。

說明

當多於兩個表出現時，後一種等效並不嚴格成立，因為JOIN比逗號綁得更緊。例如FROM ``T1`` CROSS JOIN ``T2`` INNER JOIN ``T3`` ON ``condition和FROM ``T1``,``T2`` INNER JOIN ``T3`` ON ``condition並不完全相同，因為第一種情況中的condition可以引用T1，但在第二種情況中卻不行。

條件串連

    T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
    T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
    T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

INNER和OUTER對所有串連形式都是可選的。INNER是預設；LEFT、RIGHT和FULL指示一個外串連。

串連條件在ON或USING子句中指定，或者用關鍵字NATURAL隱含地指定。串連條件決定來自兩個源表中的哪些行是“匹配”的，這些我們將在後文詳細解釋。

可能的條件連線類型是：

INNER JOIN對於 T1 的每一行 R1，產生的串連表都有一行對應 T2 中的每一個滿足和 R1 的串連條件的行。

LEFT OUTER JOIN 首先，執行一次內串連。然後，為 T1 中每一個無法在串連條件上匹配 T2 裡任何一行的行返回一個串連行，該串連行中 T2 的列用空值補齊。因此，產生的串連表裡為來自 T1 的每一行都至少包含一行。

RIGHT OUTER JOIN 首先，執行一次內串連。然後，為 T2 中每一個無法在串連條件上匹配 T1 裡任何一行的行返回一個串連行，該串連行中 T1 的列用空值補齊。因此，產生的串連表裡為來自 T2 的每一行都至少包含一行。

FULL OUTER JOIN首先，執行一次內串連。然後，為 T1 中每一個無法在串連條件上匹配 T2 裡任何一行的行返回一個串連行，該串連行中 T2 的列用空值補齊。同樣，為 T2 中每一個無法在串連條件上匹配 T1 裡任何一行的行返回一個串連行，該串連行中 T1 的列用空值補齊。

ON子句是最常見的串連條件的形式：它接收一個和WHERE子句裡用的一樣的布爾值運算式。如果兩個分別來自T1和T2的行在ON運算式上運算的結果為真，那麼它們就算是匹配的行。

USING是個縮寫符號，它允許你利用特殊的情況：串連的兩端都具有相同的串連列名。它接受共用列名的一個逗號分隔列表，並且為其中每一個共用列構造一個包含等值比較的串連條件。例如用USING (a, b)串連T1和T2會產生串連條件ON ``T1``.a = ``T2``.a AND ``T1``.b = ``T2``.b。

更進一步，JOIN USING的輸出會廢除冗餘列：不需要把匹配上的列都列印出來，因為它們必須具有相等的值。不過JOIN ON會先產生來自T1的所有列，後面跟上所有來自T2的列；而JOIN USING會先為列出的每一個列對產生一個輸出資料行，然後先跟上來自T1的剩餘列，最後跟上來自T2的剩餘列。

最後，NATURAL是USING的縮寫形式：它形成一個USING列表，該列表由那些在兩個表裡都出現了的列名組成。和USING一樣，這些列只在輸出表裡出現一次。如果不存在公用列，NATURAL JOIN的行為將和JOIN ... ON TRUE一樣產生交叉集串連。

說明

USING對於串連關係中的列改變是相當安全的，因為只有被列出的列會被組合成串連條件。NATURAL的風險更大，因為如果其中一個關係的模式改變會導致出現一個新的匹配列名，就會導致串連將新列也組合成串連條件。

為瞭解釋這些問題，假設我們有一個表t1：

     num | name
    -----+------
       1 | a
       2 | b
       3 | c

和t2：

     num | value
    -----+-------
       1 | xxx
       3 | yyy
       5 | zzz

然後我們用不同的串連方式可以獲得各種結果：

    => SELECT * FROM t1 CROSS JOIN t2;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       1 | a    |   3 | yyy
       1 | a    |   5 | zzz
       2 | b    |   1 | xxx
       2 | b    |   3 | yyy
       2 | b    |   5 | zzz
       3 | c    |   1 | xxx
       3 | c    |   3 | yyy
       3 | c    |   5 | zzz
    (9 rows)

    => SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       3 | c    |   3 | yyy
    (2 rows)

    => SELECT * FROM t1 INNER JOIN t2 USING (num);
     num | name | value
    -----+------+-------
       1 | a    | xxx
       3 | c    | yyy
    (2 rows)

    => SELECT * FROM t1 NATURAL INNER JOIN t2;
     num | name | value
    -----+------+-------
       1 | a    | xxx
       3 | c    | yyy
    (2 rows)

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       2 | b    |     |
       3 | c    |   3 | yyy
    (3 rows)

    => SELECT * FROM t1 LEFT JOIN t2 USING (num);
     num | name | value
    -----+------+-------
       1 | a    | xxx
       2 | b    |
       3 | c    | yyy
    (3 rows)

    => SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       3 | c    |   3 | yyy
         |      |   5 | zzz
    (3 rows)

    => SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       2 | b    |     |
       3 | c    |   3 | yyy
         |      |   5 | zzz
    (4 rows)

用ON指定的串連條件也可以包含與串連不直接相關的條件。這種功能可能對某些查詢很有用，但是需要我們仔細想清楚。例如：

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
       2 | b    |     |
       3 | c    |     |
    (3 rows)

注意把限制放在WHERE子句中會產生不同的結果：

    => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
     num | name | num | value
    -----+------+-----+-------
       1 | a    |   1 | xxx
    (1 row)

這是因為放在ON子句中的一個約束在串連之前被處理，而放在WHERE子句中的一個約束是在串連之後被處理。這對內串連沒有關係，但是對於外串連會帶來麻煩。

表和列別名

你可以給一個表或複雜的表引用指定一個臨時的名字，用於剩下的查詢中引用那些派生的表。這被叫做表別名。

要建立一個表別名，我們可以寫：

    FROM table_reference AS alias

或者

    FROM table_reference alias

AS關鍵字是可選的。別名可以是任意標識符。

表別名的典型應用是給長表名賦予比較短的標識符，好讓串連子句更易讀。例如：

    SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

到這裡，別名成為當前查詢的表引用的新名稱 — 我們不再能夠用該表最初的名字引用它了。因此，下面的用法是不合法的：

    SELECT * FROM my_table AS m WHERE my_table.a > 5;    -- 錯誤

表別名主要用於簡化符號，但是當把一個表串連到它自身時必須使用別名，例如：

    SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

此外，如果一個表引用是一個子查詢，則必須要使用一個別名。

圓括弧用於解決歧義。在下面的例子中，第一個語句將把別名b賦給my_table的第二個執行個體，但是第二個語句把別名賦給串連的結果：

    SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
    SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

另外一種給表指定別名的形式是給表的列賦予臨時名字，就像給表本身指定別名一樣：

    FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

如果指定的列別名比表裡實際的列少，那麼剩下的列就沒有被重新命名。這種文法對於自串連或子查詢特別有用。

如果用這些形式中的任何一種給一個JOIN子句的輸出附加了一個別名，那麼該別名就在JOIN的作用下隱去了其原始的名字。例如：

    SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

是合法 SQL，但是：

    SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

是不合法的：表別名a在別名c外面是看不到的。

子查詢

子查詢指定了一個派生表，它必須被包圍在圓括弧裡並且必須被賦予一個表別名。例如：

    FROM (SELECT * FROM table1) AS alias_name

這個例子等效於FROM table1 AS alias_name。更有趣的情況是在子查詢裡面有分組或聚集的時候，子查詢不能被簡化為一個簡單的串連。

一個子查詢也可以是一個VALUES列表：

    FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
         AS names(first, last)

再次的，這裡要求一個表別名。為VALUES列表中的列分配別名是可選的，但是選擇這樣做是一個好習慣。

表函數

表函數是那些產生一個行集合的函數，這個集合可以是由基礎資料型別 (Elementary Data Type)（標量類型）組成，也可以是由複合資料型別（錶行）組成。它們的用法類似一個表、視圖或者在查詢的FROM子句裡的子查詢。表函數返回的列可以像一個表列、視圖或者子查詢那樣被包含在SELECT、JOIN或WHERE子句裡。

也可以使用ROWS FROM文法將平行列返回的結果組合成表函數；這種情況下結果行的數量是最大一個函數結果的數量，較小的結果會用空值來填充。

    function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
    ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

如果指定了WITH ORDINALITY子句，一個額外的 bigint類型的列將會被增加到函數的結果列中。這個列對函數結果集的行進行編號，編號從 1 開始（這是對 SQL 標準文法 UNNEST ... WITH ORDINALITY的一般化）。預設情況下，序數列被稱為ordinality，但也可以通過使用一個 AS子句給它分配一個不同的列名。

調用特殊的表函數UNNEST可以使用任意數量的數組參數，它會返回對應的列數，就好像在每一個參數上單獨調用 UNNEST並且使用 ROWS FROM結構把它們組合起來。

    UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

如果沒有指定table_alias，該函數名將被用作表名。在ROWS FROM()結構的情況中，會使用第一個函數名。

如果沒有提供列的別名，那麼對於一個返回基資料類型的函數，列名也與該函數名相同。對於一個返回組合類別型的函數，結果列會從該類型的屬性得到名稱。

例子：

    CREATE TABLE foo (fooid int, foosubid int, fooname text);

    CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
        SELECT * FROM foo WHERE fooid = $1;
    $$ LANGUAGE SQL;

    SELECT * FROM getfoo(1) AS t1;

    SELECT * FROM foo
        WHERE foosubid IN (
                            SELECT foosubid
                            FROM getfoo(foo.fooid) z
                            WHERE z.fooid = foo.fooid
                          );

    CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

    SELECT * FROM vw_getfoo;

參數有時侯定義一個能夠根據它們被呼叫者式返回不同列集合的表函數是很有用的。為了支援這些，表函數可以被聲明為返回沒有OUT參數的偽類型record。如果在查詢裡使用這樣的函數，那麼我們必須在查詢中指定所預期的行結構，這樣系統才知道如何分析和規劃該查詢。這種文法是這樣的：

    function_call [AS] alias (column_definition [, ... ])
    function_call AS [alias] (column_definition [, ... ])
    ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

在沒有使用ROWS FROM()文法時， column_definition列表會取代無法附著在 FROM項上的列別名列表，列定義中的名稱就起到列別名的作用。在使用ROWS FROM()文法時，可以為每一個成員函數單獨附著一個 column_definition列表；或者在只有一個成員函數並且沒有WITH ORDINALITY子句的情況下，可以在 ROWS FROM()後面寫一個 column_definition列表來取代一個列別名列表。

考慮下面的例子：

    SELECT *
        FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
          AS t1(proname name, prosrc text)
        WHERE proname LIKE 'bytea%';

dblink函數（dblink 模組的一部分）執行一個遠端查詢。它被聲明為返回record，因為它可能會被用於任何類型的查詢。實際的列集必須在調用它的查詢中指定，這樣分析器才知道類似``這樣的東西應該擴充成什麼樣子。

此樣本使用ROWS FROM：

    SELECT *
    FROM ROWS FROM
        (
            json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
                AS (a INTEGER, b TEXT),
            generate_series(1, 3)
        ) AS x (p, q, s)
    ORDER BY p;

      p  |  q  | s
    -----+-----+---
      40 | foo | 1
     100 | bar | 2
         |     | 3

它將兩個函數串連到一個FROM目標中。 json_to_recordset()被指示返回兩列，第一個integer 和第二個text。generate_series()的結果直接使用。 ORDER BY子句將列值排序為整數。

LATERAL子查詢

可以在出現於FROM中的子查詢前放置關鍵詞LATERAL。這允許它們引用前面的FROM項提供的列（如果沒有LATERAL，每一個子查詢將被獨立計算，並且因此不能被其他FROM項交叉引用）。

出現在FROM中的表函數的前面也可以被放上關鍵詞LATERAL，但對於函數該關鍵詞是可選的，在任何情況下函數的參數都可以包含對前面的FROM項提供的列的引用。

一個LATERAL項可以出現在FROM列表頂層，或者出現在一個JOIN樹中。在後一種情況下，如果它出現在JOIN的右部，那麼它也可以引用在JOIN左部的任何項。

如果一個FROM項包含LATERAL交叉引用，計算過程如下：對於提供交叉引用列的FROM項的每一行，或者多個提供這些列的多個FROM項的行集合，LATERAL項將被使用該行或者行集中的列值進行計算。得到的結果行將和它們被計算出來的行進行正常的串連。對於來自這些列的源表的每一行或行集，該過程將重複。

LATERAL的一個簡單例子：

    SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

這不是非常有用，因為它和一種更簡單的形式得到的結果完全一樣：

    SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

在必須要使用交叉引用列來計算那些即將要被串連的行時，LATERAL是最有用的。一種常用的應用是為一個返回集合的函數提供一個參數值。例如，假設vertices(polygon)返回一個多邊形的頂點集合，我們可以這樣標識儲存在一個表中的多邊形中靠近的頂點：

    SELECT p1.id, p2.id, v1, v2
    FROM polygons p1, polygons p2,
         LATERAL vertices(p1.poly) v1,
         LATERAL vertices(p2.poly) v2
    WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

這個查詢也可以被寫成：

    SELECT p1.id, p2.id, v1, v2
    FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
         polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
    WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

或者寫成其他幾種等價的公式（正如以上提到的，LATERAL關鍵詞在這個例子中並不是必不可少的，但是我們在這裡使用它是為了使表述更清晰）。

有時候也會很特別地把LEFT JOIN放在一個LATERAL子查詢的前面，這樣即使LATERAL子查詢對源行不產生行，源行也會出現在結果中。例如，如果get_product_names()返回一個製造商製造的產品的名字，但是某些製造商在我們的表中目前沒有製造產品，我們可以找出哪些製造商是這樣：

    SELECT m.name
    FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
    WHERE pname IS NULL;

WHERE子句

WHERE子句的文法是

    WHERE search_condition

這裡的search_condition是任意返回一個boolean類型值的值運算式。

在完成對FROM子句的處理之後，產生的虛擬表的每一行都會對根據搜尋條件進行檢查。如果該條件的結果是真，那麼該行被保留在輸出表中；否則（也就是說，如果結果是假或空）就把它拋棄。搜尋條件通常至少要引用一些在FROM子句裡產生的列；雖然這不是必須的，但如果不引用這些列，那麼WHERE子句就沒什麼用了。

內串連的串連條件既可以寫在WHERE子句也可以寫在JOIN子句裡。例如，這些表運算式是等效的：

    FROM a, b WHERE a.id = b.id AND b.val > 5

和：

    FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

或者可能還有：

    FROM a NATURAL JOIN b WHERE b.val > 5

你想用哪個只是一個風格問題。FROM子句裡的JOIN文法可能不那麼容易移植到其它 SQL 資料庫管理系統中。對於外部串連而言沒有選擇：它們必須在FROM子句中完成。外部串連的ON或USING子句不等於WHERE條件，因為它導致最終結果中行的增加（對那些不匹配的輸入行）和減少。

這裡是一些WHERE子句的例子：

    SELECT ... FROM fdt WHERE c1 > 5

    SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

    SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

    SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

    SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

    SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

在上面的例子裡，fdt是從 FROM 子句中派生的表。那些不符合WHERE子句的搜尋條件的行會被從fdt中刪除。請注意我們把標量子查詢當做一個值運算式來用。和任何其它查詢一樣，子查詢裡可以使用複雜的表運算式。同時還請注意fdt在子查詢中也被引用。只有在c1也是作為子查詢輸入表的產生表的列時，才必須把c1限定成fdt.c1。但限定列名字可以增加語句的清晰度，即使有時候不是必須的。這個例子展示了一個外層查詢的列名範圍如何擴充到它的內層查詢。

GROUP BY和HAVING子句

在通過了WHERE過濾器之後，產生的輸入表可以使用GROUP BY子句進行分組，然後用HAVING子句刪除一些分組行。

    SELECT select_list
        FROM ...
        [WHERE ...]
        GROUP BY grouping_column_reference [, grouping_column_reference]...

GROUP BY子句被用來把表中在所列出的列上具有相同值的行分組在一起。這些列的列出順序並沒有什麼關係。其效果是把每組具有相同值的行組合為一個組行，它代表該組裡的所有行。這樣就可以刪除輸出裡的重複和/或計算應用於這些組的聚集。例如：

    => SELECT * FROM test1;
     x | y
    ---+---
     a | 3
     c | 2
     b | 5
     a | 1
    (4 rows)

    => SELECT x FROM test1 GROUP BY x;
     x
    ---
     a
     b
     c
    (3 rows)

在第二個查詢裡，我們不能寫成SELECT * FROM test1 GROUP BY x，因為列y裡沒有哪個值可以和每個組相關聯起來。被分組的列可以在挑選清單中引用是因為它們在每個組都有單一的值。

通常，如果一個表被分了組，那麼沒有在GROUP BY中列出的列都不能被引用，除非在聚集運算式中被引用。一個用聚集運算式的例子是：

    => SELECT x, sum(y) FROM test1 GROUP BY x;
     x | sum
    ---+-----
     a |   4
     b |   5
     c |   2
    (3 rows)

說明

沒有聚集運算式的分組實際上計算了一個列中可區分值的集合。我們也可以用DISTINCT子句實現。

這裡是另外一個例子：它計算每種產品的總銷售額（而不是所有產品的總銷售額）：

    SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
        FROM products p LEFT JOIN sales s USING (product_id)
        GROUP BY product_id, p.name, p.price;

在這個例子裡，列product_id、p.name和p.price必須在GROUP BY子句裡，因為它們都在查詢的挑選清單裡被引用到（但見下文）。列s.units不必在GROUP BY列表裡，因為它只是在一個聚集運算式（sum(...)）裡使用，它代表一組產品的銷售額。對於每種產品，這個查詢都返回一個該產品的所有銷售額的總和行。

如果產品表被建立起來，例如product_id是主鍵，那麼在上面的例子中用product_id來分組就夠了，因為名稱和價格都是函數依賴於產品識別碼，並且關於為每個產品識別碼分組返回哪個名稱和價格值就不會有歧義。

在嚴格的 SQL 裡，GROUP BY只能對源表的列進行分組，但本資料庫把這個擴充為也允許GROUP BY去根據挑選清單中的列分組。也允許對值運算式進行分組，而不僅是簡單的列名。

如果一個表已經用GROUP BY子句分了組，然後你又只對其中的某些組感興趣，那麼就可以用HAVING子句，它很象WHERE子句，用於從結果中刪除一些組。其文法是：

    SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression

在HAVING子句中的運算式可以引用分組的運算式和未分組的運算式（後者必須涉及一個聚集合函式）。

例子：

    => SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
     x | sum
    ---+-----
     a |   4
     b |   5
    (2 rows)

    => SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
     x | sum
    ---+-----
     a |   4
     b |   5
    (2 rows)

再次，一個更現實的例子：

    SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
        FROM products p LEFT JOIN sales s USING (product_id)
        WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
        GROUP BY product_id, p.name, p.price, p.cost
        HAVING sum(p.price * s.units) > 5000;

在上面的例子裡，WHERE子句用那些非分組的列選擇資料行（運算式只是對那些最近四周發生的銷售為真）。而HAVING子句限制輸出為總銷售收入超過 5000 的組。請注意聚集運算式不需要在查詢中的所有地方都一樣。

如果一個查詢包含聚集合函式調用，但是沒有GROUP BY子句，分組仍然會發生：結果是一個單一行（或者根本就沒有行，如果該單一行被HAVING所消除）。它包含一個HAVING子句時也是這樣，即使沒有任何聚集合函式調用或者GROUP BY子句。

GROUPING SETS、CUBE和ROLLUP

使用分組集的概念可以實現比上述更加複雜的分組操作。由 FROM和WHERE子句選出的資料被按照每一個指定的分組集單獨分組，按照簡單GROUP BY子句對每一個分組計算聚集，然後返回結果。例如：

    => SELECT * FROM items_sold;
     brand | size | sales
    -------+------+-------
     Foo   | L    |  10
     Foo   | M    |  20
     Bar   | M    |  15
     Bar   | L    |  5
    (4 rows)

    => SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
     brand | size | sum
    -------+------+-----
     Foo   |      |  30
     Bar   |      |  20
           | L    |  15
           | M    |  35
           |      |  50
    (5 rows)

GROUPING SETS的每一個子列表可以指定一個或者多個列或者運算式，它們將按照直接出現在GROUP BY子句中同樣的方式被解釋。一個空的分組集表示所有的行都要被聚集到一個單一分組（即使沒有輸入行存在也會被輸出）中，這就像前面所說的沒有GROUP BY子句的聚集合函式的情況一樣。

對於分組列或運算式沒有出現在其中的分組集的結果行，對分組列或運算式的引用會被空值所替代。要區分一個特定的輸出行來自於哪個分組。

PostgreSQL中提供了一種簡化方法來指定兩種常用類型的分組集。下面形式的子句

    ROLLUP ( e1, e2, e3, ... )

表示給定的運算式列表及其所有首碼（包括空列表），因此它等效於

    GROUPING SETS (
        ( e1, e2, e3, ... ),
        ...
        ( e1, e2 ),
        ( e1 ),
        ( )
    )

這通常被用來分析歷史資料，例如按部門、區和公司範圍計算的總薪水。

下面形式的子句

    CUBE ( e1, e2, ... )

表示給定的列表及其可能的子集（即冪集）。因此

    CUBE ( a, b, c )

等效於

    GROUPING SETS (
        ( a, b, c ),
        ( a, b    ),
        ( a,    c ),
        ( a       ),
        (    b, c ),
        (    b    ),
        (       c ),
        (         )
    )

CUBE或ROLLUP子句中的元素可以是運算式或者圓括弧中的元素子列表。在後一種情況中，對於產生分組集的目的來說，子列表被當作單一單元來對待。例如：

    CUBE ( (a, b), (c, d) )

等效於

    GROUPING SETS (
        ( a, b, c, d ),
        ( a, b       ),
        (       c, d ),
        (            )
    )

並且

    ROLLUP ( a, (b, c), d )

等效於

    GROUPING SETS (
        ( a, b, c, d ),
        ( a, b, c    ),
        ( a          ),
        (            )
    )

CUBE和ROLLUP可以被直接用在 GROUP BY子句中，也可以被嵌套在一個 GROUPING SETS子句中。如果一個 GROUPING SETS子句被嵌套在另一個同類子句中，效果和把內層子句的所有元素直接寫在外層子句中一樣。

如果在一個GROUP BY子句中指定了多個分組項，那麼最終的分組集列表是這些項的叉積。例如：

    GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))

等效於

    GROUP BY GROUPING SETS (
        (a, b, c, d), (a, b, c, e),
        (a, b, d),    (a, b, e),
        (a, c, d),    (a, c, e),
        (a, d),       (a, e)
    )

說明

在運算式中，結構(a, b)通常被識別為一個 a 行構造器。在 GROUP BY子句中，這不會在運算式的頂層應用，並且 (a, b)會按照上面所說的被解析為一個運算式的列表。如果出於某種原因你在分組運算式中需要一個行構造器，請使用 ROW(a, b)。

視窗函數處理

如果查詢包含任何視窗函數，這些函數將在任何分組、聚集和HAVING過濾被執行之後被計算。也就是說如果查詢使用了任何聚集、GROUP BY或HAVING，則視窗函數看到的行是分組行而不是來自於FROM/WHERE的原始錶行。

當多個視窗函數被使用，所有在視窗定義中有句法上等效的PARTITION BY和ORDER BY子句的視窗函數被保證在資料上的同一趟掃描中計算。因此它們將會看到相同的排序次序，即使ORDER BY沒有唯一地決定一個順序。但是，對於具有不同PARTITION BY或ORDER BY定義的函數的計算沒有這種保證（在這種情況中，在多個視窗Function Compute之間通常要求一個排序步驟，並且並不保證保留行的順序，即使它的ORDER BY把這些行視為等效的）。

目前，視窗函數總是要求排序好的資料，並且這樣查詢的輸出總是被根據視窗函數的PARTITION BY/ORDER BY子句的一個或者另一個排序。但是，我們不推薦依賴於此。如果你希望確保結果以特定的方式排序，請顯式使用頂層的ORDER BY子句。