Office中国论坛/Access中国论坛

标题: 浅谈数据库开发（三）——从宽表到存储过程（之二） [打印本页]
作者: roych    时间: 2019-11-16 03:23
标题: 浅谈数据库开发（三）——从宽表到存储过程（之二）
前面说了。按这种写法，至少要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

  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)

  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    时间: 2021-10-11 10:19
学习



欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/) Powered by Discuz! X3.3