---------假设sql如下----------
select
GROUPING__ID AS group_tag
from tmp_aaa t1
GROUP BY t1.active_date
,t1.mem_level
,t1.is_stock_mem
,t1.is_doub_acct
,t1.channel_type
,t1.channel_code
,t1.channel_name
,t1.tqy_avg_quota
,t1.tqg_avg_quota
GROUPING SETS ((t1.active_date,t1.mem_level,t1.is_stock_mem,t1.is_doub_acct,t1.channel_type,t1.channel_code,t1.channel_name,t1.tqy_avg_quota,t1.tqg_avg_quota)
,(t1.active_date,t1.channel_type)
,(t1.active_date,t1.is_doub_acct)
,(t1.active_date,t1.mem_level)
,(t1.active_date,t1.channel_code,t1.channel_name)
,(t1.active_date)
)
;
计算GROUPING__ID变量的值步骤如下
1) 先将group by字段的数据倒序排列,结果如下
t1.tqg_avg_quota
t1.tqy_avg_quota
t1.channel_name
t1.channel_code
t1.channel_type
t1.is_doub_acct
t1.is_stock_mem
t1.mem_level
t1.active_date
2) 然后通过倒序排序后的顺序标识GROUPING SETS中各维度组合的2进制值,对应字段有就是1,无就是0
111111111
000010001
000001001
000000011
001100001
3)最后就可以通过各维度组合的十进制的标识进行判断了
,CASE WHEN group_tag = 511 THEN "全部维度"
WHEN group_tag = 17 THEN "渠道类型"
WHEN group_tag = 9 THEN "是否双账户"
WHEN group_tag = 3 THEN "等级"
WHEN group_tag = 97 THEN "渠道名称"
ELSE "无"
END AS group_tag