一道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簡化程式碼的過程,以及由此衍生出來的相關問題。由於表是虛構的,程式碼並未經過完全測試,主要在於理解其中的技巧和原理。希望對讀到這裡的你有所幫助。如有問題歡迎指出,也歡迎交流討論~