Hive之多维度聚合

Hive之多维度聚合

多维聚合

1、grouping sets

语法: select ... from .. where .. group by A,B,C grouping sets( (A),(A,B),(A,B,C),.. )

注意:grouping sets后面在进行维度组合的时候字段只能是group by 后面的字段

select A,B,C,count(D) from school group by A,B,C grouping sets( (A),(A,B),(A,B,C) )

等价于:

select A,B,C,count(D) from school group by A,B,C
union all
select A,B,null C,count(D) from school group by A,B
union all
select A,null B,null C,count(D) from school group by A

2、cube[全维度的组合]

语法: select ... from .. where .. group by A,B,C with cube

select A,B,C,count(D) from school group by A,B,C with cube

等价于

select A,B,C,count(D) from school group by A,B,C
union all
select A,B,null C,count(D) from school group by A,B
union all 
select null A,B,C,count(D) from school group by B,C
union all
select A,null B,C,count(D) from school group by A,C
union all
select A,null B,null C,count(D) from school group by A
union all 
select null A,B,null C,count(D) from school group by B
union all
select null A,null B,C,count(D) from school group by C
union all
select null A,null B,null C,count(D) from school

3、rollup

语法: select ... from .. where .. group by A,B,C with rollup

rollup查询的时候按照group by的字段从右向左一次递减一个字段

select A,B,C,count(D) from school group by A,B,C with rollup

等价于:

select A,B,C,count(D) from school group by A,B,C
union all 
select A,B,null C,count(D) from school group by A,B
union all
select A,null,B,null C,count(D) from school group by A
union all
select null A,null B,null C,count(D) from school