[[381593]]

 身為數(shù)據(jù)分析師，大家對SQL可是再熟悉不過了。大多數(shù)人對常規(guī)的sql語法都已經(jīng)熟練掌握，但是我發(fā)現(xiàn)在工作中許多同學(xué)join用的比較多，union all只有在縱向合并表格的時候用得到。如果僅僅是這樣用，那么union all 的價值就被大打折扣了，今天給大家分享一下我在工作中經(jīng)常用的union all技巧，希望可以幫到大家。

分組

用戶分群對于數(shù)分同學(xué)來說可以說是家常便飯，這里我們來思考一個問題，如果一個用戶可以同時存在多種屬性，如何將用戶全部分開呢?舉例來說，下面有張表記錄了用戶收養(yǎng)寵物的信息，僅有兩個字段，uid,pets

對用戶分群，有些同學(xué)是這么做的：假如寵物僅有貓和狗兩類，那么用戶可以分為三類，僅養(yǎng)狗，僅養(yǎng)貓，既養(yǎng)狗又養(yǎng)貓

--僅養(yǎng)狗 
select  
uid,'dog_only' as type from pets_table a where pets='狗' 
where not exists(select uid from pets_table b where pets='貓' and a.uid=b.uid)  
group by uid 
 
--僅養(yǎng)狗 
select  
uid,'cat_only' as type from pets_table a where pets='貓' 
where not exists(select uid from pets_table b where pets='狗' and a.uid=b.uid) 
group by uid 
 
--養(yǎng)狗又養(yǎng)貓 
select  
uid,'dog_cat' as type from pets_table a where pets='貓' 
where exists (select uid from pets_table b where pets='狗' and a.uid=b.uid) 
group by uid 

如果除了養(yǎng)貓和養(yǎng)狗之外，還有倉鼠呢?

那么用戶可以分為七類，分別為，僅養(yǎng)貓，僅養(yǎng)狗，僅養(yǎng)倉鼠，養(yǎng)貓和倉鼠，養(yǎng)貓和狗，養(yǎng)狗和倉鼠，三個都養(yǎng)。

首先通過上述例子我們總結(jié)一個規(guī)律

屬性個數(shù)n與分組的數(shù)量m的關(guān)系滿足:m=2^n-1。

可見分組的數(shù)量與屬性個數(shù)之間存在指數(shù)關(guān)系，當(dāng)屬性個數(shù)大于三個后，如果使用上述sql進(jìn)行分類將會非常復(fù)雜。有沒有簡單的方法呢?為了解釋這個問題，我們用維恩圖來表示上述案例

我們將圖一中的七類用圖二來表示，養(yǎng)貓標(biāo)記為1(紅色)，養(yǎng)狗標(biāo)記為10(綠色)，養(yǎng)倉鼠標(biāo)記為100(藍(lán)色)。那么就可以根據(jù)疊加之后的值來對區(qū)分，疊加后的數(shù)字與組別對應(yīng)關(guān)系

那么1=1;2=10;3=100;4=101;5=110;6=11;7=111

理論可行，那么在實(shí)際應(yīng)用中應(yīng)該怎么操作呢?

with info  
(select 
uid, 
1 as type 
from pets_table where pets='貓' 
 
union all 
 
select 
uid, 
10 as type 
from pets_table where pets='貓' 
 
union all 
 
select 
uid, 
100 as type 
from pets_table where pets='倉鼠') 
 
select uid,sum(distinct type) from info group by uid 

經(jīng)過上述處理之后，每個uid都被打上了類別標(biāo)記。然后我們就可以根據(jù)標(biāo)記判斷用戶最終所屬的群組，用這種方法即使分組數(shù)量隨著屬性數(shù)量指數(shù)增加，但是我們的處理數(shù)據(jù)的復(fù)雜度隨著屬性數(shù)量增加線性增加。提高了效率，優(yōu)化了邏輯。

我把它叫做量子疊加分組法，原因在于

一個量子系統(tǒng)可以處于不同量子態(tài)的疊加態(tài)上，當(dāng)去觀察它的時候，才會從多種狀態(tài)坍縮到一種確定的狀態(tài)

對比我們的分組模型

一個用戶可能同時屬于n個不同的組，當(dāng)我們?nèi)um他們并觀察的時候，才能唯一確定他所在的分組

關(guān)聯(lián)

什么?sql的關(guān)聯(lián)表只認(rèn)join?那你就是個outer了

接下來我來說一下union all怎么關(guān)聯(lián)，以及union all關(guān)聯(lián)的好處

假如我們是一個購物app，需要建立一張用戶行為的寬表記錄用戶的核心行為。用戶標(biāo)識記作uid，商品唯一標(biāo)識記作id，用戶行為日志表為action，用戶行為主要有三種，一，查看商品，標(biāo)記detail;二，加入購物車，標(biāo)記cart;三，購買，標(biāo)記buy。我們需要計(jì)算的是一個uid每天三種行為的次數(shù)，具體如下：

uid detail_amunt cart_amount buy_amount

按照常規(guī)的方法，有的同學(xué)可能是這么算的

select  
uid, 
count(distinct if(action='detail',id,null)) as detail_amunt, 
count(distinct if(action='cart',id,null)) as cart_amount, 
count(distinct if(action='buy',id,null)) as buy_amount 
from action 
group by uid 

那好我們加點(diǎn)難度

這三個行為分別記錄在三個表里面，分別為detail_table,cart_table,buy_table里面(不要告訴我可以把三張表union all起來再用上面的代碼)

有些同學(xué)可能這么寫

select  
uid, 
count(distinct a.id), 
count(distinct b.id), 
count(distinct c.id) 
from  
detail_table a left outer join cart_table b 
on a.uid=b.uid 
left outer join buy_table c 
on a.uid=c.uid 
group by uid 

這里有個默認(rèn)的邏輯是，用戶的加入購物車以及購買行為一定要先查看，否則無查看的加入購物車行為不能被計(jì)算在內(nèi)。那么難度又來了，假如加入購物車它就是不需要查看呢?

有些同學(xué)是這么處理的，用今天活躍的用戶作為主表再去關(guān)聯(lián)后面三個行為的表就解決了

這樣自然可以，這樣計(jì)算需要的資源較多，邏輯并不清晰，而且把活躍且以上三個行為一個都沒有的用戶計(jì)算進(jìn)來了。

下面我來推薦一個方式，可以一次性解決上述所有問題

select uid,sum(detail_amunt),sum(cart_amount),sum(buy_amount) 
from 
(select  
uid, 
count(distinct if(action='detail_amunt',id,null)) as detail_amunt, 
0 as cart_amount, 
0 as buy_amount 
from detail_table 
group by uid 
 
union all 
 
select  
uid, 
0 as detail_amunt, 
count(distinct if(action='cart_amount',id,null)) as cart_amount, 
0 as buy_amount 
from cart_table 
group by uid 
 
union all 
 
select  
uid, 
0 as detail_amunt, 
0 as cart_amount, 
count(distinct if(action='buy_amount',id,null)) as buy_amount 
from buy_table 
group by uid)x 
group by uid 

這種方式的好處在于，計(jì)算比較快，寫法簡單，邏輯清晰，通用性較好。究其本質(zhì)其實(shí)是分別計(jì)算了三個指標(biāo)，并用union all整合成了一個寬表

上述兩個技巧比較通用，也能不錯的簡化問題，是我個人比較喜歡的方法。在你的工作中是否遇到過可以用上述方法解決的問題呢?