Hive之多维度聚合

左手的ㄟ右手 2022-12-27 14:19 143阅读 0赞

# 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