如何在SQL中利用GROUPING SETS实现多维度报表统计？-SQL-PHP中文网

grouping sets是sql标准中用于单次查询生成多维度聚合结果的语法，解决需多个group by+union all导致的冗余代码和重复表扫描问题；支持postgresql、sql server等，mysql不支持。

如何在sql中利用grouping sets实现多维度报表统计？

GROUPING SETS 是什么，它解决什么问题

GROUPING SETS 是 SQL 标准中用于生成多组聚合结果的语法，不是某个数据库独有功能，但支持程度不一（PostgreSQL 9.5+、SQL Server 2008+、Oracle 11gR2+、Trino、StarRocks 等支持；MySQL 目前仍不支持）。它本质是把多个 GROUP BY 子句的结果合并成一张结果集，避免写多个 UNION ALL 或重复扫描表。

常见错误现象：想同时看“按部门统计”“按城市统计”“按部门+城市统计”“总计”，却用四条独立 SELECT + UNION ALL，导致表被扫四遍、SQL 冗长难维护。

使用场景：

多维分析报表（如销售数据按区域、产品线、时间粒度交叉汇总）
需要带小计/总计行，且要求各维度组合明确可区分
替代复杂嵌套 ROLLUP 或 CUBE（后者易产生大量无意义组合）

怎么写 GROUPING SETS，括号里填什么

GROUPING SETS 后面跟的是一个元组列表，每个元组是一组 GROUP BY 字段，用圆括号包裹，逗号分隔。空括号 () 表示全表聚合（即总计行）。

例如，对销售表 sales 按 region、product_line、year 三字段做组合统计：

SELECT
  region,
  product_line,
  year,
  SUM(amount) AS total_sales,
  GROUPING(region) AS g_region,
  GROUPING(product_line) AS g_product,
  GROUPING(year) AS g_year
FROM sales
GROUP BY GROUPING SETS (
  (region, product_line, year),
  (region, product_line),
  (region, year),
  (region),
  ()
);

关键点：

每个元组内字段顺序不影响结果，但会影响输出列顺序（建议与 SELECT 列一致）
元组可以重复，但数据库通常会去重（不推荐重复写）
GROUPING() 函数返回 1 表示该列在当前行属于“虚值”（即由 GROUPING SETS 推导出的空组），返回 0 表示真实值，可用于后续条件判断或标记（如把 NULL 替换为 'All'）

GROUPING SETS 和 ROLLUP / CUBE 的性能差异在哪

ROLLUP(a,b,c) 等价于 GROUPING SETS((a,b,c),(a,b),(a),())；CUBE(a,b) 等价于 GROUPING SETS((a,b),(a),(b),())。但显式写 GROUPING SETS 更可控：

性能上：多数引擎会对 GROUPING SETS 做单次扫描 + 分组哈希优化，而多个 UNION ALL 必然多次扫描；CUBE(a,b,c) 会产生 2³=8 组，其中很多可能无业务意义（比如只按 c 聚合），浪费 CPU 和内存。
兼容性上：有些旧版数据库支持 ROLLUP 却不支持 GROUPING SETS（如早期 SQL Server），但新项目应优先选 GROUPING SETS —— 它语义更清晰，也更容易被查询优化器识别为“可合并聚合”。

容易踩的坑：

忘记在 SELECT 中包含 GROUPING() 函数，导致无法区分真实 NULL 和聚合占位 NULL
在 WHERE 中过滤掉 GROUPING() 为 1 的行，意外丢掉小计/总计行
把 GROUPING SETS((a),(b)) 错写成 GROUPING SETS(a,b)（后者语法错误，必须加括号）

如何让结果中的 NULL 更可读

GROUPING SETS 输出中，未参与当前分组的字段值为 NULL，但这对报表用户不友好。用 CASE WHEN GROUPING(col) = 1 THEN 'All' ELSE col END 替换即可：

SELECT
  CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END AS region,
  CASE WHEN GROUPING(product_line) = 1 THEN 'All Lines' ELSE product_line END AS product_line,
  SUM(amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((region), (product_line), ());

注意：