如果你经常写多粒度汇总报表一定体会过这种痛苦月初给运营部门出一份“经营一览”产品线要看一遍、区域要看一遍产品线交叉区域又要看一遍最后还要一个总盘。第一次接需求还老老实实写几段SQL等口径从两个变成五个的时候SQL文本基本就失控了。我处理这类问题时最常用的底层工具就是grouping sets操作符——它能把原来“group by union all”的堆叠写法压缩成一条结构化SQL里的几行内容同时数据库在大多数情况下只需要扫描一次基础数据。这篇文章会把grouping sets的语法、改写过程、空值陷阱、选型边界一次讲透适合那些天天跟明细数据和多粒度汇总打交道实在不想再靠复制粘贴维持union all拼SQL的朋友。1. 一张报表五种口径union all 的写法正在拖垮你的查询1.1 业务需求到底长什么样先说一个典型的报表需求。假设有一张销售明细表sales_summary结构很简单字段含义product_category产品线比如 A、B、Cregion销售区域比如 North、Southsales_month销售月份比如 2025-01revenue销售额领导说月底要一张汇总表表格里同时包含这些维度组合的汇总每个产品线的总销售额每个区域的总销售额每个产品线在某个区域下的销售额每个产品线在每个月份的销售额整张表的全局总销售额。这种情况非常常见业务侧很少只要单一粒度的报表大多数时候是把“明细 各个上卷维度”放到一起展示方便运营在同一张表里下钻和分析。很多同学遇到这种需求第一反应是写多条 SQL再用UNION ALL串起来。问题来了随着口径增多SQL 会变得极其冗长。你有多少次看到别人代码里连续排着七八段几乎一样的GROUP BY如果哪天业务说“再加一个按销售渠道的维度”你得在每一段聚合里都改一遍。更可怕的是这段 SQL 一旦超过两百行后面接手的人基本只能靠猜来维护。1.2 传统写法的完整代码把上面的五重口径用group by union all实现出来大概长这样SELECT product_category, region, sales_month, SUM(revenue) AS revenue FROM sales_summary GROUP BY product_category, region, sales_month UNION ALL SELECT product_category, NULL, NULL, SUM(revenue) FROM sales_summary GROUP BY product_category UNION ALL SELECT NULL, region, NULL, SUM(revenue) FROM sales_summary GROUP BY region UNION ALL SELECT product_category, region, NULL, SUM(revenue) FROM sales_summary GROUP BY product_category, region UNION ALL SELECT NULL, NULL, NULL, SUM(revenue) FROM sales_summary;注意看这些NULL它们的目的是把结果集的列数对齐。比如“按产品线汇总”这个分支没有区域和月份的信息就用NULL占位。这段 SQL 能跑但维护它的人内心一定在骂人。因为它把“同一份数据的五种观察角度”硬生生写成了五个互不相干的查询片段。1.3 这种写法藏着三个问题第一个问题是可维护性差。如果要加一个过滤条件比如“只要销售额大于 100 的产品线”你就得在五个分支里同时加上HAVING或WHERE。漏掉一个报表数据对不上排查起来非常痛苦。第二个问题是重复扫描和重复计算。绝大多数数据库在遇到这种多条UNION ALL组合时会为每个分支独立生成执行计划。也就是说同一张sales_summary表会被完整读取五遍。如果这张表有几千万行或它的数据来源本身是一个复杂的多表 JOIN 子查询这个成本会被线性放大。第三个问题是优化器难以统一调整。你很难让数据库把五个分支当成一个整体去做去重扫描、共享聚合中间结果、统一排序。它更多只是机械地把五个结果拼在一起失去了很多优化空间。其实这五个口径本质上是同一个问题我要对同一份数据同时做几组不同的 GROUP BY。这就是“分组集”的概念而 SQL 标准里早就为此准备了专门的语法也就是标题里说的GROUPING SETS。2. grouping sets 语法拆解一次扫描多组聚合并行输出2.1 核心语法很简单GROUPING SETS的语法写出来非常直观SELECT product_category, region, sales_month, SUM(revenue) AS revenue FROM sales_summary GROUP BY GROUPING SETS ( (product_category, region, sales_month), (product_category), (region), (product_category, region), () );GROUP BY GROUPING SETS后面的每个括号代表一个“分组集”。数据库会在同一条 SQL 内分别按这些维度组合做聚合然后把结果合并输出。其中最后一个空括号()表示“不按任何列分组”也就是算出全局总计。2.2 分组集是什么给数据做不同角度的切片你可以把分组集理解成“一把刀切下去的角度”。按产品线切就是每个产品线的汇总按区域切就是每个区域的汇总按产品线加区域切就是交叉维度什么都不按就是整块蛋糕一起称重。传统写法里每切一刀都要重新搬一次整块蛋糕切完一桌摆五个盘子。GROUPING SETS则是把蛋糕一次搬上工作台五种切法在同一轮操作里完成最后把五个盘子的结果依次报给你。这里有一个很关键的理解点GROUPING SETS不是“五条 SQL 的拼接”而是“一条 SQL 内声明了多种聚合目标”。数据库拿到这个语法后会构建一套同时维护多组聚合状态的计算逻辑。2.3 执行原理简要说明我不过度深入优化器源码但你可以从两个层面理解它和UNION ALL的本质差别。在扫描层面GROUPING SETS让数据库有机会只读取一次底层数据。数据行流式进入聚合算子同时被分发到多个聚合桶里更新计数。比如一行(A, North, 2025-01, 1000)它既会被累加到(A)这个分组集的桶里也会被累加到(North)的桶里还会被累加到()的全局桶里。这种“单次读取、多桶更新”的模式相比多段 SQL 独立跑大表IO 节省非常可观。在代码可读性层面五个口径从五段 50 行的 SQL变成了一组括号列表。以后业务说“再给我加一个按月份的汇总”你在括号列表里加一组(sales_month)就行不用复制粘贴整段聚合 SQL。从执行计划和结果结构来看GROUPING SETS是 SQL 标准提供的正式能力各主流数据库的优化器也会针对它做专门的计划生成而不是简单展开成UNION ALL再跑。这也是我强烈建议在聚合报表场景用它替换UNION ALL的原因之一。3. 实战改写从 union all 版到 grouping sets 版的完整过程3.1 逐条对照你真的不需要五个分支我把上一节的五段UNION ALL改写为GROUPING SETS完整的 SQL 是这样SELECT product_category, region, sales_month, SUM(revenue) AS revenue FROM sales_summary GROUP BY GROUPING SETS ( (product_category), (region), (product_category, region), (product_category, sales_month), () ) ORDER BY product_category NULLS LAST, region NULLS LAST, sales_month NULLS LAST;可以看到原来的五个分支被压缩成了五个分组集。这里的分组集顺序我特意调整成了和原始需求一致先看单产品线、单区域、再看交叉、再看产品线加月份、最后全局。改写之后有几件事需要特别注意。第一结果列不需要手动补 NULL 对齐。数据库会自动为不属于某个分组集的列填入 NULL。比如“按产品线汇总”的行region和sales_month就是 NULL。第二聚合逻辑只写了一次。SUM(revenue)只在 SELECT 里出现一次五个分组集共用同一个聚合表达式。后面如果要把revenue改成SUM(amount * discount)只需要改动一个地方。第三过滤条件只写一处。如果业务要求只看某个时段的数据直接在FROM sales_summary后面加一个WHERE sales_month 2025-01-01即可所有分组集都会自动应用这个过滤不存在漏改某个分支的问题。3.2 结果集会是什么样子用一小段数据模拟一下假设表里只有三行数据product_categoryregionsales_monthrevenueANorth2025-01100ASouth2025-01200BNorth2025-0150执行上面的GROUPING SETS后结果会包含按产品线汇总(A, NULL, NULL, 300)、(B, NULL, NULL, 50)按区域汇总(NULL, North, NULL, 150)、(NULL, South, NULL, 200)按产品线加区域(A, North, NULL, 100)、(A, South, NULL, 200)、(B, North, NULL, 50)按产品线加月份(A, NULL, 2025-01, 300)、(B, NULL, 2025-01, 50)全局总计(NULL, NULL, NULL, 350)列与列之间用 NULL 隔开识别起来非常清晰。报表端只需要把这些 NULL 替换成“全部”之类的文案就是一张现成的多维汇总表。3.3 改写时我的验证习惯我每次改造这类 SQL都会做两个验证避免上线后数据对不上。第一个验证是“总数守恒”。拿改写前的UNION ALL结果和改写后的结果逐口径对比。比如“按产品线汇总”这一部分总和必须等于“按产品线加区域”这一部分的总和因为后者只是在前者基础上多拆了一层。同时所有分口径加起来和全局总计要一致。第二个验证是“行数核对”。如果原始 SQL 有五个分支改写后每个分组集的行数应该和对应分支的行数一致。比如SELECT GROUPING(product_category) AS p_grp, GROUPING(region) AS r_grp, COUNT(*) AS row_cnt, SUM(revenue) AS total_rev FROM sales_summary GROUP BY GROUPING SETS ( (product_category, region), (product_category), (region), () ) HAVING GROUPING(product_category) 0 AND GROUPING(region) 1;上面这段把结果限制在“按产品线汇总”的分组集里方便单独核对那一部分的行数和金额。GROUPING()函数你暂时可以先把它理解成“识别当前行属于哪个分组集”的工具我们下一章详细展开。4. 别被 NULL 骗了GROUPING() 与 GROUPING_ID() 的正确用法4.1 业务数据本身就有 NULL怎么办GROUPING SETS输出汇总行时不在分组内的列会被填成 NULL。问题来了如果业务数据本身就存在 NULL 值呢比如product_category字段因为历史原因没有录入某些明细行就是 NULL。在最终结果里你根本无法区分一行里的 NULL 是“这个维度被聚合了”还是“这个明细本身的取值就是 NULL”。这就是我在实际项目中踩过的最深的坑之一。当时接手一张报表看到product_category为 NULL 的行第一反应是“这是汇总行”结果追查半天才发现有一批明细数据的产品线字段本来就是空的。这类问题用眼睛看是看不出来的必须借助 SQL 内置的判定函数。4.2 GROUPING() 函数识别“这一行是否来自对应分组集”SQL 标准提供了GROUPING(column)函数它对每个输出列给出一个标记返回0表示当前行里该列是真正参与分组的列返回1表示当前行里该列被聚合了不参与当前分组。拿我们之前五分组集的例子来说对于“按产品线汇总”的行GROUPING(product_category) 0GROUPING(region) 1GROUPING(sales_month) 1。对于全局总计行三列都是1。更实用的用法是配合CASE WHEN把汇总行的 NULL 替换成可读的标签SELECT CASE WHEN GROUPING(product_category) 1 THEN 全部产品 ELSE product_category END AS product_category, CASE WHEN GROUPING(region) 1 THEN 全部区域 ELSE region END AS region, CASE WHEN GROUPING(sales_month) 1 THEN 全部月份 ELSE sales_month END AS sales_month, SUM(revenue) AS revenue FROM sales_summary GROUP BY GROUPING SETS ( (product_category), (region), (product_category, region), (product_category, sales_month), () );这样一来输出的报表里所有汇总行的维度列都变成了“全部产品”“全部区域”“全部月份”肉眼一看就懂报表工具也省了一步前处理。这是我在所有GROUPING SETS查询里都会做的事情强烈建议你也养成这个习惯。4.3 GROUPING_ID()用一列整数标记整行来源当分组集数量很多时逐个判断每一列的GROUPING()值仍然繁琐。标准里还提供了一个GROUPING_ID(column1, column2, ...)函数它把多列的GROUPING()结果拼接成一个整数位图一行就对应一个唯一的整数用来标记当前行到底属于哪个分组集。SELECT product_category, region, GROUPING(product_category) AS p_flag, GROUPING(region) AS r_flag, GROUPING_ID(product_category, region) AS group_id, SUM(revenue) AS revenue FROM sales_summary GROUP BY GROUPING SETS ( (product_category, region), (product_category), (region), () );在这个例子里group_id 0表示这一行是(product_category, region)交叉维度的结果group_id 1表示只按产品线汇总group_id 2表示只按区域汇总group_id 3表示全局总计。我在项目里通常把GROUPING_ID()的结果作为一个额外的标记列输出到报表底层表里。运营在做数据筛选或下钻时直接用这个整数过滤某个粒度比用CASE WHEN判断多个维度的 NULL 值干净得多。需要提醒一下GROUPING_ID的位权规则在不同数据库里存在一些细节差异参数顺序影响最终拼接结果正式使用前务必看一眼你所使用数据库的官方文档确认位权方向。核心思路是一致的细节以文档为准。5. grouping sets 与 ROLLUP、CUBE 的关系什么时候该用谁5.1 三个操作符的从属关系GROUPING SETS并不是孤立的知识点。SQL 标准里还有另外两个常见聚合操作符ROLLUP和CUBE。它们之间可以用公式互相表达ROLLUP (a, b)等价于GROUPING SETS ((a, b), (a), ())CUBE (a, b)等价于GROUPING SETS ((a, b), (a), (b), ())也就是说ROLLUP生成的是“层级上卷”的分组序列从最细粒度开始逐步去掉右边的维度CUBE则是所有列组合的全排列。GROUPING SETS是三者中最基础、最灵活的表达方式你可以任意指定要哪几组而不是被迫接受系统生成的全集。5.2 典型场景怎么选如果需求是“按月份、按季度、按年份看销售趋势”这种维度天然有层级关系用它对应的上卷方式会更省事SELECT sale_year, sale_quarter, sales_month, SUM(revenue) AS revenue FROM sales_summary GROUP BY ROLLUP (sale_year, sale_quarter, sales_month);如果需求是“探索性分析”想看看各个维度之间所有可能的组合又懒得手工枚举可以考虑CUBE。但如果需求是咱们文章开头那种“固定报表五种固定口径”GROUPING SETS就是最合适的选择。它不会多算你没要求的分组结果集行数可控执行开销也最小。5.3 一个典型错误示范滥用 CUBE 导致组合爆炸我见过一个真实案例某张表有 8 个维度列开发为了省事写了GROUP BY CUBE(col1, col2, ..., col8)觉得“反正把能算的都算出来”。结果呢2 的 8 次方等于 256 个分组集一次查询跑了 20 多分钟产出的行数多到报表端加载都吃力。CUBE的分组集数量是指数增长的。维度列一旦超过 6 个基本就是在给数据库制造灾难。遇到这种需求理性的做法是回到GROUPING SETS只列出业务真正需要的那几个分组集宁可多写几行括号也不要让无效计算跑半小时。表格总结一下三者的适用场景操作符分组集数量适合场景风险GROUPING SETS手动指定通常较少固定报表、明确口径无ROLLUP维度数 1层级维度上卷无CUBE2 的维度数次方探索性全维度分析维度多了会爆炸6. 实际项目中使用 grouping sets 的几个避坑点6.1 各数据库支持情况先说支持矩阵。这个语法不是某个数据库的私有特性而是 SQL 标准能力主流数据库基本都支持但版本上有些差异数据库支持情况备注PostgreSQL支持语法稳定文档清晰Oracle支持很早就支持 GROUPING SETSSQL Server支持支持 GROUPING、GROUPING_ID 函数MySQL8.0.31 起支持之前只能用 WITH ROLLUP 曲线救国ClickHouse支持支持 GROUPING SETSTiDB支持兼容 MySQL 语义StarRocks / Doris支持分析型场景常用如果你还在用 MySQL 8.0.30 以下版本GROUPING SETS是跑不了的。这时只能继续用WITH ROLLUP或UNION ALL等升级版本后再迁移。从实践角度看升级后的收益非常明显值得推动。6.2 排序和分页的坑GROUPING SETS的结果集是多个分组集的并集直接ORDER BY时NULL 的排序位置会因数据库而异。比如 PostgreSQL 默认NULLS LAST而 MySQL 默认 NULL 排在前面。如果报表端对顺序有严格预期建议显式指定排序规则ORDER BY CASE WHEN GROUPING(product_category) 1 THEN 0 ELSE 1 END, CASE WHEN GROUPING(region) 1 THEN 0 ELSE 1 END, product_category NULLS LAST, region NULLS LAST;上面这段把“汇总行”排在最前面然后再按维度值排序。不要小看这个细节它直接决定报表工具拿到数据后是否需要额外的重排逻辑。另外如果要对结果分页比如每页 50 行很可能出现某一页上半部分是某个分组集、下半部分是另一个分组集的情况。维护多粒度报表的分页时最好按GROUPING_ID值先做粒度切分再在每个粒度内部分页。6.3 性能观察什么时候收益最大我在项目中做过一组对照组测试数据量约 1000 万行需要计算 5 个分组集。UNION ALL版本的执行时间大约是 40 秒而GROUPING SETS版本大约是 15 秒左右。数据量越大、分组集越多两者的差距越明显。原因不复杂UNION ALL的每个分支各自扫描一次表IO 和 CPU 消耗线性累加而GROUPING SETS只做一次基础数据读取内部的聚合状态是并行维护的。不过要注意如果底层数据本来就只有几千行或分组集只有两三个两者性能差异基本感知不出来。这时候优先考虑的是代码可维护性而不是性能。还有一点经验GROUPING SETS和窗口函数是可以配合使用的。先分组聚合完再在结果集上做一次窗口计算比如计算每个产品线在全局总额中的占比。这比先聚合再回表 JOIN 简单得多也不容易出错。6.4 我的最后一条建议从维护历史 SQL 的角度我每次看到超过三段UNION ALL拼出来的聚合报表都会建议项目组优先改写成GROUPING SETS。这不是炫技而是因为这类 SQL 修改频率极高业务加一个维度、改一个过滤条件都是常事。GROUPING SETS把改动点收敛到一处后续维护的人会感谢你。如果你以前没有用过它先从最简单的两三个分组集开始练手跑通一条 SQL再逐步替代项目里的复杂UNION ALL。等熟悉了GROUPING()函数和你所用数据库的GROUPING_ID位权规则这套操作就会变成聚合报表场景里的默认选项。我在做了这个迁移之后最大的感受是原来写报表 SQL 时那种“复制、粘贴、改字段”的模式终于清静了剩下的精力可以放在真正需要思考的业务口径上。