[[381593]]

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

分組

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

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

--僅養狗 
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 
 
--僅養狗 
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 
 
--養狗又養貓 
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 

如果除了養貓和養狗之外，還有倉鼠呢?

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

首先通過上述例子我們總結一個規律

屬性個數n與分組的數量m的關系滿足:m=2^n-1。

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

我們將圖一中的七類用圖二來表示，養貓標記為1(紅色)，養狗標記為10(綠色)，養倉鼠標記為100(藍色)。那么就可以根據疊加之后的值來對區分，疊加后的數字與組別對應關系

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

理論可行，那么在實際應用中應該怎么操作呢?

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 

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

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

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

對比我們的分組模型

一個用戶可能同時屬于n個不同的組，當我們去sum他們并觀察的時候，才能唯一確定他所在的分組

關聯

什么?sql的關聯表只認join?那你就是個outer了

接下來我來說一下union all怎么關聯，以及union all關聯的好處

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

uid detail_amunt cart_amount buy_amount

按照常規的方法，有的同學可能是這么算的

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 

那好我們加點難度

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

有些同學可能這么寫

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 

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

有些同學是這么處理的，用今天活躍的用戶作為主表再去關聯后面三個行為的表就解決了

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

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

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 

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

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