GROUPING SETS 子句
GROUPING SETS、ROLLUP 和 CUBE 可用于 GROUP BY 子句，以便在同一条查询中按多个维度进行分组。
请注意，该语法与 GROUP BY ALL 不兼容。
示例
按给定的四个不同维度计算平均收入：
-- the syntax () denotes the empty set (i.e., computing an ungrouped aggregate)
SELECT city, street_name, avg(income)
FROM addresses
GROUP BY GROUPING SETS ((city, street_name), (city), (street_name), ());
按相同维度计算平均收入：
SELECT city, street_name, avg(income)
FROM addresses
GROUP BY CUBE (city, street_name);
按 (city, street_name)、(city) 和 () 这些维度计算平均收入：
SELECT city, street_name, avg(income)
FROM addresses
GROUP BY ROLLUP (city, street_name);
说明
GROUPING SETS 可在单条查询中跨不同的 GROUP BY 子句执行同一种聚合。
CREATE TABLE students (course VARCHAR, type VARCHAR);
INSERT INTO students (course, type)
VALUES
('CS', 'Bachelor'), ('CS', 'Bachelor'), ('CS', 'PhD'), ('Math', 'Masters'),
('CS', NULL), ('CS', NULL), ('Math', NULL);
SELECT course, type, count(*)
FROM students
GROUP BY GROUPING SETS ((course, type), course, type, ());
| course | type | count_star() |
|---|---|---|
| Math | NULL | 1 |
| NULL | NULL | 7 |
| CS | PhD | 1 |
| CS | Bachelor | 2 |
| Math | Masters | 1 |
| CS | NULL | 2 |
| Math | NULL | 2 |
| CS | NULL | 5 |
| NULL | NULL | 3 |
| NULL | Masters | 1 |
| NULL | Bachelor | 2 |
| NULL | PhD | 1 |
在上述查询中，我们跨四个不同集合分组：course, type、course、type 和 ()（空分组）。对于某条结果所对应分组集中不存在的列，结果会显示 NULL。也就是说，上述查询等价于下面这些 UNION ALL 子句：
-- Group by course, type:
SELECT course, type, count(*)
FROM students
GROUP BY course, type
UNION ALL
-- Group by type:
SELECT NULL AS course, type, count(*)
FROM students
GROUP BY type
UNION ALL
-- Group by course:
SELECT course, NULL AS type, count(*)
FROM students
GROUP BY course
UNION ALL
-- Group by nothing:
SELECT NULL AS course, NULL AS type, count(*)
FROM students;
CUBE 和 ROLLUP 是语法糖，可方便生成常见的分组集合。
ROLLUP 子句会生成某个分组集合的所有“子分组”。例如，ROLLUP (country, city, zip) 会生成 (country, city, zip), (country, city), (country), ()。这对产出不同明细层级的分组结果很有用。其会生成 n+1 个分组集合，其中 n 是 ROLLUP 子句中的项数。
CUBE 会为输入项的所有组合生成分组集合。例如，CUBE (country, city, zip) 会生成 (country, city, zip), (country, city), (country, zip), (city, zip), (country), (city), (zip), ()。其会生成 2^n 个分组集合。
使用 GROUPING_ID() 识别分组集合
由 GROUPING SETS、ROLLUP 和 CUBE 生成的超级聚合行，通常可以通过相应分组列返回的 NULL 值来识别。但如果分组列本身就可能包含真实的 NULL，就很难判断结果集里的 NULL 到底来自原始数据，还是由分组结构生成。GROUPING_ID() 或 GROUPING() 函数就是为识别哪些分组生成了这些超级聚合行而设计的。
GROUPING_ID() 是一个聚合函数，接收组成分组的列表达式，并返回一个 BIGINT 值。对于非超级聚合行，返回值为 0。对于超级聚合行，会返回一个整数，用于标识生成该超级聚合的表达式组合。下面的示例更直观：
WITH days AS (
SELECT
year("generate_series") AS y,
quarter("generate_series") AS q,
month("generate_series") AS m
FROM generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL 1 DAY)
)
SELECT y, q, m, GROUPING_ID(y, q, m) AS "grouping_id()"
FROM days
GROUP BY GROUPING SETS (
(y, q, m),
(y, q),
(y),
()
)
ORDER BY y, q, m;
结果如下：
| y | q | m | grouping_id() |
|---|---|---|---|
| 2023 | 1 | 1 | 0 |
| 2023 | 1 | 2 | 0 |
| 2023 | 1 | 3 | 0 |
| 2023 | 1 | NULL | 1 |
| 2023 | 2 | 4 | 0 |
| 2023 | 2 | 5 | 0 |
| 2023 | 2 | 6 | 0 |
| 2023 | 2 | NULL | 1 |
| 2023 | 3 | 7 | 0 |
| 2023 | 3 | 8 | 0 |
| 2023 | 3 | 9 | 0 |
| 2023 | 3 | NULL | 1 |
| 2023 | 4 | 10 | 0 |
| 2023 | 4 | 11 | 0 |
| 2023 | 4 | 12 | 0 |
| 2023 | 4 | NULL | 1 |
| 2023 | NULL | NULL | 3 |
| NULL | NULL | NULL | 7 |
在这个示例中，最低分组层级是月份层级，即分组集 (y, q, m)。对应这一级别的结果行只是普通聚合行，因此 GROUPING_ID(y, q, m) 返回 0。分组集 (y, q) 会在月份层级之上产生超级聚合行，此时 m 列为 NULL，GROUPING_ID(y, q, m) 返回 1。分组集 (y) 会在季度层级之上产生超级聚合行，此时 m 和 q 列为 NULL，GROUPING_ID(y, q, m) 返回 3。最后，分组集 () 会为整个结果集产生一条超级聚合行，y、q 和 m 都为 NULL，此时 GROUPING_ID(y, q, m) 返回 7。
要理解返回值与分组集之间的关系，可以把 GROUPING_ID(y, q, m) 看作在写一个位字段（bitfield）：第一位对应传给 GROUPING_ID() 的最后一个表达式，第二位对应倒数第二个表达式，依此类推。将 GROUPING_ID() 转为 BIT 会更清楚：
WITH days AS (
SELECT
year("generate_series") AS y,
quarter("generate_series") AS q,
month("generate_series") AS m
FROM generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL 1 DAY)
)
SELECT
y, q, m,
GROUPING_ID(y, q, m) AS "grouping_id(y, q, m)",
right(GROUPING_ID(y, q, m)::BIT::VARCHAR, 3) AS "y_q_m_bits"
FROM days
GROUP BY GROUPING SETS (
(y, q, m),
(y, q),
(y),
()
)
ORDER BY y, q, m;
其返回结果如下：
| y | q | m | grouping_id(y, q, m) | y_q_m_bits |
|---|---|---|---|---|
| 2023 | 1 | 1 | 0 | 000 |
| 2023 | 1 | 2 | 0 | 000 |
| 2023 | 1 | 3 | 0 | 000 |
| 2023 | 1 | NULL | 1 | 001 |
| 2023 | 2 | 4 | 0 | 000 |
| 2023 | 2 | 5 | 0 | 000 |
| 2023 | 2 | 6 | 0 | 000 |
| 2023 | 2 | NULL | 1 | 001 |
| 2023 | 3 | 7 | 0 | 000 |
| 2023 | 3 | 8 | 0 | 000 |
| 2023 | 3 | 9 | 0 | 000 |
| 2023 | 3 | NULL | 1 | 001 |
| 2023 | 4 | 10 | 0 | 000 |
| 2023 | 4 | 11 | 0 | 000 |
| 2023 | 4 | 12 | 0 | 000 |
| 2023 | 4 | NULL | 1 | 001 |
| 2023 | NULL | NULL | 3 | 011 |
| NULL | NULL | NULL | 7 | 111 |
请注意，传给 GROUPING_ID() 的表达式数量及其顺序，与 GROUPING SETS 子句中实际定义的分组（或 ROLLUP、CUBE 隐含的分组）是相互独立的。只要传入 GROUPING_ID() 的表达式出现在 GROUPING SETS 子句中的任意位置，当某个表达式被汇总为超级聚合时，GROUPING_ID() 就会设置与该表达式位置对应的位。