GROUPING SETS, ROLLUP 和 CUBE 可以在 GROUP BY 子句中使用，以在同一查询中对多个维度进行分组。
请注意，此语法与 GROUP BY ALL 不兼容。
Examples
计算沿提供的四个不同维度的平均收入：
-- 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);
Description
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, ());
| 课程 | 类型 | count_star() |
|---|---|---|
| 数学 | NULL | 1 |
| NULL | NULL | 7 |
| 计算机科学 | 博士 | 1 |
| 计算机科学 | 学士 | 2 |
| 数学 | 硕士 | 1 |
| CS | NULL | 2 |
| 数学 | NULL | 2 |
| CS | NULL | 5 |
| NULL | NULL | 3 |
| NULL | 硕士 | 1 |
| NULL | 学士 | 2 |
| NULL | 博士 | 1 |
在上述查询中，我们跨四个不同的集合进行分组：course, type、course、type 和 ()（空组）。结果中包含 NULL 表示该组不在结果的分组集合中，即上述查询等同于以下 UNION 语句：
按课程、类型分组：
SELECT course, type, count(*)
FROM students
GROUP BY course, type
UNION ALL
按类型分组：
SELECT NULL AS course, type, count(*)
FROM students
GROUP BY type
UNION ALL
按课程分组：
SELECT course, NULL AS type, count(*)
FROM students
GROUP BY course
UNION ALL
不进行分组：
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值，还是由分组结构生成的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)视为写入一个位字段，其中第一位对应于传递给GROUPING_ID()的最后一个表达式，第二位对应于传递给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()就会在该表达式被汇总为超级聚合时，设置一个与该表达式位置对应的位。