● 测试表 test
1. grouping sets
① 未使用
② 使用grouping sets （与上面等价）
【代码实例】
查看代码
--todo 方式一
--所有学校的人数总和
select
'全学校' as school,
'全年级' as grade,
count(name) as num,
1 as grouping__id
from test
union all
--所有学校中各年级的人数 分组id=2
select
'全学校' as school,
grade,
count(name) as num,
2 as grouping__id
from test
group by grade
union all
-- 各学校中各年级的人数 分组id=3
select
school,
grade,
count(name) as num,
3 as grouping__id
from test
group by school,grade;
---------------------------------------------------------------
--todo 方式二
select
nvl(school,'全学校') as school,
nvl(grade,'全年级') as grade,
count(name) as num,
grouping__id
from test
group by school,grade
grouping sets( (school,grade) , grade , () );
2. Cube
① cube 在一个group by 的聚合查询中，根据所有维度组合进行聚合（即是把所有的维度组合都放入grouping sets中）
【代码示例】
查看代码
--todo 方式一
select
nvl(school,'全学校') as school,
nvl(grade,'全年级') as grade,
count(name) as num,
grouping__id
from test
group by school,grade
grouping sets((school,grade),school,grade,())
order by grouping__id;
--------------------------------------------------
--todo 方式二
select
nvl(school,'全学校') as school,
nvl(grade,'全年级') as grade,
count(name) as num,
grouping__id
from test
group by school,grade
with cube
order by grouping__id;
3. rollup
● rollup 是cube 的一个子集，以左边的维度为主，进行聚合
● 即是按照，汇总所有维度->汇总左侧school维度->汇总左右维度的顺序，来聚合，不会再从右到左聚合了
【代码示例】
查看代码
--todo 方式一
select
nvl(school,'全学校') as school,
nvl(grade,'全年级') as grade,
count(name) as num,
grouping__id
from test
group by school,grade
grouping sets((),school,(school,grade))
order by grouping__id;
----------------------------------------------
--todo 方式二
select
nvl(school,'全学校') as school,
nvl(grade,'全年级') as grade,
count(name) as num,
grouping__id
from test
group by school,grade
with rollup
order by grouping__id;
总结:
① grouping set是一个更加灵活的做分组维度聚合的函数，可以适应更多的场景
② cube和rollup在完全适用于场景时，有着更简单的写法和效率
标签：分析,__,school,函数,grade,Hive,nvl,id,grouping From： https://www.cnblogs.com/apple677/p/16615275.html