最近在跨團(tuán)隊(duì)支持一個(gè)數(shù)據(jù)底層開發(fā)項(xiàng)目，獲益良多樱调，將其中的多維度聚合數(shù)據(jù)處理和SQL優(yōu)化方法梳理總結(jié)到此篇约素，以備來日回查。
（1）問題描述：什么是多維度切片
在對(duì)業(yè)務(wù)進(jìn)行復(fù)盤分析和搭建監(jiān)控體系時(shí)笆凌，BI需要對(duì)不同的維度組合進(jìn)行多維度聚合統(tǒng)計(jì)圣猎，比如分別看在某月中：
1）整體的業(yè)務(wù)指標(biāo)——維度：整體
2）渠道的業(yè)務(wù)指標(biāo)——維度：渠道
3）業(yè)務(wù)線的業(yè)務(wù)指標(biāo)——維度：業(yè)務(wù)線
4）城市的業(yè)務(wù)指標(biāo)——維度：城市
5）每個(gè)渠道在各城市的業(yè)務(wù)指標(biāo)——維度：渠道X城市
6）每個(gè)業(yè)務(wù)線在各城市的業(yè)務(wù)指標(biāo)——維度：業(yè)務(wù)線X城市
7）每個(gè)業(yè)務(wù)線在各渠道的業(yè)務(wù)指標(biāo)——維度：業(yè)務(wù)線X渠道
8）每個(gè)業(yè)務(wù)線在各城市的不同渠道的業(yè)務(wù)指標(biāo)——維度：業(yè)務(wù)線X城市X渠道
維度每多包一層，數(shù)據(jù)體量成指數(shù)增長(zhǎng)乞而，多維度聚合分析在維度多的情況下跑數(shù)非常耗資源送悔，造成數(shù)據(jù)延遲產(chǎn)出、任務(wù)被殺爪模，且指標(biāo)匯總時(shí)容易出錯(cuò)欠啤，因此需要特別注意維度適宜選取和代碼優(yōu)化。
（2）案例分析：多維度統(tǒng)計(jì)時(shí)高效聚合難點(diǎn)
當(dāng)用戶與維度之間存在多對(duì)多的關(guān)系時(shí)屋灌，同一個(gè)用戶在某個(gè)維度下會(huì)出現(xiàn)多次（不同維度取值）洁段，用戶ID可以去重求和，但是用戶ID所對(duì)應(yīng)的數(shù)值指標(biāo)無法直接求和共郭。
假設(shè)字段如下：
表T1為流量表祠丝，含有字段：
user_id（用戶ID），
gender（性別）落塑，
channel（渠道）纽疟，
city（城市）憾赁，
page_id（頁(yè)面ID），
log_time（訪問頁(yè)面時(shí)刻）
表T2為訂單表蟆肆，含有字段：
user_id（用戶ID），
order_id（訂單ID）炎功，
payment（訂單支付金額）
分區(qū)ds均為日。
如何求不同性別赁温、城市股囊、渠道的DAU（即user_id去重）更啄、PV（訪問頁(yè)面次數(shù)求和）、訂單數(shù)（即order_id去重）内狗、總凈G（即訂單支付金額求和）义锥？
預(yù)處理：
1）將表T1按照user_id缨该，gender，channel，city維度統(tǒng)計(jì)PV熄云，新表記作 newT1；
2）將表T2按照user_id統(tǒng)計(jì)當(dāng)日總訂單數(shù)和支付金額荚守，新表記作 newT2矗漾；
3）兩個(gè)新表合并得到表T：
user_id薄料，
gender，
channel誊役，
city，
pv（訪問頁(yè)面次數(shù)）击孩，
order_cnt（用戶當(dāng)日總訂單筆數(shù)）鹏漆，
pay_sum（用戶當(dāng)日總訂單支付金額）
乍一看艺玲，貌似直接按照性別、城市又跛、渠道group by一下求和就好了若治，實(shí)際并不是那么簡(jiǎn)單，先把數(shù)據(jù)拿出來看下：
如果按照性別分組：
select gender, sum(pv),sum(order_cnt),sum(pay_sum)
from T group by gender
將得到男性訂單數(shù)為4（而實(shí)際只有2單，總計(jì)支付金額只有60婆跑，上海和蘇州重復(fù)算了）滑进，女性訂單也是類似情況。
易錯(cuò)點(diǎn)：
這里的問題在于阴汇，訂單沒有歸因到城市和渠道节槐，而是跟著user_id走，但user_id又可以對(duì)應(yīng)多個(gè)不同的城市哥倔、不同的渠道揍庄，因此在進(jìn)行城市/渠道維度切片時(shí)同一個(gè)user_id的order_cnt和pay_sum會(huì)被重復(fù)計(jì)算。
那可以怎么做呢府阀？
（3）升級(jí)解法：在用戶粒度即進(jìn)行多維度聚合
解決辦法：先對(duì)user_id粒度進(jìn)行多維度聚合芽突，再把訂單關(guān)聯(lián)到user_id！
多維度切片分析一般語法：
select
V1,V2,V3,V4,
grouping__id, ----------聚合分組的編號(hào)田巴，區(qū)分是哪種維度組合方式
count(…),
sum(…)
from T1
group by V1壹哺，V2艘刚，V3，V4
grouping sets( ------------不同維度組合方式
(),
(V1),
(V2),
(V3),
(V1,V2),
(V1,V3),
(V1,V2,V3),
)
對(duì)于上面的案例箩朴，可以首先將T1按user_id+其他維度一起進(jìn)行聚合：
select
user_id,gender,channel,city,
grouping__id as group_type, ----------聚合分組的編號(hào)炸庞，區(qū)分是哪種維度組合方式
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),
)
這里有兩個(gè)技巧：
1）grouping函數(shù)使用：進(jìn)行多維度聚合的時(shí)候荚斯，不在聚合維度的字段會(huì)默認(rèn)空值，容易與真實(shí)的空值混淆滥壕，需要做一層判斷處理：
if(grouping(gender)=0,gender,'ALL') as gender
grouping([字段名])判斷字段是否加入聚合捏浊，=0則是撞叨，=1則否
不進(jìn)行聚合則為“ALL”牵敷，代表對(duì)這個(gè)維度整體統(tǒng)計(jì)法希，不再細(xì)分切片。
2）conv函數(shù)巧妙“翻譯”分組編碼：grouping__id的取值是整數(shù)毛肋，很難知道對(duì)應(yīng)哪個(gè)維度組合方式，因此需要對(duì)grouping__id進(jìn)行自動(dòng)匹配處理：
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是根據(jù)取group by后面的維度字段是（=0）否（=1）加入聚合得到2進(jìn)制數(shù)后轉(zhuǎn)化成的10進(jìn)制數(shù)字诗眨。
conv('[判斷字段是否參與聚合的01序列]',2,10)函數(shù)可用來將2進(jìn)制轉(zhuǎn)換成10進(jìn)制數(shù)匠楚，這樣就可以建立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 ----------聚合分組的編號(hào)璃饱，區(qū)分是哪種維度組合方式
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:
跟newT2以u(píng)ser_id為關(guān)鍵字進(jìn)行左連接得到 newT：
對(duì)newT按照gender,channel,city,groupid直接分組計(jì)數(shù)求和撩穿，
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' 的表示整體業(yè)務(wù)數(shù)據(jù)冗锁；
group_id='user_id X city' 的表示分城市看業(yè)務(wù)數(shù)據(jù)嗤栓；
group_id='user_id X channel' 的表示分渠道看業(yè)務(wù)數(shù)據(jù)；
…… ……
所有需要的維度組合切片都全了叨叙，省力擂错！清楚樱蛤！而且數(shù)據(jù)處理過程中沒有任何一處用distinct，計(jì)算高效爽醋！
如果我們嫌字段太多便脊、或者某些字段本身再業(yè)務(wù)上有包含關(guān)系時(shí)，可以進(jìn)行合并：
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
得到如下結(jié)果遂赠，之后進(jìn)行報(bào)表處理時(shí)，可以根據(jù)分列符號(hào)“^”再進(jìn)行拆解
