一道SQL題的前世今生
- 2019 年 10 月 8 日
- 筆記
本文轉載自超哥的雜貨鋪，詳情可以掃描下方二維碼：
以清凈心看世界；
用歡喜心過生活。
超哥的雜貨鋪，你值得擁有~
來看一道SQL題目：
註：以下討論核心在於解釋原理，所涉及到的數據和表結構均為虛構。本文代碼較多，如果看不清楚，可以在後台回復「sql」獲取本文PDF版本。
假設某APP場景下，有以下數據需求： 表1，新增用戶表，t_new_user 字段：uid 用戶id，reg_date新增日期 表2，收藏行為表，t_favorite_act 字段：uid 用戶id，fav_date 收藏日期 表3，加購物車行為，下面簡稱「加購」，t_add_cart_act 字段：uid 用戶id，add_date 加購日期 表4，新安裝用戶表(包括前文的新增用戶，還包含卸載重裝的用戶)， t_new_install_user 字段，uid 用戶id，install_date 安裝日期 目標：求2019-07-01新增用戶數和新裝用戶數以及當日新增用戶和新裝用戶在當天，接下來三天，接下來七天產生收藏行為和加購物車行為的人數(請確保你理解了需求)。輸出格式如下：
點擊圖片查看細節
題目中涉及到了兩類用戶兩種行為三種日期，直觀的想法是可以化整為零，先縮小範圍，看一種用戶一個日期的行為，然後類推解決。(比如先看下新增用戶三日收藏用戶數，加購用戶數)。我們看一下這種思路的SQL寫法。
提取2019-07-01新增用戶數，三日收藏用戶數，三日加購用戶數的代碼如下：
select a.reg_date, count(distinct a.uid) as new_usr_cnt, count(distinct b.uid) as 3day_fav_usr_cnt,--3日收藏用戶數 count(distinct c.uid) as 3day_add_cart_usr_cnt--3日加購用戶數 from ( select reg_date, uid from t_new_user where reg_date = '2019-07-01' group by reg_date, uid ) a left join ( select fav_date, uid from t_favorite_act where fav_date between '2019-07-01' and date_add('2019-07-01', 2) group by fav_date, uid ) b on a.uid = b.uid left join ( select add_date, uid from t_add_cart_act where add_date between '2019-07-01'and date_add('2019-07-01', 2) group by add_date, uid ) c on a.uid = c.uid group by a.reg_date
提取2019-07-01新裝用戶數(包含新增)，三日收藏用戶數，三日加購用戶數的代碼如下(注意和之前的差別)
select a.reg_date, count(distinct a.uid) as new_install_usr_cnt, count(distinct b.uid) as new_install_3day_fav_usr_cnt, --3日收藏用戶數 count(distinct c.uid) as new_install_3day_add_cart_usr_cnt--3日加購用戶數 from ( select reg_date as new_date, uid from t_new_user where reg_date = '2019-07-01' union all select install_date as new_date, uid from t_new_install_user where install_date = '2019-07-01' ) a left join ( select fav_date, uid from t_favorite_act where fav_date between '2019-07-01' and date_add('2019-07-01', 2) group by fav_date, uid ) b on a.uid = b.uid left join ( select add_date, uid from t_add_cart_act where add_date between '2019-07-01'and date_add('2019-07-01', 2) group by add_date, uid ) c on a.uid = c.uid group by a.reg_date
按照這種思路，我們需要寫好多段的SQL(需要修改日期相關的代碼)：新增用戶當日，新增用戶3日，新增用戶7日，新裝用戶當日，新裝用戶3日，新裝用戶7日。 分別運行之後6次，經過整理就能得到最終的結果。也可以外面載嵌套一個left join，設法把這些小段的SQL合併起來(可以思考一下怎麼做)，這樣減少運行的次數，但是代碼會非常長，而且有很多重複的部分。顯然，這樣做不夠「明智」。
有沒有更簡單一點的寫法呢？答案當然是肯定的。
變式1：新裝和新增合併起來寫
有沒有發現「同一個日期」，如上面的「三日內行為」，對於新裝用戶和新增用戶的代碼，b表和c表的大段SQL是一樣的！而且後面的代碼包含了前面的。因為都要從這兩個表裡取收藏和加購行為，而且日期還一樣。如果按照上面的寫法，將新增用戶和新裝用戶兩段用日期關聯，放到一起執行，會重複掃描b表和c表。顯然這樣在一定程度上會影響效率。考慮到大段代碼的重複性，我們可以巧妙的合併一下，如下：
select a.reg_date, count(distinct case when a.type='new_user' then a.uid else null end) as new_usr_cnt, count(distinct case when a.type='new_user' then b.uid else null end) as fav_usr_cnt, count(distinct case when a.type='new_user' then c.uid else null end) as add_cart_usr_cnt, count(distinct a.uid) as new_install_usr_cnt, count(distinct b.uid) as new_install_3day_fav_usr_cnt, count(distinct c.uid) as new_install_3day_add_cart_usr_cnt from ( select reg_date as new_date, uid, 'new_user' as type from t_new_user where reg_date = '2019-07-01' union all select install_date as new_date, uid, 'reinstall' as type from t_new_install_user where install_date = '2019-07-01' ) a left join ( select fav_date, uid from t_favorite_act where fav_date between '2019-07-01' and date_add('2019-07-01', 2) group by fav_date, uid ) b on a.uid = b.uid left join ( select add_date, uid from t_add_cart_act where add_date between '2019-07-01'and date_add('2019-07-01', 2) group by add_date, uid ) c on a.uid = c.uid group by a.reg_date
這樣是把新增和新裝的合併起來了，但是當日，三日，七日還是需要分開寫。還有辦法優化嗎？當然可以！(想像一下，如果需求忽然增加了五日，十日，十五日怎麼辦？)（題外話，其實新裝表是包含新增表的，如果做表的時候能用一個標識區分安裝時是否是首次，可能會更合理一些。）
變式2：巧用datediff
datediff函數可以求兩個日期的時間差。雖然目前的需求是求當日，三日，七日，也就是日期差分別是0，2，6(注意不是1，3，7)的情況。如果我們能求出所有的時間差diff_day，再對diff_day進行判斷，不僅能處理當前的需求，就是再來幾個別的不同的日期，那也可以只用一個case when就解決了，代碼如下，顯然比原來更清晰簡潔。
select a.reg_date, count(distinct case when fav_datediff = 0 and a.type='new_user' then a.uid) as new_install_user_current_day, count(distinct case when fav_datediff = 0 and a.type='new_user' then b.uid else null end) as new_user_current_day_fav, count(distinct case when fav_datediff > 0 and fav_datediff <= 2 and a.type='new_user' then b.uid else null end) as new_user_3_day_fav, count(distinct case when fav_datediff > 0 and fav_datediff <= 6 and a.type='new_user' then b.uid else null end) as new_user_7_day_fav, count(distinct case when add_datediff = 0 and a.type='new_user' then c.uid else null end) as new_user_current_day_add, count(distinct case when add_datediff > 0 and add_datediff <= 2 and a.type='new_user' then c.uid else null end) as new_user_3_day_add, count(distinct case when add_datediff > 0 and add_datediff <= 6 and a.type='new_user' then c.uid else null end) as new_user_7_day_add, count(distinct case when fav_datediff = 0 then a.uid) as new_install_user_current_day, count(distinct case when fav_datediff = 0 then b.uid else null end) as new_install_user_current_day_fav, count(distinct case when fav_datediff > 0 and fav_datediff <= 2 then b.uid else null end) as new_install_user_3_day_fav, count(distinct case when fav_datediff > 0 and fav_datediff <= 6 then b.uid else null end) as new_install_user_7_day_fav, count(distinct case when add_datediff = 0 and then c.uid else null end) as new_install_user_current_day_add, count(distinct case when add_datediff > 0 and add_datediff <= 2 then c.uid else null end) as new_install_user_3_day_add, count(distinct case when add_datediff > 0 and add_datediff <= 6 then c.uid else null end) as new_install_user_7_day_add from ( select a.reg_date, a.uid, a.type, b.uid, c.uid, datediff(b.fav_date, a.reg_date) as fav_datediff, datediff(b.add_date, a.reg_date) as add_datediff, from ( select reg_date as new_date, uid, 'new_user' as type from t_new_user where reg_date = '2019-07-01' union all select install_date as new_date , uid, 'reinstall' as type from t_new_install_user where install_date = '2019-07-01' ) a left join ( select fav_date, uid from t_favorite_act where fav_date between '2019-07-01' and date_add('2019-07-01', 7) --注意這裡要取能滿足datediff=6的日期範圍 group by fav_date, uid ) b on a.uid = b.uid left join ( select add_date, uid from t_add_cart_act where add_date between '2019-07-01'and date_add('2019-07-01', 7) --注意這裡要取能滿足datediff=6的日期範圍 group by add_date, uid ) c on a.uid = c.uid ) d
看完上面的代碼，是不是感覺豁然開朗了許多？我們巧妙的將之前的代碼縮減並且減少了冗餘。對比一開始的想法，不僅思路更加清晰，代碼量也精簡不少，而且更便捷，執行的效率也更高。不過我們繼續來思考這端代碼，看有沒有哪些細節是可能會忽略的。
變式3：如果不是2019-07-01怎麼辦？
如果是別的固定的日期，當然只需要複製一下代碼，改變一下日期就好了。但工作中會有這樣的場景，不僅僅只是臨時取一個數據，而是要開發報表，這需要讓寫好的SQL根據不同的日期變量，每天執行一下，獲得相應的數據。這裡有幾個問題。SQL的定時執行通常需要依賴於shell腳本，我們需要把日期作為一個變量，它需要每天都自動更新成最新的時間(通常會有一天延遲，因此最新的日期就是當前日期昨天的時間)。另一個是數據更新的問題。我們用實例來說明，假設今天是0809，那我們應該能得到0808以及之前的數據。對於0802以及之前的數據，它的當日，三日，七日的轉化情況已經固定了，不會隨着時間進一步更新。對於0806以及之前的數據，它的當日和三日轉化也已經確定。而0803-0808這些天，他們的七日轉化數據還沒有「到位」，0806-0808，他們的三日轉化數據也還沒有「到位」，因為時間周期還沒到。我們可以選擇將當前最新的數據呈現出來(例如0808的數據，當日，三日，七日是一樣的，因為只有當日的數據)，也可以選擇如果日期還沒到可以計算數據的時候，在相應的數據置為0。還有一個需要考慮的點：當最新的一天任務執行的時候，都伴隨着有一些天的計算時間得到了滿足(原來的1天變成2天，2天變成3天，以此類推)，所以就需要同時更新當前時間及之前七天的數據。在調度任務的代碼里需要有相應的處理邏輯。例如每天寫入的時候都需要刪除之前七天，寫進最新七天等。(這裡使用的是不置0的方式)。具體在這裡就不展開了。
我們繼續討論。
變式4：留存計算
回到我們開篇的問題，有沒有覺得，需求的邏輯和留存很像。留存通常是指之前有過某種行為，後續依然有。但這裡前後兩種行為是不一樣的。其實可以看做是另一種意義上的留存。因此上面的思路也可以用來計算留存。拿次日留存來舉例，我們算一下0801-0807的次日留存。這裡額外需要一張活躍表。t_active_user(uid 用戶id，act_date 活躍日期)
--次留 select a.reg_date, count(distinct a.uid) new_user, count(distinct b.uid) act_user from ( select reg_date, uid from t_new_user where reg_date >='20190801' and reg_date <='20190807' group by reg_date, uid --每日每組新增用戶 ) a left join ( select act_date, uid from t_active_user where act_date >='20190801' and act_date <='20190808'--至少要到0808才能保證0807有留存數據 group by act_date, uid --每日每組日活用戶 ) b on a.uid = b.uid where datediff(b.act_date, a.reg_date) = 1 group by a.reg_date
你認為這段SQL有問題嗎？（我們把留存的分子分母分開來計算，先不做相除）
乍一看可能不覺得有什麼問題。但運行之後會發現new_user和act_user的結果是一樣的。問題出在where條件已經把沒有不符合條件的活躍用戶過濾掉了。正確的寫法應該將判斷條件寫到count函數中(可以仔細思考一下)，如下：
--次留 select a.reg_date, count(distinct a.uid) new_user, count(distinct case when datediff(b.act_date, a.reg_date) = 1 then b.uid else null end) act_user from ( select reg_date, uid from t_new_user where reg_date >='20190801' and reg_date <='20190807' group by reg_date, uid ) a left join ( select act_date, uid from t_active_user where act_date >='20190801' and act_date <='20190808'--至少要到0808才能保證0807有留存數據 group by act_date, uid ) b on a.uid = b.uid group by a.reg_date
同樣的，7日留存也可以在此基礎上稍加改動得到。但在多日留存的時候，依然也要考慮前面說的數據更新的問題。
select a.reg_date, count(distinct a.uid) new_user, count(distinct case when datediff(b.act_date, a.reg_date) = 1 then b.uid else null end) 2day_act_user， count(distinct case when datediff(b.act_date, a.reg_date) = 6 then b.uid else null end) 7day_act_user from ( select reg_date, uid from t_new_user where reg_date >='20190801' and reg_date <='20190802'--有些天還不能計算7留的數據 group by reg_date, uid ) a left join ( select act_date, uid from t_active_user where act_date >='20190801' and act_date <='20190808'--至少要到0808才能保證0802有7日留存數據 group by act_date, uid ) b on a.uid = b.uid group by a.reg_date
關於活躍留存的計算，還可能存在活躍表是全量表的情況，即每個最新分區有歷史每一天的活躍用戶。那寫法可能又有不同了。我們可以使用下面的方法計算留存。累計活躍表：t_user_active_accu(uid 用戶id，act_date 活躍日期)。這種情況我們依然可以請datediff幫助我們計算留存。只需要按照日期差進行分組，統計各個日期差的活躍用戶數，最後將相同日期差的用戶數求和就能算出「留存的用戶」了。如下面代碼計算了20190801的留存。
select sum(case when datediff=0 then user_cnt else 0 end), sum(case when datediff=1 then user_cnt else 0 end) from ( select datediff, count(distinct uid) as user_cnt from ( select uid, datediff(act_date，'2019-08-01') as datediff from t_user_active_accu where ds = '20190808'--ds表示分區，取一個最新的即可 and datediff('2019-08-01', act_date) >=0 ) a group by datediff ) b
上面兩種計算留存的方式都比較好理解，使用了datediff函數，但需要注意使用的場景，一種是增量表，一種是全量表。
總結
本文我們從一道SQL題目出發，討論了使用union all 和datediff簡化代碼的過程，以及由此衍生出來的相關問題。由於表是虛構的，代碼並未經過完全測試，主要在於理解其中的技巧和原理。希望對讀到這裡的你有所幫助。如有問題歡迎指出，也歡迎交流討論~
