hive 高级分组聚合(grouping sets cube和rollup)

1.grouping sets

1.1

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

相当于 select a,b,sum(c) from tbl group by a,b

1.2

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

相当于

select a,b,sum(c) from tbl group by a,b

union

select a,null ,sum(c) from tbl group by a

1.3

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

相当于

select a,null,sum(c) from tbl group by a

union

select null ,b,sum(c) from tbl group by b

1.4

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

相当于

select a,b,sum(c) from tbl group by a,b

union

select a,null,sum(c) from tbl group by a

union

select null,b,sum(c) from tbl group by b

union

select null,null,sum(c) from tbl

2.cube 会计算所有group by 列的所有组合

select a,b,c,count(1) from tbl group by a,b,c with cub