【数据库】Sql Server存储过程通过作业定时执行按天统计记录

我正在参加「掘金·启航计划」

通过前两篇文章的学习，我们已经对创建表、存储过程、作业等功能点有所了解
本次将结合前面所学习的知识点，创建统计表以及结合作业定时按天以及实时统计域名各个长度的记录值

• 统计效果

主要知识点列表

编号	语言或插件	知识点	说明
1	sql	dateadd	时间加减，可以设置获取到前一天或者下一月时间等，比如：dateadd(day,-1,getdate())，-1获取前一天
2	sql	case when then else end	条件分支语句
3	sql	object_id	等同于，select id from sysobjects where name=对象名

【创建统计表】

以时间字符串作为主键，确保每天只能由唯一一条统计记录

create table domain_count_day(
  id int identity(1,1),
  length_three_count int,
  length_four_count int,
  length_five_count int,
  length_six_count int,
  length_seven_count int,
  length_eight_count int,
  create_time varchar(10) primary key
)

【临时表】

创建临时的同时，需要加一个判断
如果临时表存在，那么可以根据自己业务情况，先删除临时表，然后再通过select into的方法将查询到数据直接插入到临时表里，省了创建临时表设置的字段和类型

• 使用说明 1）使用object_id方法 2）特别要注意，一定要是(N'tempdb..#temp',N'U')格式，否则容易出现判断不准的情况 3）删除临时表使用drop table关键词，和删除表操作一样 4）select * from into 临时表 from(目标查询) as 别名

--创建临时表
if object_id(N'tempdb..#temp',N'U') is not null
begin
	--删除临时表
	drop table #temp2	
end
select * into #temp2 from(
	select 
	domain_length,
	count(1) as count_length
	from dbo.domain_table
	where convert(varchar(10),create_time,120)=@time_rows
	group by domain_length
	--order by domain_length asc
) as aaa

【创建存储过程】

• 逻辑说明 1）定义今天和昨天的记录值变量
  用于判断当前时间节点是否生成了记录，有记录则更新操作，没有记录则添加操作
  2）再定义今天和昨天的时间值变量
  用于过滤筛选今天和昨天的统计记录
  3）再定义对应38位长度的值变量
  4）创建临时表
  以今天或昨天时间为筛选记录，以域名长度为分组，进行记录统计，并设置域名长度和总数两个字段，追加到临时表里 5）通过38长度为筛选条件，赋值到对象变量里 6）最后根据是否存在统计记录进行添加和更新

--drop proc countDomainValueDay;
create procedure countDomainValueDay
as
begin 
	
	declare @prev_time_rows int
	declare @time_rows int
	declare @length_three_count int
	declare @length_four_count int
	declare @length_five_count int
	declare @length_six_count int
	declare @length_seven_count int
	declare @length_eight_count int
	declare @day_time_prev varchar(50)
	declare @day_time varchar(50)
	
	-----当前时间的前一天-----
	set @day_time_prev=convert(varchar(10),dateadd(day,-1,getdate()),120)
	
	--创建临时表
	if object_id(N'#temp') is not null
	begin
		--删除临时表
		drop table #temp	
	end
	select * into #temp from(
		select 
		domain_length,
		count(1) as count_length
		from dbo.domain_table
		where convert(varchar(10),create_time,120)=@day_time_prev
		group by domain_length
		--order by domain_length asc
	) as aaa

	select @length_three_count=count_length from #temp where domain_length=3
	select @length_four_count=count_length from #temp where domain_length=4
	select @length_five_count=count_length from #temp where domain_length=5
	select @length_six_count=count_length from #temp where domain_length=6
	select @length_seven_count=count_length from #temp where domain_length=7
	select @length_eight_count=count_length from #temp where domain_length=8
	
	--判断日统计记录是否存在 - 前一天
	select @prev_time_rows=count(1) from domain_count_day
	where convert(varchar(10),create_time,120)=@day_time_prev
	
	if @prev_time_rows<=0
	begin
		insert into domain_count_day(length_three_count,length_four_count,length_five_count,length_six_count,length_seven_count,length_eight_count,create_time)
		values(@length_three_count,@length_four_count,@length_five_count,@length_six_count,@length_seven_count,@length_eight_count,@day_time_prev)
	end
	else 
	begin
		update domain_count_day set
		length_three_count=@length_three_count,
		length_four_count=@length_four_count,
		length_five_count=@length_five_count,
		length_six_count=@length_six_count,
		length_seven_count=@length_seven_count,
		length_eight_count=@length_eight_count
		where create_time=@day_time_prev
	end
	-----/当前时间的前一天-----
	
	
	-----当前时间-----
	set @day_time=convert(varchar(10),getdate(),120)
	
	--创建临时表
	if object_id(N'#temp2') is not null
	begin
		--删除临时表
		drop table #temp2	
	end
	select * into #temp2 from(
		select 
		domain_length,
		count(1) as count_length
		from dbo.domain_table
		where convert(varchar(10),create_time,120)=@@day_time
		group by domain_length
		--order by domain_length asc
	) as aaa

	select @length_three_count=count_length from #temp2 where domain_length=3
	select @length_four_count=count_length from #temp2 where domain_length=4
	select @length_five_count=count_length from #temp2 where domain_length=5
	select @length_six_count=count_length from #temp2 where domain_length=6
	select @length_seven_count=count_length from #temp2 where domain_length=7
	select @length_eight_count=count_length from #temp2 where domain_length=8
	
	--判断日统计记录是否存在 - 当前
	select @time_rows=count(1) from domain_count_day
	where convert(varchar(10),create_time,120)=@day_time
	
	if @time_rows<=0
	begin
		insert into domain_count_day(length_three_count,length_four_count,length_five_count,length_six_count,length_seven_count,length_eight_count,create_time)
		values(@length_three_count,@length_four_count,@length_five_count,@length_six_count,@length_seven_count,@length_eight_count,@day_time)
	end
	else 
	begin
		update domain_count_day set
		length_three_count=@length_three_count,
		length_four_count=@length_four_count,
		length_five_count=@length_five_count,
		length_six_count=@length_six_count,
		length_seven_count=@length_seven_count,
		length_eight_count=@length_eight_count
		where create_time=@day_time
	end
end

【开启作业】

• 作业基本信息

• 步骤设置 数据库这里容易选错，如果没注意的话

• 设置计划 可以使用上一篇文章设置好的定时计划

• 开始作业

【统计效果】

从统计效果可以得到一个有意思的结论
1）首位出现的概率稍微小一点
2）越靠近中间，值就越对称接近