作者：小5聊基础
简介：一只喜欢全栈方向的程序员，欢迎咨询，尽绵薄之力答疑解惑
编程原则：Write Less Do More
统计效果
【创建统计表】
以时间字符串作为主键，确保每天只能由唯一一条统计记录
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）步骤设置 数据库这里容易选错，如果没注意的话
、
3）设置计划 可以使用上一篇文章设置好的定时计划
4）开始作业 
【统计效果】
从统计效果可以得到一个有意思的结论
1）首位出现的概率稍微小一点
2）越靠近中间，值就越对称接近
