最近遇到一道關(guān)于用戶來(lái)源及去向的SQL題目，當(dāng)時(shí)思考的時(shí)候存在考慮不周全的地方铺呵，故專門模擬數(shù)據(jù)從問(wèn)題出發(fā)重新整理思路分享出來(lái)愕秫。
案例
現(xiàn)有一批數(shù)據(jù)記錄為某天多個(gè)用戶app的使用情況丧肴，數(shù)據(jù)存放在record表中蘸劈，其中有四個(gè)字段類型：
- id：記錄的id標(biāo)識(shí)
- user_id：用戶的id標(biāo)識(shí)
- app_name：用戶打開app的應(yīng)用名稱
- create_time：用戶打開app的時(shí)間
現(xiàn)要求得不同來(lái)源去向的用戶數(shù)，即淘寶->京東弦叶、京東->唯品會(huì)等來(lái)源去向的用戶數(shù)俊犯，并寫出對(duì)應(yīng)的SQL語(yǔ)句。
思路及解答
首先伤哺，觀察數(shù)據(jù)后發(fā)現(xiàn)用戶可能有多次打開同一app和用戶只打開一個(gè)app的情況瘫析。其次，打開app的時(shí)間都在某天所以這里不考慮按不同天來(lái)計(jì)算默责。這里我們需要將原問(wèn)題拆解為小問(wèn)題，需要思考的問(wèn)題有（如有遺漏可在評(píng)論區(qū)留言）：
- 如何表示用戶使用app的來(lái)源及去向咸包？
- 怎么判斷app的使用順序桃序？
- 用戶多次打開app會(huì)不會(huì)影響統(tǒng)計(jì)結(jié)果？
- 用戶只打開一個(gè)app并沒有去向如何處理烂瘫？
- 如何計(jì)算不同來(lái)源去向的用戶數(shù)媒熊？
解決問(wèn)題1和2需要查詢到用打開app后下一個(gè)打開app是哪個(gè)。在SQL語(yǔ)言里面如果要得到兩兩交叉的結(jié)果坟比，需要使用到交叉關(guān)聯(lián)芦鳍，同時(shí)需要是同個(gè)用戶打開的app，具體實(shí)現(xiàn)代碼如下：
SELECT
a.user_id,
a.app_name AS start_app,
a.create_time AS start_time,
b.app_name AS end_app,
b.create_time AS end_time
FROM record a
JOIN record b
WHERE a.user_id = b.user_id
從上圖的查詢結(jié)果可以看到兩個(gè)問(wèn)題葛账，交叉關(guān)聯(lián)后來(lái)源與去向重合柠衅，來(lái)源app打開時(shí)間要大于去向打開時(shí)間。于是我們需要添加兩個(gè)條件即start_app不等于end_app籍琳，start_time要小于end_time菲宴，修改后的代碼如下：
SELECT
a.user_id,
a.app_name AS start_app,
a.create_time AS start_time,
b.app_name AS end_app,
b.create_time AS end_time
FROM record a
JOIN record b
WHERE a.user_id = b.user_id
AND a.app_name != b.app_name
AND a.create_time < b.create_time
這樣的處理同時(shí)問(wèn)題4也解決贷祈，因?yàn)楫?dāng)用戶只打開一個(gè)app的時(shí)候交叉關(guān)聯(lián)后去向app還是自身，所以在上述操作中已經(jīng)過(guò)濾喝峦。從上圖可以發(fā)現(xiàn)1001用戶在打開唯品會(huì)前打開過(guò)兩次淘寶势誊，故有兩條來(lái)源去向的記錄，這里就會(huì)造成重復(fù)統(tǒng)計(jì)谣蠢，所以我們需要用戶最后一次的來(lái)源去向記錄即可粟耻，具體修改如下：
SELECT
a.user_id,
a.app_name AS start_app,
MAX(a.create_time) AS start_time,
b.app_name AS end_app,
b.create_time AS end_time
FROM record a
JOIN record b
WHERE a.user_id = b.user_id
AND a.app_name != b.app_name
AND a.create_time < b.create_time
GROUP BY a.user_id, a.app_name, b.app_name
現(xiàn)在，我們需要計(jì)算不同來(lái)源去向的用戶占比眉踱，即求得來(lái)源和去向分組后的user_id除重的結(jié)果挤忙，具體實(shí)現(xiàn)代碼如下：
SELECT
start_app,
end_app,
COUNT(DISTINCT user_id) AS user_num
FROM (
SELECT
a.user_id,
a.app_name AS start_app,
MAX(a.create_time) AS start_time,
b.app_name AS end_app,
b.create_time AS end_time
FROM record a
JOIN record b
WHERE a.user_id = b.user_id
AND a.app_name != b.app_name
AND a.create_time < b.create_time
GROUP BY a.user_id, a.app_name, b.app_name
) groups
GROUP BY start_app, end_app
總結(jié)
做SQL題目與寫代碼一樣，重要的是能得到最終的結(jié)果勋锤，故查詢效率最后考慮饭玲，需要優(yōu)先考慮查詢后的結(jié)果是否需要進(jìn)行篩選以及重復(fù)記錄的情況。將問(wèn)題拆解為小問(wèn)題有助于降低問(wèn)題難度叁执，同時(shí)也能對(duì)多方面有所考慮茄厘。總的來(lái)說(shuō)這道SQL題目并不難谈宛，關(guān)鍵在于思路是否清晰次哈，其次才是基礎(chǔ)知識(shí)的考驗(yàn)。
