设为首页收藏本站Access中国

Office中国论坛/Access中国论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

返回列表 发新帖
查看: 2794|回复: 1

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

[复制链接]
发表于 2019-11-16 03:23:33 | 显示全部楼层 |阅读模式
前面说了。按这种写法,至少要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. set @day0='2019-1-1'
  11. set @day1=DATEADD(day,1,@day0)
  12. set @day2=DATEADD(day,2,@day0)
  13. set @day3=DATEADD(day,3,@day0)
  14. set @day4=DATEADD(day,4,@day0)
  15. set @day5=DATEADD(day,5,@day0)
  16. set @day6=DATEADD(day,6,@day0)
  17. set @day7=DATEADD(day,7,@day0)
  18. set @day8=DATEADD(day,8,@day0)

  19. select 登录时间
  20. ,SUM(day0) as day0
  21. ,SUM(day1) as day1
  22. ,SUM(day2) as day2
  23. ,SUM(day3) as day3
  24. ,SUM(day4) as day4
  25. ,SUM(day5) as day5
  26. ,SUM(day6) as day6
  27. ,SUM(day7) as day7
  28. ,SUM(day8) as day8
  29. from(
  30. select @day0 as 登录时间
  31. ,count(distinct case when a.登录时间=@day0 then a.用户ID else null end) as day0
  32. ,count(distinct case when a.登录时间=@day1 then a.用户ID else null end) as day1
  33. ,count(distinct case when a.登录时间=@day2 then a.用户ID else null end) as day2
  34. ,count(distinct case when a.登录时间=@day3 then a.用户ID else null end) as day3
  35. ,count(distinct case when a.登录时间=@day4 then a.用户ID else null end) as day4
  36. ,count(distinct case when a.登录时间=@day5 then a.用户ID else null end) as day5
  37. ,count(distinct case when a.登录时间=@day6 then a.用户ID else null end) as day6
  38. ,count(distinct case when a.登录时间=@day7 then a.用户ID else null end) as day7
  39. ,count(distinct case when a.登录时间=@day8 then a.用户ID else null end) as day8
  40. from log_info a,(select distinct 用户ID from log_info where 登录时间=@day0) b
  41. where a.用户ID=b.用户ID and a.登录时间>=@day0
  42. group by a.登录时间
  43. union all
  44. select @day1 as 登录时间
  45. ,0 as day0
  46. ,count(distinct case when a.登录时间=@day1 then a.用户ID else null end) as day1
  47. ,count(distinct case when a.登录时间=@day2 then a.用户ID else null end) as day2
  48. ,count(distinct case when a.登录时间=@day3 then a.用户ID else null end) as day3
  49. ,count(distinct case when a.登录时间=@day4 then a.用户ID else null end) as day4
  50. ,count(distinct case when a.登录时间=@day5 then a.用户ID else null end) as day5
  51. ,count(distinct case when a.登录时间=@day6 then a.用户ID else null end) as day6
  52. ,count(distinct case when a.登录时间=@day7 then a.用户ID else null end) as day7
  53. ,count(distinct case when a.登录时间=@day8 then a.用户ID else null end) as day8
  54. from log_info a,(select distinct 用户ID from log_info where 登录时间=@day1) b
  55. where a.用户ID=b.用户ID
  56. --新用户
  57. and a.用户ID not in(select 用户ID from log_info c where c.登录时间=@day0)
  58. group by a.登录时间
  59. /*
  60. 这里仅用2个表进行union all
  61. 以下从略。
  62. union all
  63. …………
  64. */
  65. ) a
  66. group by 登录时间
复制代码
这时候,你发现,只需要改下@day0的值,就能随时算出留存客户数了。你开始为自己的聪明鼓掌。但你还是隐隐有些担忧。现在甲方爸爸只是想看一个星期的数据,你已经写得这么复杂了,万一某天他心血来潮要看一个月的呢?此外,还有一个问题,数据都不是现成的。每次修改条件,就必须重新计算。你觉得这可能会影响体验。那么,这个是否可以做成一张表,每次运行时把数据插入再展示呢?
于是,你开始在论坛里翻到了roych写的那篇帖子,终于体会到宽表的好处了。那么表结构该怎么确定呢?是不是可以按结果表那样做?如果按那样建表的话,那么主键显然不好确定。用第一列的日期作为主键看起来没什么问题,
但实际上,你会发现,这个根本就不具备主键的气质。这个字段来源于一个常量。——虽然看起来像是变量,但到了底层,它每一行都是一样的。——如果真的采取这样的方案,有没有问题?
当然没有问题。前面我们不用存储过程都能写出来,何况现在改为存储过程,理论上应该更简单才对。但由于不存在主键或者唯一索引的要求,因此,原先的SQL语句也就谈不上优化了。
那么为了优化SQL语句,这宽表应该怎么建呢?欲知后事如何,且看下回分解。
发表于 2021-10-11 10:19:33 | 显示全部楼层
学习
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|站长邮箱|小黑屋|手机版|Office中国/Access中国 ( 粤ICP备10043721号-1 )  

GMT+8, 2024-3-29 20:15 , Processed in 0.090021 second(s), 26 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表