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

roych · 发表于 2019-11-16 14:32:43

上回说到，咱们的数据开发攻城狮想到了宽表的建立。但宽表该如何建立，心里没个谱。如果直接按结果表来建立，似乎达不到优化脚本的目的。但除此之外，我们并没有更多的字段了。那么，是否可以考虑把用户ID作为主键来建立宽表，然后再进行统计呢？

于是，咱决定建立起这么一个表（图1）：

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

同样地，我们也留意到，图1中，1月8日（含）以后再无新数据。这也从另一方面验证了图2的准确性。代码如下：

create table log_result(
userID int,min_Date date
,day0 int,day1 int,day2 int,day3 int
,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已经给我们写了一个模板：

我们把尖括号里的内容替换为我们自己的东西，一个存储过程就写好了。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Roych
-- Create date: 2019-11-16
-- Description: 用于完成用户留存率数据源的计算
-- =============================================
CREATE PROCEDURE sp_user_count @min_date date=null
AS
BEGIN
SET NOCOUNT ON;
--定义9个日期变量
declare @day0 date,@day1 date,@day2 date,@day3 date,@day4 date
,@day5 date,@day6 date,@day7 date,@day8 date
/*
另一种变量声明的写法：
declare @day0 date
declare @day1 date
……………………
declare @dayn date
*/
--如果使用默认值（即无输入），则预设为系统日期前8天。
if @min_date is null
set @min_date=dateadd(day,-8,GETDATE())
set @day0=@min_date
set @day1=DATEADD(day,1,@min_date)
set @day2=DATEADD(day,2,@min_date)
set @day3=DATEADD(day,3,@min_date)
set @day4=DATEADD(day,4,@min_date)
set @day5=DATEADD(day,5,@min_date)
set @day6=DATEADD(day,6,@min_date)
set @day7=DATEADD(day,7,@min_date)
set @day8=DATEADD(day,8,@min_date)
--清空旧数据
truncate table log_result
--插入新数据（这里只插入day0~day8的数据，并初始化登录天数为0）
insert into log_result
select 用户ID,min(登录时间),0,0,0,0,0,0,0,0,0
from log_info where 登录时间 between @day0 and @day8
group by 用户ID
--更新字段day0~day8
update log_result set log_result.day0=1 from (select 用户ID from log_info where 登录时间=@day0) b
where log_result.userID=b.用户ID
update log_result set log_result.day1=1 from (select 用户ID from log_info where 登录时间=@day1) b
where log_result.userID=b.用户ID
update log_result set log_result.day2=1 from (select 用户ID from log_info where 登录时间=@day2) b
where log_result.userID=b.用户ID
update log_result set log_result.day3=1 from (select 用户ID from log_info where 登录时间=@day3) b
where log_result.userID=b.用户ID
update log_result set log_result.day4=1 from (select 用户ID from log_info where 登录时间=@day4) b
where log_result.userID=b.用户ID
update log_result set log_result.day5=1 from (select 用户ID from log_info where 登录时间=@day5) b
where log_result.userID=b.用户ID
update log_result set log_result.day6=1 from (select 用户ID from log_info where 登录时间=@day6) b
where log_result.userID=b.用户ID
update log_result set log_result.day7=1 from (select 用户ID from log_info where 登录时间=@day7) b
where log_result.userID=b.用户ID
update log_result set log_result.day8=1 from (select 用户ID from log_info where 登录时间=@day8) b
where log_result.userID=b.用户ID
END
GO

复制代码

存储过程写完之后，我们可以执行一下：
exec sp_user_count '2019-1-1'
然后去查看log_result，看看结果是否与我们预想的一致：

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

GOODWIN · 发表于 2021-10-11 10:20:15

学习

帐号		自动登录	找回密码
密码			注册

主题相关群

地区相关群[您还没设置居住地]

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

本帖子中包含更多资源

群介绍
主题无相 (随机显示版	同地区群块其他地区)

群介绍
Access免费培训群2 (792054000)