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

GROUPING SETS — 高效实现多维聚合分析Hive / SparkHive 官方文档 - Grouping SetsSpark 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 SETSGROUPING SETS是 SQL 提供的一种多维分组聚合语法允许我们在一条 SQL 中指定多个分组组合从而同时计算不同维度、不同粒度的聚合结果。简单来说原本需要通过多条 GROUP BY 查询再使用 UNION ALL 拼接的聚合逻辑现在可以通过 GROUPING SETS 统一实现。linkhttps://cwiki.apache.org/confluence/display/Hive/EnhancedAggregation%2CCube%2CGroupingandRollup2.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。假设原始数据如下cityproductsales北京手机100NULL电脑50当使用上述 SQL 进行聚合时可能得到以下结果cityproducttotal_sales北京手机100ALL电脑50北京ALL100ALLALL50ALLALL150可以看到ALL / 电脑这一行实际上来自原始数据中的city NULL并不是跨城市的汇总结果。更明显的是结果中出现了两条ALL / ALL但一条代表城市为空的数据小计另一条代表全局汇总。显然单纯依靠COALESCE()我们已经无法准确判断这两条记录的统计粒度。因此这种方式更适合维度字段能够保证非空且只需要简单展示聚合结果的场景。如果业务数据中可能存在真实的NULL就需要更严谨的处理方式。3.2 方法二使用 GROUPING() 精准识别汇总行为了解决上述问题SQL 提供了GROUPING()函数用于判断某个字段是否参与当前分组。它的返回值只有两种返回值含义0当前字段参与分组即使字段值本身为 NULL1当前字段未参与分组其 NULL 为聚合占位符有了这个函数我们就可以准确区分两种不同来源的NULL。SELECTCASEWHENGROUPING(city)1THENALLELSEcityENDAScity,CASEWHENGROUPING(product)1THENALLELSEproductENDASproduct,SUM(sales)AStotal_salesFROMsalesGROUPBYGROUPING SETS((city,product),(city),());与COALESCE()不同这里只有在字段未参与当前分组时才会将其替换成ALL。对于刚才的示例数据结果就会变成cityproducttotal_sales北京手机100NULL电脑50北京ALL100NULLALL50ALLALL150这样一来原始数据中的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(city)GROUPING(product)二进制GROUPING_ID(city, product)00000(city)01011(product)10102()11113其计算逻辑可以表示为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_id1;相比通过product IS NULL判断城市汇总这种写法更加准确因为它不会受到原始数据中真实NULL值的影响。场景三辅助调试复杂的多维聚合 SQL当聚合涉及多个维度时输出结果中的NULL可能来自不同的分组组合。此时在查询结果中保留grouping_id就可以直观地判断每一行对应哪一种聚合粒度降低排查问题的成本。3.5 小结三个与多维聚合结果识别相关的函数函数主要作用COALESCE()将 NULL 替换为指定值便于展示GROUPING()判断单个字段是否参与当前分组GROUPING_ID()将多个字段的分组状态编码为整数在实际开发中我更倾向于使用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) 种分组组合。例如维度数量分组组合数量2438416532101,024当维度较多时部分组合可能并没有实际业务价值却仍然需要参与计算从而增加额外的计算开销。因此CUBE更适合维度数量可控、且确实需要全面交叉分析的场景。4.3 GROUPING SETS更灵活的自定义聚合回到GROUPING SETS它最大的特点就是不受固定分组模式的限制可以根据业务需求自由选择聚合粒度。例如业务看板只需要展示以下三种统计结果地区 品类地区全局汇总那么我们可以直接指定GROUPBYGROUPING SETS((region,product_category),(region),())相比CUBE这种方式不会额外计算单独按品类汇总的结果相比ROLLUP它又允许我们根据业务需求灵活调整分组组合。总结一下三种语法的差异如下语法聚合方式典型场景ROLLUP按维度顺序逐级汇总组织、地域等层级统计CUBE枚举所有维度组合OLAP 多维交叉分析GROUPING SETS自定义分组组合业务看板、指定粒度汇总实际使用时并不存在绝对最优的选择关键还是看业务需要哪些聚合结果。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或许就能带来一些新的思考。保持好奇多问一句“为什么这样写”或许就是技术积累最自然的方式。