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, ());
| 课程 | type | count_star() |
|---|---|---|
| 数学 | NULL | 1 |
| NULL | NULL | 7 |
| 计算机科学 | 博士 | 1 |
| 计算机科学 | 学士 | 2 |
| 数学 | 硕士 | 1 |
| 计算机科学 | NULL | 2 |
| 数学 | NULL | 2 |
| 计算机科学 | NULL | 5 |
| NULL | NULL | 3 |
| NULL | 硕士 | 1 |
| NULL | 学士 | 2 |
| NULL | 博士 | 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 个项在 ROLLUP 子句中，则会生成 n+1 个分组集。
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 | 季度 | 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 | 季度 | 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() 就会设置对应于该表达式位置的位。