阅读量:0
有些指标涉及【多维度】的聚合，大的汇总维度，小的明细维度，需要精细化的下钻。
- grouping sets： 多维度组合，组合维度自定义；
- with cube： 多维度组合，程序自由组合，组合为各个维度的笛卡尔积；
- with rollup：是 with cube的子集，以左侧维度为主，即不允许左侧为NULL，右侧为非NULL的情况出现
一、grouping sets
0 数据准备
with test1 as (select '2021-08' as month_date,'2021-08-11' as day_date,10 as pv union all select '2021-08' as month_date,'2021-08-10' as day_date,15 as pv union all select '2021-08' as month_date,'2021-08-08' as day_date,35 as pv union all select '2021-07' as month_date,'2021-07-08' as day_date,35 as pv union all select '2021-07' as month_date,'2021-07-06' as day_date,25 as pv union all select '2021-07' as month_date,'2021-07-03' as day_date,15 as pv) select month_date, day_date, sum(pv) as pv from test1 group by month_date,day_date grouping sets ( (), (month_date), (month_date,day_date), (day_date) ) order by month_date,day_date; 1 结果分析
二、with cube
0 数据准备
with test1 as (select '2021-08' as month_date,'2021-08-11' as day_date,10 as pv union all select '2021-08' as month_date,'2021-08-10' as day_date,15 as pv union all select '2021-08' as month_date,'2021-08-08' as day_date,35 as pv union all select '2021-07' as month_date,'2021-07-08' as day_date,35 as pv union all select '2021-07' as month_date,'2021-07-06' as day_date,25 as pv union all select '2021-07' as month_date,'2021-07-03' as day_date,15 as pv) select month_date, day_date, sum(pv) as pv from test1 group by month_date,day_date with cube order by month_date,day_date;1 结果分析
2 总结
- 从with cube和grouping sets的案例可以看出，两个结果是一样的；
- with cube的维度组合（各个维度的笛卡尔积）就是groupingsets里面手动添加的维度，即为month_date,day_date两个维度的笛卡尔积。维度组合即为：()、 (month_date)、 (month_date,day_date)、 (day_date)
三、 with rollup
0 数据准备
with test1 as (select '2021-08' as month_date,'2021-08-11' as day_date,10 as pv union all select '2021-08' as month_date,'2021-08-10' as day_date,15 as pv union all select '2021-08' as month_date,'2021-08-08' as day_date,35 as pv union all select '2021-07' as month_date,'2021-07-08' as day_date,35 as pv union all select '2021-07' as month_date,'2021-07-06' as day_date,25 as pv union all select '2021-07' as month_date,'2021-07-03' as day_date,15 as pv) select month_date, day_date, sum(pv) as pv from test1 group by month_date,day_date with rollup order by month_date,day_date;1 结果分析
2 总结
从结果上可以看出，with rollup 和with cube的区别是，少了day_date这个单独维度的聚合，因为with rollup是以左侧维度为主，当左侧month_date维度为NULL时，右侧day_date维度必须为NULL。
参考文章 ：
