企业官网建设流程全解析

GROUPING SETS — 高效实现多维聚合分析（Hive / Spark）

• Hive 官方文档 - Grouping Sets
• Spark SQL 支持的 GROUPING SETS

一、问题发现：看板背后的 SQL 引发的好奇

最近在与数据分析师（DA）同学对接，搭建业务看板、Review 他提交的 SQL 脚本时，注意到一段让我眼前一亮的写法（Demo不涉及具体业务逻辑，仅做演示）：

SELECTregion,product_category,channel,SUM(sales_amount)AStotal_salesFROMdws_sales_detailGROUPBYregion,product_category,channel GROUPING SETS((region,product_category,channel),(region,product_category),(region),());

并不是我熟悉的GROUP BY或UNION ALL写法。

虽然能大致猜到它的作用——同时输出多个粒度的聚合结果（比如“地区+品类+渠道”、“地区+品类”、“仅地区”以及“全局总计”），但从未在日常开发中使用过GROUPING SETS。

出于职业敏感，意识到这可能是一个被我长期忽略的 SQL 高级特性。于是决定深入研究一下：它到底是什么？为什么 DA 会用它？相比传统写法有什么优势？是否值得在我们团队推广？

二、什么是 GROUPING SETS？

GROUPING SETS是 SQL 提供的一种多维分组聚合语法，允许我们在一条 SQL 中指定多个分组组合，从而同时计算不同维度、不同粒度的聚合结果。

简单来说，原本需要通过多条 GROUP BY 查询，再使用 UNION ALL 拼接的聚合逻辑，现在可以通过 GROUPING SETS 统一实现。

link：https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup

2.1 从一个简单的例子理解

假设我们有一张销售明细表 sales_table，其中包含地区（region）、产品品类（product_category）和销售额（sales）三个字段。

现在，我们希望在业务看板中同时展示以下四种统计结果：

• 地区 + 品类：每个地区、每种产品的销售额
• 地区：每个地区的总销售额
• 品类：每种产品在所有地区的总销售额
• 全局汇总：所有地区、所有产品的总销售额

按照传统思路，我们可以分别编写四条 GROUP BY 查询，再通过 UNION ALL 合并结果。
这种方式虽然直观，但当统计维度不断增加时，重复的 SQL 逻辑也会随之增多，代码维护和后续扩展都会变得比较繁琐。

而使用 GROUPING SETS，上述需求可以直接写成：

SELECTregion,product_category,SUM(sales)AStotal_salesFROMsales_tableGROUPBYGROUPING SETS((region,product_category),-- 地区 + 品类(region),-- 地区汇总(product_category),-- 品类汇总()-- 全局汇总);

这里，GROUPING SETS 中的每一个括号，都代表一种独立的分组粒度。

例如，(region, product_category) 表示按地区和品类聚合，(region) 表示仅按地区聚合，而空括号 () 则表示不按照任何维度分组，直接计算全局汇总结果。

从逻辑上看，这条 SQL 相当于执行了四种不同的 GROUP BY 聚合，再将结果合并到同一个结果集中。

需要注意的是，对于某一种分组组合，未参与当前分组的维度字段会以 NULL 作为占位符。

例如，当结果按 region 汇总时，product_category 就会显示为 NULL；而在全局汇总行中，两个维度字段都会显示为 NULL。

不过，这里也隐藏着一个容易被忽略的问题：如何区分聚合产生的 NULL，与原始数据中真实存在的 NULL？

这个问题我们会在下一章结合 GROUPING() 函数进一步讨论。

三、如何识别汇总行？——从 NULL 处理到聚合粒度标识

上一章提到，使用GROUPING SETS进行多维聚合时，未参与当前分组的字段会以NULL作为占位符。

但这里存在一个容易被忽略的问题：业务数据本身也可能包含真实的NULL值。

例如，某条销售记录的城市信息缺失，city本身就是NULL。而当我们按照产品品类进行汇总时，city同样会显示为NULL。

虽然两种情况下的字段值相同，但它们的业务含义却完全不同：

• 原始 NULL：该字段参与了分组，但源数据中的值本身为空。
• 汇总 NULL：该字段未参与当前分组，由聚合操作生成的占位符。

如果不加以区分，就可能导致报表展示不准确，甚至影响后续的数据分析。

针对这个问题，我们可以采用以下两种处理方式。

3.1 方法一：使用 COALESCE 替换 NULL

最直观的方式是通过COALESCE()函数，将NULL替换成更容易理解的标识，例如ALL。

SELECTCOALESCE(city,'ALL')AScity,COALESCE(product,'ALL')ASproduct,SUM(sales)AStotal_salesFROMsalesGROUPBYGROUPING SETS((city,product),(city),());

这样处理后，结果中的NULL会统一显示为ALL，方便业务人员理解。

不过，这种写法存在一个明显的问题：COALESCE()无法区分原始数据中的 NULL 和聚合产生的 NULL。

假设原始数据如下：

当使用上述 SQL 进行聚合时，可能得到以下结果：

可以看到，ALL / 电脑这一行实际上来自原始数据中的city = NULL，并不是跨城市的汇总结果。

更明显的是，结果中出现了两条ALL / ALL，但一条代表城市为空的数据小计，另一条代表全局汇总。

显然，单纯依靠COALESCE()，我们已经无法准确判断这两条记录的统计粒度。

因此，这种方式更适合维度字段能够保证非空，且只需要简单展示聚合结果的场景。

如果业务数据中可能存在真实的NULL，就需要更严谨的处理方式。

3.2 方法二：使用 GROUPING() 精准识别汇总行

为了解决上述问题，SQL 提供了GROUPING()函数，用于判断某个字段是否参与当前分组。

它的返回值只有两种：

有了这个函数，我们就可以准确区分两种不同来源的NULL。

SELECTCASEWHENGROUPING(city)=1THEN'ALL'ELSEcityENDAScity,CASEWHENGROUPING(product)=1THEN'ALL'ELSEproductENDASproduct,SUM(sales)AStotal_salesFROMsalesGROUPBYGROUPING SETS((city,product),(city),());

与COALESCE()不同，这里只有在字段未参与当前分组时，才会将其替换成ALL。

对于刚才的示例数据，结果就会变成：

这样一来，原始数据中的NULL就得到了保留，而聚合产生的占位符则被明确标记为ALL。

相比COALESCE()，这种方式能够避免将真实缺失值误判为汇总行，更适合对数据准确性要求较高的场景。

不过，随着分组维度增加，如果每个字段都通过GROUPING()单独判断，SQL 仍然会变得比较繁琐。

这时，我们就可以进一步使用GROUPING_ID，为不同的聚合粒度生成统一标识。

3.3 进阶用法：使用 GROUPING_ID 标识聚合粒度

GROUPING_ID可以理解为多个GROUPING()判断结果的组合。

它将各个字段是否参与当前分组的状态编码为一个二进制数，再转换成十进制整数，从而标识不同的聚合粒度。

以 Spark SQL 中的GROUPING_ID(city, product)为例：

SELECTcity,product,GROUPING_ID(city,product)ASgrouping_id,SUM(sales)AStotal_salesFROMsalesGROUPBYGROUPING SETS((city,product),(city),(product),());

对于city和product两个维度，可以得到以下四种分组状态：

其计算逻辑可以表示为：

GROUPING_ID(city, product) = GROUPING(city) × 2 + GROUPING(product)

这里需要注意的是，字段顺序决定了二进制位的顺序：左侧字段对应高位，右侧字段对应低位。

因此，GROUPING_ID(city, product)和GROUPING_ID(product, city)并不完全等价。

理解这个规则后，就能快速判断每一种GROUPING_ID对应的聚合粒度。

3.4 GROUPING_ID 在实际开发中的应用

在实际开发中，GROUPING_ID不只是一个辅助判断函数，它还可以用于结果标记、粒度筛选以及复杂 SQL 的调试。

场景一：为不同聚合粒度添加业务标签

例如，在数据看板中，我们希望明确区分城市和品类明细、城市汇总、品类汇总以及全局汇总。

SELECTcity,product,GROUPING_ID(city,product)ASgrouping_id,CASEGROUPING_ID(city,product)WHEN0THEN'城市 + 品类'WHEN1THEN'城市汇总'WHEN2THEN'品类汇总'WHEN3THEN'全局汇总'ENDASagg_type,SUM(sales)AStotal_salesFROMsalesGROUPBYGROUPING SETS((city,product),(city),(product),());

这样，下游就可以直接根据agg_type或grouping_id区分不同的统计粒度，而不需要再通过字段是否为NULL进行推断。

场景二：筛选指定粒度的聚合结果

例如，我们只希望保留城市维度的汇总数据。

可以在聚合完成后，利用grouping_id进行筛选：

WITHaggAS(SELECTcity,product,GROUPING_ID(city,product)ASgrouping_id,SUM(sales)AStotal_salesFROMsalesGROUPBYGROUPING SETS((city,product),(city),(product),()))SELECTcity,total_salesFROMaggWHEREgrouping_id=1;

相比通过product IS NULL判断城市汇总，这种写法更加准确，因为它不会受到原始数据中真实NULL值的影响。

场景三：辅助调试复杂的多维聚合 SQL

当聚合涉及多个维度时，输出结果中的NULL可能来自不同的分组组合。

此时，在查询结果中保留grouping_id，就可以直观地判断每一行对应哪一种聚合粒度，降低排查问题的成本。

3.5 小结

三个与多维聚合结果识别相关的函数：

在实际开发中，我更倾向于使用GROUPING()区分真实数据与汇总占位符，通过GROUPING_ID标识不同的聚合粒度。

这样不仅能够保证结果的语义准确，也方便后续的数据加工和 BI 看板使用。

需要补充的是，以上GROUPING_ID(col1, col2)示例以 Spark SQL 为准。Hive 在对应功能的命名和不同版本行为上存在差异，使用时需要结合实际引擎版本确认哈🤣。

四、GROUPING SETS、ROLLUP 与 CUBE 的关系

了解了GROUPING SETS的基本用法后，我们再来看两个与它密切相关的 SQL 特性：ROLLUP和CUBE。

实际上，这三者都属于 SQL 的多维聚合能力。它们的核心区别在于：如何确定需要计算哪些分组组合。

简单来说：

• GROUPING SETS：手动指定需要的分组组合，灵活性最高。
• ROLLUP：按照指定的维度顺序，逐级向上汇总。
• CUBE：计算所有维度组合对应的聚合结果。

从表达能力上看，ROLLUP和CUBE都可以看作GROUPING SETS的简写形式。

4.1 ROLLUP：适用于层级式汇总

ROLLUP适用于具有明确层级关系的统计场景，例如：

省份 → 城市 → 门店

假设我们希望分别统计门店、城市、省份以及全国四个层级的销售额，可以使用：

GROUPBYROLLUP(province,city,store)

等价于：

GROUPBYGROUPING SETS((province,city,store),(province,city),(province),())

可以看到，ROLLUP会按照字段顺序，依次去掉最右侧的维度，直到生成全局汇总。

因此，对于包含 N 个维度的ROLLUP，会生成N + 1 种分组组合。

需要注意的是，ROLLUP的字段顺序非常重要。不同的字段顺序，代表不同的汇总路径。

4.2 CUBE：适用于全维度组合分析

与ROLLUP不同，CUBE不要求维度之间具有层级关系，而是会生成指定维度的所有分组组合。

例如：

GROUPBYCUBE(region,product_category)

等价于：

GROUPBYGROUPING SETS((region,product_category),(region),(product_category),())

这种方式适合需要从多个维度自由切换分析视角的场景。

不过，CUBE也有一个需要注意的问题：分组组合的数量会随着维度增加而指数级增长。

对于 N 个维度，CUBE会生成 (2^N) 种分组组合。

例如：

当维度较多时，部分组合可能并没有实际业务价值，却仍然需要参与计算，从而增加额外的计算开销。

因此，CUBE更适合维度数量可控、且确实需要全面交叉分析的场景。

4.3 GROUPING SETS：更灵活的自定义聚合

回到GROUPING SETS，它最大的特点就是不受固定分组模式的限制，可以根据业务需求自由选择聚合粒度。

例如，业务看板只需要展示以下三种统计结果：

• 地区 + 品类
• 地区
• 全局汇总

那么我们可以直接指定：

GROUPBYGROUPING SETS((region,product_category),(region),())

相比CUBE，这种方式不会额外计算单独按品类汇总的结果；相比ROLLUP，它又允许我们根据业务需求灵活调整分组组合。

总结一下，三种语法的差异如下：

实际使用时，并不存在绝对最优的选择，关键还是看业务需要哪些聚合结果。

4.4 回到最初的问题：为什么业务看板会使用 GROUPING SETS？

现在，我们再回到文章开头的场景。

为什么 DA 同学会在业务看板的 SQL 中使用GROUPING SETS，而不是传统的GROUP BY + UNION ALL？

结合前面的分析，我认为主要有以下几个原因。

1. 减少重复 SQL，统一聚合逻辑

当多个统计粒度使用相同的数据来源、过滤条件和聚合指标时，使用GROUPING SETS可以避免重复编写大量相似的 SQL。

这不仅让代码更加简洁，也降低了不同统计口径之间出现逻辑偏差的风险。

2. 有机会减少重复计算

传统的UNION ALL写法通常需要编写多条独立的聚合查询，而GROUPING SETS将多个统计粒度统一到一条查询中，使计算引擎有机会复用数据读取和部分聚合计算。

在 Hive、Spark SQL 等分布式计算引擎中，这种方式可能减少重复的数据扫描和计算开销。

但需要注意，GROUPING SETS并不意味着一定只扫描一次数据，也不代表性能必然优于UNION ALL。

实际执行效率仍然取决于数据规模、分组组合数量、字段基数以及引擎生成的物理执行计划。

3. 更适合业务看板的多粒度统计需求

业务看板往往需要同时展示整体指标和不同维度的细分指标。

例如：

• 整体销售额
• 各地区销售额
• 各地区、各品类销售额
• 各地区、各品类、各渠道销售额

这些统计结果具有相同的指标口径，但聚合粒度不同，正好符合GROUPING SETS的应用特点。

4. 方便下游统一消费聚合结果

结合前面介绍的GROUPING_ID，还可以为不同粒度的结果添加统一标识。

这样，下游的数据加工任务或 BI 看板就可以直接通过粒度标识筛选所需的数据，而不必维护多套独立的聚合结果。

当然，这也要求我们在设计结果表时，明确区分不同的聚合粒度，避免在后续统计中重复汇总。

总的来说，GROUPING SETS的价值不仅在于简化 SQL，更在于将多个相关的聚合需求统一建模和管理。

对于需要同时产出多个统计粒度的数仓开发场景，它是一种值得考虑的实现方式。

• CUBE(a, b)=GROUPING SETS( (a,b), (a), (b), () )→ 所有组合

当需要非对称或自定义的分组组合时（比如只要(a,b)和()，不要(a)），就必须用GROUPING SETS。

五、写在最后

回过头来看，这次对GROUPING SETS的学习，起点其实只是一次日常的 SQL Review。

一个之前没有留意过的 SQL 写法，让我重新认识了多维聚合的实现方式，也意识到很多看似熟悉的技术工具，其实还有不少值得深入探索的地方。

GROUPING SETS本身并不复杂，但它提供了另一种解决问题的思路：当多个统计需求具有相同的数据来源和聚合逻辑时，与其分别处理，不如尝试将它们统一起来。

当然，技术方案没有绝对的优劣。相比单纯追求 SQL 写得更短，理解不同方案的适用场景、执行原理和潜在成本，才是更重要的事情。

这次经历也提醒了我，日常开发中的 Code Review 不只是检查代码是否正确，也是一个学习和交流的机会。有时候，一段不熟悉的 SQL，或许就能带来一些新的思考。

保持好奇，多问一句“为什么这样写”，或许就是技术积累最自然的方式。