SparkSQL GROUP BY子句中如何混用分组表达式与CUBE表达式

问题原因 #

SparkSQL分组语法规则与PostgreSQL、Presto存在差异，group by 普通字段, cube(字段列表)的混合写法当前不受SparkSQL支持，会抛出java.lang.UnsupportedOperationException异常，需按照SparkSQL官方定义的混合分组规则改写。

正确改写方案 #

你要实现的按a固定分组，同时对b、c做CUBE聚合的逻辑，可以用以下两种等价写法：

写法1：CUBE嵌套固定字段+过滤 #

select a,b,c,sum(d) from table group by cube(a, b, c)
-- 过滤掉cube生成的a为空的聚合行，匹配原需求
having grouping(a) = 0

写法2：直接改写为GROUPING SETS（性能更优，无多余计算） #

按照官方混合分组规则，group by a, cube(b,c)等价于对a这个单元素分组集和cube(b,c)生成的分组集做笛卡尔积，最终可直接写为：

select a,b,c,sum(d) from table 
group by grouping sets(
    (a,b,c),
    (a,b),
    (a,c),
    (a)
)

官方规则说明 #

混合/嵌套分组分析
GROUP BY子句可包含多个group_expressions以及多个CUBE|ROLLUP|GROUPING SETS。GROUPING SETS内部也可以嵌套CUBE|ROLLUP|GROUPING SETS子句，例如GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location)), GROUPING SETS(warehouse, GROUPING SETS(location, GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location))))。CUBE|ROLLUP只是GROUPING SETS的语法糖，如需了解CUBE|ROLLUP转换为GROUPING SETS的方法，请参考上文章节。在该场景下，group_expression可视为仅包含单个分组的GROUPING SETS。若GROUP BY子句中存在多个GROUPING SETS，我们会对原始的多个GROUPING SETS做笛卡尔积，生成单个GROUPING SETS。若GROUPING SETS子句中存在嵌套的GROUPING SETS，我们会直接提取其内部的分组集并去除外层嵌套。例如，GROUP BY warehouse, GROUPING SETS((product), ()), GROUPING SETS((location, size), (location), (size), ()) 和 GROUP BY warehouse, ROLLUP(product), CUBE(location, size) 等价于 GROUP BY GROUPING SETS( (warehouse, product, location, size), (warehouse, product, location), (warehouse, product, size), (warehouse, product), (warehouse, location, size), (warehouse, location), (warehouse, size), (warehouse))。

GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product))) 等价于 GROUP BY GROUPING SETS((warehouse), (warehouse, product))。

内容的提问来源于stack exchange，提问作者ze.yu