浅谈数据库开发（三）——从宽表到存储过程（之二）

roych

前面说了。按这种写法，至少要union all七八个子句。且不说容易出错，万一某天，甲方爸爸说，我今天不想看1月1日的数据，我想看1月8日开始的留存率。。。。估计这时候你就一波操作猛如虎，“查找，替换。”先把1月1日的改成1月8日，然后把1月2日的改成1月9日……如此类推。过几天，甲方爸爸又说，我想看1月3日一个星期内的留存率。。。。于是你心里一万匹草泥马奔腾而过，腹诽不已，“这特么还有完没完啊？”，但你不敢声张，只好脸上笑嘻嘻，心里MMP。
经过几番折腾，你决定用变量来处理。

1. declare @day0 date
   
2. declare @day1 date
   
3. declare @day2 date
   
4. declare @day3 date
   
5. declare @day4 date
   
6. declare @day5 date
   
7. declare @day6 date
   
8. declare @day7 date
   
9. declare @day8 date
   
10. 
    
11. set @day0='2019-1-1'
    
12. set @day1=DATEADD(day,1,@day0)
    
13. set @day2=DATEADD(day,2,@day0)
    
14. set @day3=DATEADD(day,3,@day0)
    
15. set @day4=DATEADD(day,4,@day0)
    
16. set @day5=DATEADD(day,5,@day0)
    
17. set @day6=DATEADD(day,6,@day0)
    
18. set @day7=DATEADD(day,7,@day0)
    
19. set @day8=DATEADD(day,8,@day0)
    
20. 
    
21. select 登录时间
    
22. ,SUM(day0) as day0
    
23. ,SUM(day1) as day1
    
24. ,SUM(day2) as day2
    
25. ,SUM(day3) as day3
    
26. ,SUM(day4) as day4
    
27. ,SUM(day5) as day5
    
28. ,SUM(day6) as day6
    
29. ,SUM(day7) as day7
    
30. ,SUM(day8) as day8
    
31. from(
    
32. select @day0 as 登录时间
    
33. ,count(distinct case when a.登录时间=@day0 then a.用户ID else null end) as day0
    
34. ,count(distinct case when a.登录时间=@day1 then a.用户ID else null end) as day1
    
35. ,count(distinct case when a.登录时间=@day2 then a.用户ID else null end) as day2
    
36. ,count(distinct case when a.登录时间=@day3 then a.用户ID else null end) as day3
    
37. ,count(distinct case when a.登录时间=@day4 then a.用户ID else null end) as day4
    
38. ,count(distinct case when a.登录时间=@day5 then a.用户ID else null end) as day5
    
39. ,count(distinct case when a.登录时间=@day6 then a.用户ID else null end) as day6
    
40. ,count(distinct case when a.登录时间=@day7 then a.用户ID else null end) as day7
    
41. ,count(distinct case when a.登录时间=@day8 then a.用户ID else null end) as day8
    
42. from log_info a,(select distinct 用户ID from log_info where 登录时间=@day0) b
    
43. where a.用户ID=b.用户ID and a.登录时间>=@day0
    
44. group by a.登录时间
    
45. union all
    
46. select @day1 as 登录时间
    
47. ,0 as day0
    
48. ,count(distinct case when a.登录时间=@day1 then a.用户ID else null end) as day1
    
49. ,count(distinct case when a.登录时间=@day2 then a.用户ID else null end) as day2
    
50. ,count(distinct case when a.登录时间=@day3 then a.用户ID else null end) as day3
    
51. ,count(distinct case when a.登录时间=@day4 then a.用户ID else null end) as day4
    
52. ,count(distinct case when a.登录时间=@day5 then a.用户ID else null end) as day5
    
53. ,count(distinct case when a.登录时间=@day6 then a.用户ID else null end) as day6
    
54. ,count(distinct case when a.登录时间=@day7 then a.用户ID else null end) as day7
    
55. ,count(distinct case when a.登录时间=@day8 then a.用户ID else null end) as day8
    
56. from log_info a,(select distinct 用户ID from log_info where 登录时间=@day1) b
    
57. where a.用户ID=b.用户ID
    
58. --新用户
    
59. and a.用户ID not in(select 用户ID from log_info c where c.登录时间=@day0)
    
60. group by a.登录时间
    
61. /*
    
62. 这里仅用2个表进行union all
    
63. 以下从略。
    
64. union all
    
65. …………
    
66. */
    
67. ) a
    
68. group by 登录时间

复制代码

这时候，你发现，只需要改下@day0的值，就能随时算出留存客户数了。你开始为自己的聪明鼓掌。但你还是隐隐有些担忧。现在甲方爸爸只是想看一个星期的数据，你已经写得这么复杂了，万一某天他心血来潮要看一个月的呢？此外，还有一个问题，数据都不是现成的。每次修改条件，就必须重新计算。你觉得这可能会影响体验。那么，这个是否可以做成一张表，每次运行时把数据插入再展示呢？
于是，你开始在论坛里翻到了roych写的那篇帖子，终于体会到宽表的好处了。那么表结构该怎么确定呢？是不是可以按结果表那样做？如果按那样建表的话，那么主键显然不好确定。用第一列的日期作为主键看起来没什么问题，
但实际上，你会发现，这个根本就不具备主键的气质。这个字段来源于一个常量。——虽然看起来像是变量，但到了底层，它每一行都是一样的。——如果真的采取这样的方案，有没有问题？
当然没有问题。前面我们不用存储过程都能写出来，何况现在改为存储过程，理论上应该更简单才对。但由于不存在主键或者唯一索引的要求，因此，原先的SQL语句也就谈不上优化了。
那么为了优化SQL语句，这宽表应该怎么建呢？欲知后事如何，且看下回分解。

GOODWIN

学习