高级聚合 · Hadoop2.x

grouping sets 高级聚合本质是多重`group by`进行`union all`操作。 ```sql SELECT a,b,SUM(c) FROM tab1 GROUP BY a,b GROUPING SETS((a,b)，a，b,()); -- 等价于 SELECT a,b,SUM(c) FROM tab1 GROUP BY a,b union SELECT a,null,SUM(c) FROM tab1 GROUP BY a,null union SELECT null,b,SUM(c) FROM tab1 GROUP BY null,b union SELECT null,null,SUM(c) FROM tab1; ``` :-: 表 GROUPING SETS 及等价语句 | GROUPING SETS 语句 | 等价于 GROUP BY 语句 | | --- | --- | | SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a, b), a, b, ( )); | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b UNION SELECT null, null, SUM( c ) FROM tab1; | | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a); | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a; | | SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) ); | SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b; | | SELECT a,b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS (a,b); | SELECT a, null, SUM( c ) FROM tab1 GROUP BY a UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY b; | **`GROUP BY WITH CUBE|ROLLUP`** >`CUBE`：对分组列进行所有可能组合的聚合，是 group by 字段的所有组合 `ROLLUP`：计算维度层次级别上的聚合 :-: 表 ROLLUP/CUBE 及等价语句 | ROLLUP/CUBE 语句 | 等价于 GROUP BY 语句 | | --- | --- | | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b, c WITH CUBE; | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )); | | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b, c WITH ROLLUP; | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( )); |