HIVE多维分析函数：grouping sets、cube、rollup

HIVE多维分析函数：grouping sets、cube、rollup

HIVE的多维分析函数，配合group by 使用，通常的group by只能对单维度的字段进行分析聚合操作，如果想要获取不同组合字段的聚合操作，就需要写多个group by 组合，HIVE提供了内置的函数用于上述这种多维分析

一、grouping sets

可以指定任意group组合字段

语法

select 
  k1,k2,k3,sum(v1)
from
  tbl
group by 
  k1,k2,k3
grouping sets
  ((组合1),(组合2),(组合3))
1
2
3
4
5
6
7
8

举例

比如有一个表，里面有3个字段：department、name、age，我们需要求出department和name为一组的count，name和age为一组的count，department和name和age为一组的count，总count，应该怎么做到？

按照传统的group by，应该聚合各种组合然后union all实现，如下：

select department,name,null,count(1) from tbl group by department,name
union all 
select null,name,age,count(1) from tbl group by name,age
union all 
select department,name,age,count(1) from tbl group by department,name,age
union all 
select null,null,null,count(1) from tbl
1
2
3
4
5
6
7

读取了4次tbl，求得了4个结果，然后union all在了一起，如果使用grouping sets，如下：

select 
  department,name,age,count(1) 
from tbl 
  group by department,name,age 
grouping sets ((department,name,age),(department,name),(name,age),())
1
2
3
4
5

简直不要太方便

注意，对于grouping sets 后的组合，应该是group by后的子集，对于没有使用到的group by字段，会自动使用null值填充

二、cube

cube同样是HIVE提供的多维分析的内置函数，可以看作是全组合版的grouping sets

语法

select 
  k1,k2,k3,sum(v1)
from
  tbl
group by 
  k1,k2,k3
with cube
1
2
3
4
5
6
7

举例

还是上述的例子，对于department、name、age，如果需要求所有的组合聚合结果，使用grouping sets实现，如下：

select 
  department,name,age,count(1) 
from tbl 
  group by department,name,age 
grouping sets ((department,name,age),(department,name),(department,age),(department),(name,age),(name),(age),())
1
2
3
4
5

而函数cube直接就帮你全组合了字段，使用cube实现，如下：

select 
  department,name,age,count(1) 
from tbl 
  group by department,name,age 
with cube
1
2
3
4
5

这里with cube就等同于grouping sets ((department,name,age),(department,name),(department,age),(department),(name,age),(name),(age),())

注意，cube会返回所有group by后的字段全组合的聚合结果，对于未使用到的组合，会用null值填充

三、rollup

rollup的含义是卷曲的意思，顾名思义，就是会从右向左的组合字段，得到聚合结果

语法

select 
  k1,k2,k3,sum(v1)
from
  tbl
group by 
  k1,k2,k3
with rollup
1
2
3
4
5
6
7

举例

对于cube，虽然很方便的返回全组合的聚合结果，但是在实际使用中可能不需要这样，还是上个例子，对于字段department、name、age，如果想要得到(department,name,age), (department, name),(department),()的组合，使用grouping sets实现，如下：

select 
  department,name,age,count(1) 
from tbl 
  group by department,name,age 
grouping sets ((department,name,age),(department,name),(department,age),(department),())
1
2
3
4
5

如果使用rollup实现，如下：

select   department,name,age,count(1) from tbl   group by department,name,age with rollup
1

这里的with rollup就等同于grouping sets ((department,name,age),(department,name),(department,age),(department),())

注意，rollup的组合顺序是按照group by后的字段，从左到右迭代的，对于未使用到的字段，会使用null值填充