7.2. 表表达式#

7.2.1. FROM 子句
7.2.2. WHERE 子句
7.2.3. GROUP BY 和 HAVING 子句
7.2.4. GROUPING SETS、CUBE 和 ROLLUP
7.2.5. 窗口函数处理

表表达式计算一个表。表表达式包含一个FROM子句，该子句后面可以跟WHERE、GROUP BY和HAVING子句。简单的表表达式仅引用磁盘上的表，即所谓的基表，但可以使用更复杂的表达式以各种方式修改或组合基表。

表表达式中可选的WHERE、GROUP BY和HAVING子句指定在FROM子句中派生的表上执行的连续转换管道。所有这些转换都会生成一个虚拟表，该表提供传递给选择列表以计算查询的输出行的行。

7.2.1.`FROM`子句#

FROM子句从用逗号分隔的表引用列表中给出的一个或多个其他表派生一个表。

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

表引用可以是表名（可能是经过架构限定的），或派生表，例如子查询、JOIN构造或这些的复杂组合。如果在FROM子句中列出了多个表引用，则这些表将进行交叉联接（即，形成它们行的笛卡尔积；见下文）。FROM列表的结果是一个中间虚拟表，然后可以由WHERE、GROUP BY和HAVING子句进行转换，最终成为整个表表达式的结果。

当表引用命名一个表，该表是表继承层次结构的父表时，表引用不仅会生成该表的行，还会生成其所有子表的行，除非关键字ONLY位于表名前。但是，引用仅生成在命名表中出现的列，而子表中添加的任何列都将被忽略。

你可以不用在表名前写ONLY，而是在表名后写*，以明确指定包括子表。现在已经不再有理由使用此语法，因为搜索子表现在始终是默认行为。但是，出于与旧版本的兼容性，它仍然受支持。

7.2.1.1. 联接表#

联接表是根据特定联接类型的规则从另外两个（真实或派生）表派生的表。内部联接、外部联接和交叉联接均可用。联接表的通用语法为

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 子句中使用的相同类型的布尔值表达式。如果 ON 表达式计算结果为真，则来自 T1 和 T2 的一对行匹配。

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子句中的限制条件在联接之后处理。对于内联接而言，这无关紧要，但对于外联接而言，却很重要。

7.2.1.2. 表和列别名#

可以为表和复杂表引用指定一个临时名称，用于在查询的其余部分中引用派生表。这称为表别名。

要创建表别名，请编写

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;    -- wrong

表别名主要用于符号方便，但在将表联接到自身时必须使用它们，例如

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外不可见。

7.2.1.3. 子查询#

指定派生表的子查询必须用括号括起来。可以为它们分配表别名，还可以选择分配列别名（如第 7.2.1.2 节中所示）。例如

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列表的列分配别名是可选的，但这是一个好习惯。有关详细信息，请参阅第 7.7 节。

根据 SQL 标准，必须为子查询提供表别名。PostgreSQL允许省略AS和别名，但在可能移植到其他系统的 SQL 代码中编写别名是一个好习惯。

7.2.1.4. 表函数#

表函数是生成一组行的函数，这些行由基本数据类型（标量类型）或复合数据类型（表行）组成。它们在查询的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 开始。（这是UNNEST ... WITH ORDINALITY的 SQL 标准语法的概括。）默认情况下，序数列称为ordinality，但可以使用AS子句为其分配不同的列名。

特殊表函数UNNEST可以使用任意数量的数组参数调用，并且它返回相应数量的列，就好像在每个参数上单独调用了UNNEST（第 9.19 节）并使用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;

在某些情况下，定义表函数很有用，该表函数可以根据调用方式返回不同的列集。为了支持这一点，可以将表函数声明为返回伪类型record，且没有OUT参数。在查询中使用此类函数时，必须在查询本身中指定预期的行结构，以便系统能够知道如何解析和规划查询。此语法如下所示

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子句，则可以编写column_definition*列表来代替ROWS FROM()后面的列别名列表。

考虑此示例

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子句将列值按整数排序。

7.2.1.5.`LATERAL`子查询#

出现在FROM中的子查询可以在关键字LATERAL之前。这允许它们引用由前面的FROM项提供的列。（如果没有LATERAL，则每个子查询都会独立评估，因此不能交叉引用任何其他FROM项。）

出现在FROM中的表函数也可以在关键字LATERAL之前，但对于函数，关键字是可选的；函数的参数在任何情况下都可以包含对由前面的FROM项提供的列的引用。

LATERAL项可以出现在FROM列表的顶层，或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;

7.2.2.`WHERE`子句#

WHERE子句的语法为

WHERE search_condition

其中*search_condition*是任何值表达式（请参见第 4.2 节），它返回boolean类型的值。

在FROM子句的处理完成后，会根据搜索条件检查派生虚拟表中的每一行。如果条件结果为真，则将该行保留在输出表中，否则（即，如果结果为假或 null）则将其丢弃。搜索条件通常至少引用FROM子句中生成的表的一列；这不是必需的，但否则WHERE子句将相当无用。

注意

内连接的连接条件可以写在WHERE子句中，也可以写在JOIN子句中。例如，这些表表达式是等效的和甚至可能是使用哪一个主要取决于样式。FROM子句中的JOIN语法可能无法移植到其他 SQL 数据库管理系统，即使它在 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。但即使不需要限定列名称，限定列名称也会增加清晰度。此示例显示了外部查询的列命名范围如何扩展到其内部查询。

7.2.3.`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)

此处sum是一个聚合函数，它计算整个组的单个值。有关可用聚合函数的更多信息，请参见第 9.21 节。

提示

不使用聚合表达式的分组有效地计算列中不同值的集合。这也可以使用DISTINCT子句来实现（参见第 7.3.3 节）。

这里有另一个示例：它计算每个产品的总销售额（而不是所有产品的总销售额）

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分组就足够了，因为名称和价格在功能上依赖于产品 ID，因此对于每个产品 ID 组返回哪个名称和价格值不会有歧义。

在严格的 SQL 中，GROUP BY只能按源表的列进行分组，但PostgreSQL将其扩展为还允许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子句，情况也是如此。

7.2.4.`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子句的聚合函数的情况。

对分组列或表达式的引用在分组集中那些列不出现的输出行中被替换为 null 值。要区分特定输出行产生的分组，请参阅表 9.63。

提供了一种简写符号来指定两种常见的分组集类型。形式为

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)
)

在同时指定多个分组项时，分组集的最终集合可能包含重复项。例如

GROUP BY ROLLUP (a, b), ROLLUP (a, c)

等效于

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

如果这些重复项不可取，则可以使用DISTINCT子句直接在GROUP BY上删除它们。因此

GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)

等效于

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

这与使用SELECT DISTINCT不同，因为输出行仍然可能包含重复项。如果任何未分组的列包含 NULL，则它将与该列分组时使用的 NULL 无法区分。

注意

构造(a, b)通常在表达式中被识别为行构造器。在GROUP BY子句中，这并不适用于表达式的顶层，并且(a, b)被解析为如上所述的表达式列表。如果您出于某种原因需要在分组表达式中使用行构造器，请使用ROW(a, b)。

7.2.5. 窗口函数处理#

如果查询包含任何窗口函数（请参见第 3.5 节、第 9.22 节和第 4.2.8 节），这些函数将在执行任何分组、聚合和HAVING过滤之后进行评估。也就是说，如果查询使用任何聚合、GROUP BY或HAVING，则窗口函数看到的是组行，而不是FROM/WHERE中的原始表行。

当使用多个窗口函数时，所有在窗口定义中具有语法上等效的PARTITION BY和ORDER BY子句的窗口函数都保证在对数据进行单次遍历时进行评估。因此，它们将看到相同的排序顺序，即使ORDER BY无法唯一确定排序顺序。但是，对于具有不同的PARTITION BY或ORDER BY规范的函数的评估，没有任何保证。（在这种情况下，在窗口函数评估的遍历之间通常需要一个排序步骤，并且无法保证该排序保留其ORDER BY视为等效的行顺序。）

目前，窗口函数始终需要预排序数据，因此查询输出将根据窗口函数的PARTITION BY/ORDER BY子句之一进行排序。但是，不建议依赖于此。如果你希望确保结果按特定方式进行排序，请使用显式的顶级ORDER BY子句。

7.2. 表表达式# ​

7.2.1.FROM子句# ​

7.2.1.1. 联接表# ​

注意

注意

7.2.1.2. 表和列别名# ​

7.2.1.3. 子查询# ​

7.2.1.4. 表函数# ​

7.2.1.5.LATERAL子查询# ​

7.2.2.WHERE子句# ​

7.2.3.GROUP BY和HAVING子句# ​

7.2.4.GROUPING SETS、CUBE和ROLLUP# ​

7.2.5. 窗口函数处理# ​