-
hive 实现多个字段的组合聚合,也可以上卷下钻
-
Enhanced Aggregation, Cube, Grouping and Rollup
Grouping sets, CUBE and ROLLUP operators, and the GROUPING__ID function were added in Hive 0.10.0.
Table 1 - GROUPING SET queries and the equivalent GROUP BY queries
Aggregate Query with GROUPING SETS | Equivalent Aggregate Query with GROUP BY |
---|---|
Aggregate Query with GROUPING SETS | Equivalent Aggregate Query with GROUP BY |
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), a) | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
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, null, SUM( c ) FROM tab1 GROUP BY a
SELECT null, b, SUM( c ) FROM tab1 GROUP BY b |
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
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null
SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b
SELECT null, null, SUM( c ) FROM tab1 |
其他具体使用见 :
https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation,+Cube,+Grouping+and+Rollup