淺談數(shù)據(jù)庫開發(fā)（二）——從寬表到存儲(chǔ)過程（之一）

roych

前面說了寬表的建立，很多人可能覺得沒什么實(shí)戰(zhàn)，聽起來比較枯燥。這次我決定從一個(gè)簡單的例子講起�！�—七天留存率。我們的數(shù)據(jù)源是這樣的：

甲方爸爸要求的結(jié)果是這樣的：

為了便于后面工作的開展，我們先來解釋下這張表的數(shù)值來源。左邊第一列的日期是第一次登錄日期，其它列的日期表示，從第一次登陸后，這些用戶是否再次登陸。如果有，則統(tǒng)計(jì)它。以第二行為例：
第一個(gè)交叉的位置（即1月1日與1月1日的相交處）中的3，表示的是是1月1日有3個(gè)用戶登錄（用戶ID分別是1、2、3），1月2日時(shí)這三個(gè)人又登錄了。1月3日時(shí)只有2個(gè)人（ID2沒登錄），1月4日三個(gè)人又登錄，到了1月5日時(shí)又是2個(gè)人（ID3沒登錄）……如此類推。
再如第三行：
1月2日第一次登錄的只有ID4（1、2和3第一次登錄是在1月1日），因此，此后ID4分別在1月5日和1月6日登錄了，因此1月2日（行）與1月5日、1月6日（列）交叉處均為1……其它如此類推。

可能有人覺得，這個(gè)表那么復(fù)雜，到底能干嗎呢？其實(shí)很簡單。它就是用來統(tǒng)計(jì)新用戶留存率的。由左上角到右下角方向的對(duì)角線上的數(shù)字表示的是當(dāng)天的新用戶數(shù)（第一次登錄為新用戶）。該對(duì)角線上方的數(shù)字則表示老用戶數(shù)。由此可見，1月8-9日沒有新增用戶。


說完了這個(gè)矩陣圖的作用后，接下來，我們就開始考慮如何去實(shí)現(xiàn)它了。這時(shí)候可能有人會(huì)不屑地想著：這有啥難的？咱select，然后union一下不就得了？然后你寫出了以下語句：

1. select a.登錄時(shí)間
   
2. ,count(distinct case when a.登錄時(shí)間='2019-1-1' then a.用戶ID else null end) as day0
   
3. ,count(distinct case when a.登錄時(shí)間='2019-1-2' then a.用戶ID else null end) as day1
   
4. ,count(distinct case when a.登錄時(shí)間='2019-1-3' then a.用戶ID else null end) as day2
   
5. ,count(distinct case when a.登錄時(shí)間='2019-1-4' then a.用戶ID else null end) as day3
   
6. ,count(distinct case when a.登錄時(shí)間='2019-1-5' then a.用戶ID else null end) as day4
   
7. ,count(distinct case when a.登錄時(shí)間='2019-1-6' then a.用戶ID else null end) as day5
   
8. ,count(distinct case when a.登錄時(shí)間='2019-1-7' then a.用戶ID else null end) as day6
   
9. ,count(distinct case when a.登錄時(shí)間='2019-1-8' then a.用戶ID else null end) as day7
   
10. ,count(distinct case when a.登錄時(shí)間='2019-1-9' then a.用戶ID else null end) as day8
    
11. from log_info a,(select distinct 用戶ID from log_info where 登錄時(shí)間='2019-1-1') b
    
12. where a.用戶ID=b.用戶ID and a.登錄時(shí)間>='2019-1-1'
    
13. group by a.登錄時(shí)間
    
14. union all
    
15. select a.登錄時(shí)間
    
16. ,0 as day0
    
17. ,count(distinct case when a.登錄時(shí)間='2019-1-2' then a.用戶ID else null end) as day1
    
18. ,count(distinct case when a.登錄時(shí)間='2019-1-3' then a.用戶ID else null end) as day2
    
19. ,count(distinct case when a.登錄時(shí)間='2019-1-4' then a.用戶ID else null end) as day3
    
20. ,count(distinct case when a.登錄時(shí)間='2019-1-5' then a.用戶ID else null end) as day4
    
21. ,count(distinct case when a.登錄時(shí)間='2019-1-6' then a.用戶ID else null end) as day5
    
22. ,count(distinct case when a.登錄時(shí)間='2019-1-7' then a.用戶ID else null end) as day6
    
23. ,count(distinct case when a.登錄時(shí)間='2019-1-8' then a.用戶ID else null end) as day7
    
24. ,count(distinct case when a.登錄時(shí)間='2019-1-9' then a.用戶ID else null end) as day8
    
25. from log_info a,(select distinct 用戶ID from log_info where 登錄時(shí)間='2019-1-2') b
    
26. where a.用戶ID=b.用戶ID
    
27. --新用戶
    
28. and a.用戶ID not in(select 用戶ID from log_info c where c.登錄時(shí)間='2019-1-1')
    
29. group by a.登錄時(shí)間
    
30. /*
    
31. 這里僅用2個(gè)表進(jìn)行union all
    
32. 以下從略。
    
33. union all
    
34. …………
    
35. */

復(fù)制代碼

從腳本上看似乎沒什么問題，運(yùn)行一下：

你的結(jié)果是這樣的：

而甲方爸爸要求的第一行是這樣的：

換句話說，你把一行的數(shù)據(jù)拆分為7行了。當(dāng)然，也不是無可饒恕的問題。我們?cè)賡um一下不就得了？然后你會(huì)發(fā)現(xiàn)，似乎sum不了。原來是第一列的日期得修改下，于是你改成這樣：

1. select 登錄時(shí)間
   
2. ,SUM(day0) as day0
   
3. ,SUM(day1) as day1
   
4. ,SUM(day2) as day2
   
5. ,SUM(day3) as day3
   
6. ,SUM(day4) as day4
   
7. ,SUM(day5) as day5
   
8. ,SUM(day6) as day6
   
9. ,SUM(day7) as day7
   
10. ,SUM(day8) as day8
    
11. from(
    
12. select '2019-1-1' as 登錄時(shí)間
    
13. ,count(distinct case when a.登錄時(shí)間='2019-1-1' then a.用戶ID else null end) as day0
    
14. ,count(distinct case when a.登錄時(shí)間='2019-1-2' then a.用戶ID else null end) as day1
    
15. ,count(distinct case when a.登錄時(shí)間='2019-1-3' then a.用戶ID else null end) as day2
    
16. ,count(distinct case when a.登錄時(shí)間='2019-1-4' then a.用戶ID else null end) as day3
    
17. ,count(distinct case when a.登錄時(shí)間='2019-1-5' then a.用戶ID else null end) as day4
    
18. ,count(distinct case when a.登錄時(shí)間='2019-1-6' then a.用戶ID else null end) as day5
    
19. ,count(distinct case when a.登錄時(shí)間='2019-1-7' then a.用戶ID else null end) as day6
    
20. ,count(distinct case when a.登錄時(shí)間='2019-1-8' then a.用戶ID else null end) as day7
    
21. ,count(distinct case when a.登錄時(shí)間='2019-1-9' then a.用戶ID else null end) as day8
    
22. from log_info a,(select distinct 用戶ID from log_info where 登錄時(shí)間='2019-1-1') b
    
23. where a.用戶ID=b.用戶ID and a.登錄時(shí)間>='2019-1-1'
    
24. group by a.登錄時(shí)間
    
25. union all
    
26. select '2019-1-2' as 登錄時(shí)間
    
27. ,0 as day0
    
28. ,count(distinct case when a.登錄時(shí)間='2019-1-2' then a.用戶ID else null end) as day1
    
29. ,count(distinct case when a.登錄時(shí)間='2019-1-3' then a.用戶ID else null end) as day2
    
30. ,count(distinct case when a.登錄時(shí)間='2019-1-4' then a.用戶ID else null end) as day3
    
31. ,count(distinct case when a.登錄時(shí)間='2019-1-5' then a.用戶ID else null end) as day4
    
32. ,count(distinct case when a.登錄時(shí)間='2019-1-6' then a.用戶ID else null end) as day5
    
33. ,count(distinct case when a.登錄時(shí)間='2019-1-7' then a.用戶ID else null end) as day6
    
34. ,count(distinct case when a.登錄時(shí)間='2019-1-8' then a.用戶ID else null end) as day7
    
35. ,count(distinct case when a.登錄時(shí)間='2019-1-9' then a.用戶ID else null end) as day8
    
36. from log_info a,(select distinct 用戶ID from log_info where 登錄時(shí)間='2019-1-2') b
    
37. where a.用戶ID=b.用戶ID
    
38. --新用戶
    
39. and a.用戶ID not in(select 用戶ID from log_info c where c.登錄時(shí)間='2019-1-1')
    
40. group by a.登錄時(shí)間
    
41. /*
    
42. 這里僅用2個(gè)表進(jìn)行union all
    
43. 以下從略。
    
44. union all
    
45. …………
    
46. */
    
47. ) a
    
48. group by 登錄時(shí)間

復(fù)制代碼

這次結(jié)果總算出來了：

于是，你臉上露出老母親一樣疲憊而欣慰的笑容。你留意到這里只是2行數(shù)據(jù)，心里想到后面還要復(fù)制粘貼7個(gè)union all，心里又莫名泛起了一陣悲涼：原來SQL server是一種體力活啊。于是你想，到底有沒有別的辦法呢？
欲知后事如何，且看下回分解。
附上數(shù)據(jù)源和模擬結(jié)果供大家參考。



相關(guān)文章：
淺談數(shù)據(jù)庫開發(fā)（一）：關(guān)于寬表
淺談數(shù)據(jù)庫開發(fā)（二）——從寬表到存儲(chǔ)過程（之一）
淺談數(shù)據(jù)庫開發(fā)（三）——從寬表到存儲(chǔ)過程（之二）
淺談數(shù)據(jù)庫開發(fā)（四）——從寬表到存儲(chǔ)過程（之三）
淺談數(shù)據(jù)庫開發(fā)（五）——從寬表到存儲(chǔ)過程（之四）
淺談數(shù)據(jù)庫開發(fā)（六）——從寬表到存儲(chǔ)過程（之五）

chenwine

技術(shù)一流，言語風(fēng)趣！

GOODWIN

學(xué)習(xí)