今天记录一下在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在完全适用于场景时，有着更简单的写法和效率。
