hive 分析函数 grouping sets, cube, rollup

今天记录一下在OLAP中常用的分析函数，这几个函数可以大大简化sql，使代码更优美，效率更高

准备数据：test表

school            grade     user
hf第一中学    一年级    张三1
hf第一中学    二年级    张三2
hf第一中学    三年级    张三3
hf第一中学    四年级    张三4
hf第一中学    五年级    张三5
hf第一中学    六年级    张三6
hf第二中学    一年级    李四1
hf第二中学    二年级    李四2
hf第二中学    三年级    李四3
hf第二中学    四年级    李四4
hf第二中学    五年级    李四5
hf第二中学    六年级    李四6
hf第三中学    一年级    王二1
hf第三中学    二年级    王二2
hf第三中学    三年级    王二3
hf第三中学    四年级    王二4
hf第三中学    五年级    王二5
hf第三中学    六年级    王二6

grouping sets 在一个group by 的聚合查询中，根据不同的维度进行组合，我们可以选择性的去对维度聚合，也可以聚合所有维度，当维度上卷聚合时，维度值为 null，得到一个新的汇总维度，其中grouping__id代表一个分组的唯一id

select 
coalesce(school,'全学校') as school,
coalesce(grade,'全年级') as grade,
count(user) as num,
grouping__id
from test 
group by 
school,
grade
grouping sets((school,grade),grade,())

上面的代码相当于

select 
'全学校' as school,
'全年级' as grade,
count(user) as num,
0 as grouping__id 
from test
union all 
select 
'全学校' as school,
grade,
count(user) as num,
2 as grouping__id
from test 
group by 
grade
union all 
select 
school,
grade,
count(user) as num,
3 as grouping__id
from test 
group by 
school,
grade

明显使用grouping sets 的写法更简单，也更容易扩展，当维度多的情况下，优势更加明显，当然这个也和excel的分类汇总，比较相似，可以比较着去理解记忆。

那么grouping__id是怎么计算的呢？需要注意的是hive和spark的grouping__id计算方式是不一样的。

hive中的规则是根据group by字段，从低到高，group sets中出现的字段是1，没出现是0。

如上面的例子中，()对应二进制0，(school)对应二进制01，(grade)对应二进制10，(school,grade)对应二进制11。

spark中的规则是根据group by字段，从高到低，group sets中出现的字段是0，没出现是1。

如上面的例子中，()对应二进制11，(school)对应二进制01，(grade)对应二进制10，(school,grade)对应二进制00。

cube 在一个group by 的聚合查询中，根据所有维度组合进行聚合

select 
coalesce(school,'全学校') as school,
coalesce(grade,'全年级') as grade,
count(user) as num,
grouping__id
from test 
group by 
school,
grade
with cube

上面的代码相当于

select 
coalesce(school,'全学校') as school,
coalesce(grade,'全年级') as grade,
count(user) as num,
grouping__id
from test 
group by 
school,
grade
grouping sets((school,grade),school,grade,())

即是把所有的维度组合都放入grouping sets中

rollup 是cube 的一个子集，以左边的维度为主，进行聚合

select 
coalesce(school,'全学校') as school,
coalesce(grade,'全年级') as grade,
count(user) as num,
grouping__id
from test 
group by 
school,
grade
with rollup

上面的代码相当于

select 
coalesce(school,'全学校') as school,
coalesce(grade,'全年级') as grade,
count(user) as num,
grouping__id
from test 
group by 
school,
grade
grouping sets((),school,(school,grade))

即是按照，汇总所有维度->汇总左侧school维度->汇总左右维度的顺序，来聚合，不会再从右到左聚合了。

总结：grouping set是一个更加灵活的做分组维度聚合的函数，可以适应更多的场景。

cube和rollup在完全适用于场景时，有着更简单的写法和效率。