设为首页收藏本站Access中国

Office中国论坛/Access中国论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

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

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

[复制链接]
发表于 2019-11-16 14:32:43 | 显示全部楼层 |阅读模式
上回说到,咱们的数据开发攻城狮想到了宽表的建立。但宽表该如何建立,心里没个谱。如果直接按结果表来建立,似乎达不到优化脚本的目的。但除此之外,我们并没有更多的字段了。那么,是否可以考虑把用户ID作为主键来建立宽表,然后再进行统计呢?

于是,咱决定建立起这么一个表(图1):

我们只需要对日期右边的列进行sum统计,然后group by日期就可以得到以下结果了(图2):


同样地,我们也留意到,图1中,1月8日(含)以后再无新数据。这也从另一方面验证了图2的准确性。代码如下:
  1. create table log_result(
  2. userID int,min_Date date
  3. ,day0 int,day1 int,day2 int,day3 int
  4. ,day4 int,day5 int,day6 int,day7 int,day8 int)
复制代码
之所以设置day0~day8为int,是考虑到数值更便于统计,0表示没登录,1表示登录。当然,你也可以设置为varchar,用“是/否”来统计,又或者设置为bit。但我的印象中,bit只有0和1两个值,如果某天要统计当天登录次数,可能就需要更改表字段结构了。从这一点来看,设置为int可能更具有扩展性。——这是后话了,这里暂时表过。
建立好表之后,接下来就是写存储过程填充数据了。在写存储过程之前,我觉得有必要讲下流程步骤。按我的习惯来说,一般有以下几个步骤:

1、清空旧数据
2、插入基础数据(必要的地方可以把某些字段的默认值追加上去,例如,“0”,“其它”等等)
3、更新其他字段(必要的地方可以先把目标字段先初始化为默认值,例如,“0”,“其它”等等)
4、数据清洗(包括删除不符合条件的数据,清理一些特殊字符之类,具体看实际情况)
很多初学者往往会忘记第一步。结果每运行一次存储过程,就生成一批数据,最后导致数据重复。这里到底用truncate table还是delete from+where,需要看实际需求,前者效率比较高,但如需考虑到数据的可追溯性,或者不希望一次性插入大量数据,则应该选择用后者。

此外,也有人不喜欢预先建表,喜欢用生成表语句(select * into 结果表 from 源表)这样的话,好像听起来好像是减少了步骤(没有插入数据和更新数据),而且很灵活,需要哪些字段再手动alter table add column进去。在确认需求阶段,这样写无可厚非。但固化存储过程时则不应该再这样写了。因为每次生成一个表,然后需要几个字段,你后面还得add几个column,步骤更繁琐。

我们这个需求很明确了,所以按上面的create语句建表即可。接下来,就是存储过程的编写了。根据前面我们的思路,需要传入一个日期参数,然后根据这个日期参数确定后面的day0~day8。依次打开数据库/可编程性/存储过程,右击选择“新建存储过程”。我们可以看到SQL server已经给我们写了一个模板:

我们把尖括号里的内容替换为我们自己的东西,一个存储过程就写好了。
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. -- =============================================
  6. -- Author:                Roych
  7. -- Create date: 2019-11-16
  8. -- Description:        用于完成用户留存率数据源的计算
  9. -- =============================================
  10. CREATE PROCEDURE sp_user_count @min_date date=null
  11. AS
  12. BEGIN
  13.         SET NOCOUNT ON;
  14.         --定义9个日期变量
  15.         declare @day0 date,@day1 date,@day2 date,@day3 date,@day4 date
  16.                         ,@day5 date,@day6 date,@day7 date,@day8 date
  17.         /*
  18.         另一种变量声明的写法:
  19.         declare @day0 date
  20.         declare @day1 date
  21.         ……………………
  22.         declare @dayn date
  23.         */
  24.         --如果使用默认值(即无输入),则预设为系统日期前8天。
  25.         if @min_date is null
  26.                 set @min_date=dateadd(day,-8,GETDATE())
  27.         set @day0=@min_date
  28.         set @day1=DATEADD(day,1,@min_date)
  29.         set @day2=DATEADD(day,2,@min_date)
  30.         set @day3=DATEADD(day,3,@min_date)
  31.         set @day4=DATEADD(day,4,@min_date)
  32.         set @day5=DATEADD(day,5,@min_date)
  33.         set @day6=DATEADD(day,6,@min_date)
  34.         set @day7=DATEADD(day,7,@min_date)
  35.         set @day8=DATEADD(day,8,@min_date)
  36. --清空旧数据
  37. truncate table log_result
  38. --插入新数据(这里只插入day0~day8的数据,并初始化登录天数为0)
  39. insert into log_result
  40. select 用户ID,min(登录时间),0,0,0,0,0,0,0,0,0
  41. from log_info where 登录时间 between @day0 and @day8
  42. group by 用户ID

  43. --更新字段day0~day8
  44. update log_result set log_result.day0=1 from (select 用户ID from log_info where 登录时间=@day0) b
  45. where log_result.userID=b.用户ID

  46. update log_result set log_result.day1=1 from (select 用户ID from log_info where 登录时间=@day1) b
  47. where log_result.userID=b.用户ID

  48. update log_result set log_result.day2=1 from (select 用户ID from log_info where 登录时间=@day2) b
  49. where log_result.userID=b.用户ID

  50. update log_result set log_result.day3=1 from (select 用户ID from log_info where 登录时间=@day3) b
  51. where log_result.userID=b.用户ID

  52. update log_result set log_result.day4=1 from (select 用户ID from log_info where 登录时间=@day4) b
  53. where log_result.userID=b.用户ID

  54. update log_result set log_result.day5=1 from (select 用户ID from log_info where 登录时间=@day5) b
  55. where log_result.userID=b.用户ID

  56. update log_result set log_result.day6=1 from (select 用户ID from log_info where 登录时间=@day6) b
  57. where log_result.userID=b.用户ID

  58. update log_result set log_result.day7=1 from (select 用户ID from log_info where 登录时间=@day7) b
  59. where log_result.userID=b.用户ID

  60. update log_result set log_result.day8=1 from (select 用户ID from log_info where 登录时间=@day8) b
  61. where log_result.userID=b.用户ID
  62. END
  63. GO
复制代码
存储过程写完之后,我们可以执行一下:
exec sp_user_count '2019-1-1'
然后去查看log_result,看看结果是否与我们预想的一致:

看起来似乎已经达到了我们的预期。你以为这样就结束了?然鹅并没有。请看下回分解。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?注册

x
发表于 2021-10-11 10:20:15 | 显示全部楼层
学习
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2024-3-29 21:15 , Processed in 0.098131 second(s), 27 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

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