hive grouping sets、with cube和with rollup的区别（详细分析，建议收藏）

原创已于 2022-09-07 11:33:33 修改 · 1.7k 阅读

13 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

hive

于 2021-08-12 15:14:58 首次发布

对于互联网公司报表需求中，有很多多维度的组合，大的汇总维度、小的明细维度，精细化的下钻。这样的背景下，我们很多的指标都要进行多维度的聚合。

grouping sets： 多维度组合，组合维度自定义
with cube： 多维度组合，程序自由组合，组合为各个维度的笛卡尔积
with rollup： with cube的子集，以左侧维度为主，即不允许左侧为NULL，右侧为非NULL的情况出现

我们来几个案例：

with cube：

spark-sql> 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;
NULL   		NULL    		135                                                             
NULL		2021-07-03		15
NULL		2021-07-06		25
NULL		2021-07-08		35
NULL		2021-08-08		35
NULL		2021-08-10		15
NULL		2021-08-11		10
2021-07		NULL			75
2021-07		2021-07-03		15
2021-07		2021-07-06		25
2021-07		2021-07-08		35
2021-08		NULL			60
2021-08		2021-08-08		35
2021-08		2021-08-10		15
2021-08		2021-08-11		10
Time taken: 26.605 seconds, Fetched 15 row(s)

grouping sets：

spark-sql> 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;
NULL    	NULL    		135                                                             
NULL		2021-07-03		15
NULL		2021-07-06		25
NULL		2021-07-08		35
NULL		2021-08-08		35
NULL		2021-08-10		15
NULL		2021-08-11		10
2021-07		NULL			75
2021-07		2021-07-03		15
2021-07		2021-07-06		25
2021-07		2021-07-08		35
2021-08		NULL			60
2021-08		2021-08-08		35
2021-08		2021-08-10		15
2021-08		2021-08-11		10
Time taken: 12.854 seconds, Fetched 15 row(s)

总结：
从with cube和grouping sets的案例可以看出，两个结果是一样的。
with cube的维度组合就是groupingsets里面手动添加的维度，即为month_date,day_date两个维度的笛卡尔积。

我们再来看看 with rollup:

spark-sql> 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;
NULL    	NULL    		135                                                             
2021-07		NULL			75
2021-07		2021-07-03		15
2021-07		2021-07-06		25
2021-07		2021-07-08		35
2021-08		NULL			60
2021-08		2021-08-08		35
2021-08		2021-08-10		15
2021-08		2021-08-11		10
Time taken: 18.792 seconds, Fetched 9 row(s)