SQL：多维度切片分析之清脑技巧

最近在跨团队支持一个数据底层开发项目，获益良多，将其中的多维度聚合数据处理和SQL优化方法梳理总结到此篇，以备来日回查。

（1）问题描述：什么是多维度切片

在对业务进行复盘分析和搭建监控体系时，BI需要对不同的维度组合进行多维度聚合统计，比如分别看在某月中：
1）整体的业务指标——维度：整体
2）渠道的业务指标——维度：渠道
3）业务线的业务指标——维度：业务线
4）城市的业务指标——维度：城市
5）每个渠道在各城市的业务指标——维度：渠道X城市
6）每个业务线在各城市的业务指标——维度：业务线X城市
7）每个业务线在各渠道的业务指标——维度：业务线X渠道
8）每个业务线在各城市的不同渠道的业务指标——维度：业务线X城市X渠道

维度每多包一层，数据体量成指数增长，多维度聚合分析在维度多的情况下跑数非常耗资源，造成数据延迟产出、任务被杀，且指标汇总时容易出错，因此需要特别注意维度适宜选取和代码优化。

（2）案例分析：多维度统计时高效聚合难点

当用户与维度之间存在多对多的关系时，同一个用户在某个维度下会出现多次（不同维度取值），用户ID可以去重求和，但是用户ID所对应的数值指标无法直接求和。

假设字段如下：
表T1为流量表，含有字段：

user_id（用户ID），
gender（性别），
channel（渠道），
city（城市），
page_id（页面ID），
log_time（访问页面时刻）

表T2为订单表，含有字段：

user_id（用户ID），
order_id（订单ID），
payment（订单支付金额）
分区ds均为日。

如何求不同性别、城市、渠道的DAU（即user_id去重）、PV（访问页面次数求和）、订单数（即order_id去重）、总净G（即订单支付金额求和）？

预处理：
1）将表T1按照user_id，gender，channel，city维度统计PV，新表记作 newT1；
2）将表T2按照user_id统计当日总订单数和支付金额，新表记作 newT2；
3）两个新表合并得到表T：

user_id，
gender，
channel，
city，
pv（访问页面次数），
order_cnt（用户当日总订单笔数），
pay_sum（用户当日总订单支付金额）

乍一看，貌似直接按照性别、城市、渠道group by一下求和就好了，实际并不是那么简单，先把数据拿出来看下：

表T

如果按照性别分组：
select gender, sum(pv),sum(order_cnt),sum(pay_sum)
from T group by gender

将得到男性订单数为4（而实际只有2单，总计支付金额只有60，上海和苏州重复算了），女性订单也是类似情况。

易错点：
这里的问题在于，订单没有归因到城市和渠道，而是跟着user_id走，但user_id又可以对应多个不同的城市、不同的渠道，因此在进行城市/渠道维度切片时同一个user_id的order_cnt和pay_sum会被重复计算。

那可以怎么做呢？

（3）升级解法：在用户粒度即进行多维度聚合

解决办法：先对user_id粒度进行多维度聚合，再把订单关联到user_id！

多维度切片分析一般语法：

select 
        V1,V2,V3,V4,
        grouping__id, ----------聚合分组的编号，区分是哪种维度组合方式
        count(…),
        sum(…)
from T1
group by V1，V2，V3，V4
grouping sets( ------------不同维度组合方式
(),
(V1),
(V2),
(V3),
(V1,V2),
(V1,V3),
(V1,V2,V3),
)

对于上面的案例，可以首先将T1按user_id+其他维度一起进行聚合：

select 
        user_id,gender,channel,city,
        grouping__id as group_type, ----------聚合分组的编号，区分是哪种维度组合方式
        count(1) as pv
from T1
group by user_id,gender,channel,city,
grouping sets( ------------不同维度组合方式
(user_id),
(user_id,gender),
(user_id,channel),
(user_id,city),
(user_id,channel,gender),
(user_id,channel,city),
(user_id,city,gender),
)

这里有两个技巧：
1）grouping函数使用：进行多维度聚合的时候，不在聚合维度的字段会默认空值，容易与真实的空值混淆，需要做一层判断处理：
if(grouping(gender)=0,gender,'ALL') as gender

grouping([字段名])判断字段是否加入聚合，=0则是，=1则否
不进行聚合则为“ALL”，代表对这个维度整体统计，不再细分切片。

2）conv函数巧妙“翻译”分组编码：grouping__id的取值是整数，很难知道对应哪个维度组合方式，因此需要对grouping__id进行自动匹配处理：
case grouping__id
when conv('0111',2,10) then 'user_id'
when conv('0011',2,10) then 'user_id X gender'
when conv('0101',2,10) then 'user_id X channel'
when conv('0110',2,10) then 'user_id X city'
when conv('0001',2,10) then 'user_id X channel X gender'
when conv('0100',2,10) then 'user_id X channel X city'
when conv('0010',2,10) then 'user_id X city X gender'
end as groupid

group_id是根据取group by后面的维度字段是（=0）否（=1）加入聚合得到2进制数后转化成的10进制数字。
conv('[判断字段是否参与聚合的01序列]',2,10)函数可用来将2进制转换成10进制数，这样就可以建立grouping__id 和维度组合方式的映射了。

更新后代码如下：

select 
        user_id,
        if(grouping(gender)=0,gender,'ALL') as gender
        if(grouping(channel)=0,gender,'ALL') as channel
        if(grouping(city)=0,gender,'ALL') as city
        case grouping__id 
        when conv('0111',2,10) then 'user_id'
        when conv('0011',2,10) then 'user_id X gender'
        when conv('0101',2,10) then  'user_id X channel'
        when conv('0110',2,10) then 'user_id X city'
        when conv('0001',2,10) then 'user_id X channel X gender'
        when conv('0100',2,10) then 'user_id X channel X city'
        when conv('0010',2,10) then 'user_id X city X gender'
        end as groupid ----------聚合分组的编号，区分是哪种维度组合方式
        count(1) as pv
from T1
group by user_id,gender,channel,city,
grouping sets( ------------不同维度组合方式
(user_id),
(user_id,gender),
(user_id,channel),
(user_id,city),
(user_id,channel,gender),
(user_id,channel,city),
(user_id,city,gender),
)

得到新的表newT1:

newT1

跟newT2以user_id为关键字进行左连接得到 newT：

newT

对newT按照gender,channel,city,groupid直接分组计数求和，

select gender,channel,city,group_id,
count(user_id) as dau,
sum(pv) as pv,
sum(order_cnt) as order_cnt,
sum(pay_sum) as pay_sum
from newT
group by gender,channel,city,groupid

得到：

group_id='user_id' 的表示整体业务数据；
group_id='user_id X city' 的表示分城市看业务数据；
group_id='user_id X channel' 的表示分渠道看业务数据；
…… ……
所有需要的维度组合切片都全了，省力！清楚！而且数据处理过程中没有任何一处用distinct，计算高效！

如果我们嫌字段太多、或者某些字段本身再业务上有包含关系时，可以进行合并：

select 
concat(coalesce(gender,'-'),'^',coalesce(channel,'-'),'^',coalesce(city,'-') ) as attr,
group_id,
count(user_id) as dau,
sum(pv) as pv,
sum(order_cnt) as order_cnt,
sum(pay_sum) as pay_sum
from newT
group by gender,channel,city,groupid

得到如下结果，之后进行报表处理时，可以根据分列符号“^”再进行拆解