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 clauses 执行相同的聚合。
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), ()。这对于生成不同详细程度的 group by 子句非常有用。这将生成 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() 都会设置一个对应于该表达式位置的位。