Postgresql 中文操作指南

7.2. Table Expressions #

table expression 计算表。表表达式包含一个 FROM 子句，其后可选择地跟有 WHERE、GROUP BY 和 HAVING 子句。简单的表表达式只引用磁盘上的表，即所谓的基表，但是可以使用更复杂的表达式以各种方式修改或合并基表。

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

7.2.1. The FROM Clause #

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

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

表引用可以是表名（可能具有模式限定），或者派生表（例如子查询、JOIN 构造或它们的复杂组合）。如果 FROM 子句中列出了多个表引用，则表将被交叉连接（也就是说，会形成它们的行的笛卡尔积；请参阅下方）。FROM 列表的结果是一个中间虚拟表，然后可以由 WHERE、GROUP BY 和 HAVING 子句进行转换，最终成为总表表达式的结果。

当表引用指定表为表继承层次结构的父表时，表引用不仅会生成该表中的行，还会生成它所有下级表中的行，除非表名之前有关键字 ONLY。但是，引用只会生成已命名表中出现的列 — 忽略已分表中添加的任何列。

您可以在表名前面写 ONLY 来表示表名，也可以在表名后面写 * 来明确指定包含下级表。目前使用这种语法已经没有真正理由，因为现在始终将搜索下级表作为默认行为。但是，为了与较早版本兼容，仍然支持它。

7.2.1.1. Joined Tables #

联接表是依据特定的联接类型规则从两个其他（真实或派生）表派生出的表。可以使用内联接、外联接和交叉连接。联接表的常规语法是

T1 join_type T2 [ join_condition ]

所有类型的联接都可以链接在一起，或者嵌套：T1 和 T2，或者二者，都可以是联接表。圆括号可以围绕 JOIN 子句用来控制联接顺序。在没有圆括号的情况下，JOIN 子句按从左至右嵌套。

Join Types

Cross join

T1 CROSS JOIN T2

对于 T1 和 T2 中每一行的可能组合（即笛卡尔积），联接表都将包含一行，其中包括 T1 中的所有列，后跟 T2 中的所有列。如果各表分别有 N 行和 M 行，则联接表将有 N * M 行。
FROM _T1 CROSS JOIN T2 _ is equivalent to _FROM _T1 INNER JOIN T2 ON TRUE_ (see below). It is also equivalent to FROM _T1, T2 。
- Qualified joins

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 隐含外联接。
join condition 在 ON 或 USING 中指定，它也可以由 NATURAL 暗含。联接条件决定了两个源表中的哪一行被认为“匹配”，具体解释见下文。
可能的复合联接类型有：
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 ，产生笛卡尔积联接。

Note

后者当出现两个以上表时，该等价关系并不完全成立，因为 JOIN 的结合比逗号更紧密。例如， FROM _T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROM _T1, T2 INNER JOIN T3 ON condition because the condition 在第一种情况下可以引用 T1 ，但在第二种情况下不能。

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 列中添加一个带空值的联接行。

Note

USING 对于连接关系中的列更改相当安全，因为只有列出的列被合并。NATURAL 的风险更大，因为对任一关系的任何导致出现新匹配的列名的架构更改都将导致连接也合并该新列。

综上所述，假设我们有以下各表：

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

和：

 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. Table and Column Aliases #

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

要创建表别名，请这样写

FROM table_reference AS alias

或

FROM table_reference alias

AS 关键字是可选噪音。alias 可以是任何标识符。

表别名的一个典型应用是为长表名指定短标识符，以使联接条款保持可读性。例如：

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;

括号用于解决歧义。在下面的示例中，第 1 条语句将别名 b 分配给 my_table 的第二个实例，但第 2 条语句将别名分配给连接的结果：

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. Subqueries #

指定派生表的子查询必须括在括号中。可以为它们分配表别名，还可以选择分配列别名（如 Section 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 列表中的列分配别名是可选的，但这是一个好习惯。有关详细信息，请参见 Section 7.7。

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

7.2.1.4. Table Functions #

表函数是产生一组行的函数，由基本数据类型（标量类型）或复合数据类型（表行）组成。它们在查询的 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 ( Section 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 Subqueries #

出现在 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. The WHERE Clause #

WHERE 条款的语法是

WHERE search_condition

其中 search_condition 是返回类型为 boolean 的任何值表达式（参见 Section 4.2）。

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

Note

内连接的连接条件可以写在 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

使用哪一种主要取决于风格。JOIN 中 FROM 子句中的语法可能不像 SQL 标准中那样便携到其他 SQL 数据库管理系统。对于外部连接，没有选择：必须在 FROM 子句中完成。外部连接的 ON 或 USING 子句_not_ 等同于 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 中消除。注意标量子查询用作值表达式。就像任何其他查询一样，子查询可以使用复杂表表达式。还要注意 @{13} 在子查询中引用的方式。只有当 c1 也是子查询的派生输入表中一列的名称时，才需要将 c1 限定为 fdt.c1。但即使不需要限定列名，限定列名也会增加清晰度。此示例显示了外部查询的列命名范围如何扩展到其内部查询。

7.2.3. The GROUP BY and HAVING Clauses #

通过 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 是一个聚合函数，它计算整个组的单个值。有关可用聚合函数的详细信息，请参见 Section 9.21。

Tip

在没有任何聚合表达式的条件下进行分组实际上就是计算列中不同的值集。也可以使用 DISTINCT 子句（参见 Section 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 中 name 和 price 会是 functionally dependent，因此每个产品 ID 组要返回的 name 和 price 值不会有歧义。

在严格的 SQL 中，GROUP BY 只可以按源表的列分组，但 PostgreSQL 将其扩展为还允许 GROUP BY 按选择列表中的列分组。还允许按值表达式而不是简单列名分组。

如果已使用 GROUP BY 对表进行了分组，但只关注某些组，则可以像 WHERE 子句一样使用 HAVING 子句，从结果中消除组。语法如下：

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, and ROLLUP #

还可以使用 grouping sets 的概念来执行比上述更复杂的分组操作。由 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 值。要区分特定输出行是由哪个分组产生的，请参见 Table 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，因为输出行仍然可能包含副本。如果任何未分组列所含的内容均为空，则从该列被分组时使用的空值中无法将其区分开来。

Note

构造 (a, b) 通常在表达式的 row constructor 中被识别。在 GROUP BY 条款内，这并不适用于表达式最上层，而 (a, b) 被解析为上述表达式列表。如果您出于某种原因在分组表达式中省略 need 行构造函数，请使用 ROW(a, b)。

7.2.5. Window Function Processing #

如果查询包含任何窗口函数（参见 Section 3.5、 Section 9.22 和 Section 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 子句。