MaxCompute - ODPS重装上阵　第七弹 - Grouping Set, Cube and Rollup

MaxCompute（原ODPS）是阿里云自主研发的具有业界领先水平的分布式大数据处理平台, 尤其在集团内部得到广泛应用，支撑了多个BU的核心业务。 MaxCompute除了持续优化性能外，也致力于提升SQL语言的用户体验和表达能力，提高广大ODPS开发者的生产力。

MaxCompute基于ODPS2.0新一代的SQL引擎，显著提升了SQL语言编译过程的易用性与语言的表达能力。我们在此推出MaxCompute(ODPS2.0)重装上阵系列文章

第六弹向您介绍了User Defined Type，本篇将向您介绍MaxCompute对GROUPING SETS的支持。

场景

由于业务需求，需要经常对数据进行多维度的聚合分析，如既需要对a列做聚合也要对b列做聚合，同时也要按照a、b两列同时做聚合，所以，不得不写很多很多的UNION ALL，因此造成了很多重复代码，维护起来不方便。

该场景的问题，可以通过使用Grouping Sets能够非常好地解决。

本文中很多例子采用MaxCompute Studio作展示，没有安装MaxCompute Studio的用户，可以参照wiki安装MaxCompute Studio，导入测试MaxCompute项目，创建工程。

MaxCompute中的GROUPING SETS功能是SELECT语句中GROUP BY子句的扩展。允许采用多种方式对结果分组，而不必使用多个SELECT语句来实现这一目的。这样能够使MaxCompute的引擎给出更有的执行计划，从而提高执行性能。

如下例子：

之前解法，必须使用多个 SELECT 语句计算多个分组，并且用UNION ALL把它们连接起来：

通过MaxCompute Studio的执行图，我们可以看出，物理执行计划是做了3次聚合，然后再UNION起来。

GROUPING SETS语法和普通GROUP BY类似，但需要额外执行所需的多个GROUP BY组合。例如以上SQL的((os, device), (city), ())，请注意这是一个2层的括号，每个内层括号执行一个GROUP BY组合；空括号表示GROUP BY列表为空，即COUNT所有列。
观察MaxCompute Studio的执行图，我们发现，物理执行计划只包含一个Reduce阶段，无需进行UNION操作。

请注意:

若分组集里不使用表达式，系统会使用NULL充当占位符，使得这些结果集可以做UNION操作。例如，结果第 1-5 行的city列。
相比于UNION多个group by的实现，GROUPINGSETS方式在总的资源消耗上面占优的。但使用GROUPINGSETS会使Reducer的阶段变少，如上例，从3个（R2_1, R3_1, R4_1）变为1个(R2_1), 从而导致总的Reducer instance数变少，可能会使任务端到端时间变长。
这种情况建议使用 odps.sql.reducer.instances手动调大reducer的instance数目。例如上面的示例，可以 set odps.sql.reducer.instances=3; 来保持和原来instance数不变。

CUBE和ROLLUP可以认为是特殊的GROUPING SETS。

CUBE会枚举指定列的所有可能组合作为GROUPING SETS。而ROLLUP会以按层级聚合的方式产生GROUPING SETS。

例如：

GROUP BY CUBE(a, b, c) 等价于 GROUPING SETS( (a,b,c), (a,b), (a,c), (b,c), (a) ,(b), (c), () )。
GROUP BY ROLLUP(a,b,c) 等价于 GROUPING SETS( (a,b,c), (a,b), (a), () )。

CUBE会把GROUP BY列进行全量组合，即N个列会产生 2^N中组合，故不建议N的数目超过5。