Groupings sets详解

概要

GROUPING SETS在SELECT语句中的使用，它扩展了GROUP BY的功能，允许一次性执行多种分组操作，提高了查询效率。hive，spark，presto中都有此函数。以下介绍以sparksql为例；

使用方法介绍

首先需要提醒一点的是，hql中和sparksql中虽然均有grouping sets函数，可是grouping__id顺序确是相反的，一般情况我们习惯了hql中保持一致，需要脚本中增加：set spark.grouping.sets.reference.hive=true;

基本使用，是在group by 维度值后，使用grouping sets ((a,b,c),(a,b),©)像这样；

SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a)
###等价于
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a

示例

有这样一份数据，统计了不同维度下用户数和浏览次数，维度有日期（day）,平台（platform）和频道（channel）；

具体代码如下：

select 
	day
	,platform
    ,channel
    ,sum(pv) as pv
    ,sum(imei_num) as imei_num
from 
(SELECT  
	day
	,if((cast(grouping__id as int) & 1) = 0,'ALL',platform) as platform
    ,if((cast(grouping__id as int) & 2) = 0,'ALL',channel) as channel
    ,SUM(pv) as pv
	,imei
	,max(if(pv>0,1,0)) as imei_num
FROM tmp_read_pv
GROUP BY  platform, channel,day,imei
GROUPING SETS ( 
(platform,channel,imei,day),
(platform,imei,day),
(channel,imei,day),
(imei,day)
 )
 ) tt 
 GROUP BY  platform, channel,day
 ;

一般情况下，涉及到人数去重，我们习惯于嵌套两层，最里面一层加上imei维度，最外面再把imei去掉；

现在思考一个问题，如果我们维度比较多，比如有8个维度，里面还涉及到去重指标，我们该怎么做呢？如果直接使用grouping sets
再加上imei，共有9个维度，直接写需要$2^9$行，如果直接用with cube,因为里面有imei数据量比较大，几乎跑不动。那该如何呢？

方法：多段使用grouping sets ,比如你有9个（加imei）维度,你可以第一次先group by 5个（加imei）维度，其他几个维度当做指标
，此时4个维度，都有all枚举值，第二次使用grouping sets ，group by 两个维度，再加上第一次的5个,剩下两个依然当做指标，grouping sets里面需要注意一下，这4个维度不用再增加ALL枚举值，此时这两个维度也有all枚举值，最后一次再新增两个维度，group by 两个维度，再加上前两次的7个维度，注意事项同上，此时最后两个维度也有ALL枚举值。在最后把imei维度去掉再聚合一层，就能得出最终结果；