grouping sets 联合 Grouping__ID 如何使用
一、背景
(此文的前提是你已经会使用grouping sets 而还不明白Grouping__ID如何使用的情况)在做离线项目开发时,经常会有这种场景:在求某几大类指标时,他们的逻辑其实完全一样,只是其指标所用的去重字段不一样,又或者纬度组合的场景不一样,那么我们一般首先想到的方法是把这几段union all起来。你没有错,union all完全可以解决此场景,但是假如union all的每段代码都很复杂,可能一段代码就有几百甚至达到千行呢?这很正常,对于一个稍微复杂一点的离线项目来说。那么这个时候grouping sets的简化代码的功能就尤为明显了,根据不同维度进行grouping sets的组合搭配完全可以代替union all,而这个时候我们需要区分最终的结果是哪个维度组合而产生呢?这个时候用Grouping__ID就可以用来区分具体的结果是由哪个组合产生的了。具体使用如下:
1.1 union all的使用
假设有如下场景(简化),
dws表dws_cu_trsf_lead_trans_d结构如下:
字段名 | 字段类型 | 备注 |
---|---|---|
region_desc1 | string | 省 |
region_desc2 | string | 市 |
region_desc3 | string | 县区 |
user_name | string | 客户 |
internet_client_type | string | 新老客 |
product_source_type | string | 产品类型 |
product_name | string | 产品名称 |
is_act | string | 是否参加活动 |
is_add_wx | string | 是否 添加微信 |
is_buy | string | 是否购买 |
day | string | 天分区(全量) |
此dws表是已经聚合过的聚合层表,现求省、市、县区的新老客(包括’整体’的类型)、产品类型(包括’整体’的类型)、各产品名称(包括’整体’的类型)对应的参加活动的人数、添加微信人数、购买产品的人数。
假设这里我们使用union all来求:结果如下:
select
region_desc1
,internet_client_type
,product_source_type
,product_name
,count(distinct case when is_act = '1' then user_name end) --参加活动人数
,count(distinct case when is_add_wx = '1' then user_name end) --添加微信人数
,count(distinct case when is_buy = '1' then user_name end) --购买产品人数
from dws_cu_trsf_lead_trans_d where day = '${IncStartAll}'
group by region_desc1,internet_client_type,product_source_type,product_name
union all
select
region_desc2
,internet_client_type
,product_source_type
,product_name
,count(distinct case when is_act = '1' then user_name end) --参加活动人数
,count(distinct case when is_add_wx = '1' then user_name end) --添加微信人数
,count(distinct case when is_buy = '1' then user_name end) --购买产品人数
from dws_cu_trsf_lead_trans_d where day = '${IncStartAll}'
group by region_desc1,internet_client_type,product_source_type,product_name
union all
select
region_desc3
,internet_client_type
,product_source_type
,product_name
,count(distinct case when is_act = '1' then user_name end) --参加活动人数
,count(distinct case when is_add_wx = '1' then user_name end) --添加微信人数
,count(distinct case when is_buy = '1' then user_name end) --购买产品人数
from dws_cu_trsf_lead_trans_d where day = '${IncStartAll}'
group by region_desc1,internet_client_type,product_source_type,product_name
union all
select
region_desc1
,'整体' as internet_client_type
,product_source_type
,product_name
,count(distinct case when is_act = '1' then user_name end) --参加活动人数
,count(distinct case when is_add_wx = '1' then user_name end) --添加微信人数
,count(distinct case when is_buy = '1' then user_name end) --购买产品人数
from dws_cu_trsf_lead_trans_d where day = '${IncStartAll}'
group by region_desc1,product_source_type,product_name
union all
select
region_desc2
,'整体' as internet_client_type
,product_source_type
,product_name
,count(distinct case when is_act = '1' then user_name end) --参加活动人数
,count(distinct case when is_add_wx = '1' then user_name end) --添加微信人数
,count(distinct case when is_buy = '1' then user_name end) --购买产品人数
from dws_cu_trsf_lead_trans_d where day = '${IncStartAll}'
group by region_desc1,product_source_type,product_nameunion all
select
region_desc3
,'整体' as internet_client_type
,product_source_type
,product_name
,count(distinct case when is_act = '1' then user_name end) --参加活动人数
,count(distinct case when is_add_wx = '1' then user_name end) --添加微信人数
,count(distinct case when is_buy = '1' then user_name end) --购买产品人数
from dws_cu_trsf_lead_trans_d where day = '${IncStartAll}'
group by region_desc1,product_source_type,product_name
union all
select
region_desc1
,'整体' as internet_client_type
,'整体' as product_source_type
,product_name
,count(distinct case when is_act = '1' then user_name end) --参加活动人数
,count(distinct case when is_add_wx = '1' then user_name end) --添加微信人数
,count(distinct case when is_buy = '1' then user_name end) --购买产品人数
from dws_cu_trsf_lead_trans_d where day = '${IncStartAll}'
group by region_desc1,product_name
union all
...
union all
...
太长了,实在不想写,写完会崩溃的。(此处的省、市、县区要求打平设置成同级别观看指标,有的业务场景确实如此),这个时候Grouping__ID横空出世,完美解决union all的痛点。用Grouping__ID结果如下:
1.2 Grouping__ID的使用
select
case when Grouping__ID in ('57','49','41','25','33','17', '9','1') then '省'
when Grouping__ID in ('58','50','42','26','34','18','10','2') then '市'
when Grouping__ID in ('60','52','44','28','36','20','12','4') then '县区'
end as region_type --分组字段
,case when internet_client_type is null then '整体' else internet_client_type end as internet_client_type
,case when product_source_type is null then '整体' else product_source_type end as product_source_type
,case when product_name is null then '整体' else product_name end as product_name
,count(distinct case when is_act = '1' then user_name end) --参加活动人数
,count(distinct case when is_add_wx = '1' then user_name end) --添加微信人数
,count(distinct case when is_buy = '1' then user_name end) --购买产品人数
from dws_cu_trsf_lead_trans_d where day = '${IncStartAll}'
group by region_desc1,region_desc2,region_desc3,internet_client_type,product_source_type,product_name
grouping sets(
--所有维度组合
(region_desc1,internet_client_type,product_source_type,product_name),
(region_desc2,internet_client_type,product_source_type,product_name),
(region_desc3,internet_client_type,product_source_type,product_name),
--新老客整体维度+其他维度组合
(region_desc1,product_source_type,product_name),
(region_desc2,product_source_type,product_name),
(region_desc3,product_source_type,product_name),
--产品类型整体维度 + 其他维度组合
(region_desc1,internet_client_type,product_name),
(region_desc2,internet_client_type,product_name),
(region_desc3,internet_client_type,product_name),
--产品名称整体维度 + 其他维度组合
(region_desc1,internet_client_type,product_source_type),
(region_desc2,internet_client_type,product_source_type),
(region_desc3,internet_client_type,product_source_type),
--新老客整体维度 + 产品类型整体维度 + 其他维度组合
(region_desc1,product_name),
(region_desc2,product_name),
(region_desc3,product_name),
--新老客整体维度 + 产品名称整体维度 + 其他维度组合
(region_desc1,product_source_type),
(region_desc2,product_source_type),
(region_desc3,product_source_type),
--产品类型整体维度 + 产品名称整体维度 + 其他维度组合
(region_desc1,internet_client_type),
(region_desc2,internet_client_type),
(region_desc3,internet_client_type),
--所有维度整体
(region_desc1),
(region_desc2),
(region_desc3)
)
以上在使用grouping sets前需要注意各维度的null值你已经处理好了,因为后续是根据维度的null值判断此维度是整体的。
1.3 Grouping__ID 如何计算
如下图所示,按照维度进行排列,每组grouping set里的维度出现的标记为1,否则为0 (实际上是二进制的0和1),最后按照反方向把二进制列出来转化成10进制,最后出现的十进制就是Grouping__ID,就可以分的清楚结果到底是哪个组合的了!小伙伴们,是不是很好用呢?纯手打以及整理excel的,看到的点个赞哟!