一，写在前面的话
最近公司需要按天，按小时查看数据，可以直观的看到时间段的数据峰值。接到需求，就开始疯狂百度搜索，但是搜索到的资料有很多都不清楚，需要自己去总结和挖掘其中的重要信息。现在我把分享出来了呢，希望大家喜欢。
针对sqlserver, 有几点需要给大家说清楚（不懂的自行百度）：
•master..spt_values 是什么东西？能用来做什么？
•如何产生连续的时间段（年， 月， 天，小时，分钟）
二，master..spt_values是什么东西？能用来做什么呢？
相对固定通用的取数字的表，主要作用就是取连续数字，不过有个缺陷就是只能取到2047。可以执行下面语句就知道什么意思了。
select number from master..spt_values where type='p'
三，如何产生连续的时间段（年， 月， 天，小时，分钟）
在实际的运用中，目前主要是产生连续的时间段。我准备了常用的操作，那下面的语句就分别展示出来。
-- 按年产生连续的
select
substring(convert(nvarchar(10), dateadd(year, number, '2016-01-01'),120),1,4) as groupday,type
from
master..spt_values
where type = 'p' and number <= datediff(year, '2016-01-01', '2019-01-01')
-- 按月产生连续的
select
substring(convert(nvarchar(10), dateadd(month, number, '2019-01-01'),120),1,7) as groupday,type
from
master..spt_values
where type = 'p' and number <= datediff(month, '2018-01-01', '2019-01-01')
-- 按天产生连续的
select
convert(nvarchar(10), dateadd(day, number, '2019-01-01'),120) as groupday,type
from
master..spt_values
where type = 'p' and number <= datediff(day, '2019-01-01', '2019-01-18')
-- 按小时产生连续的
select
substring(convert(char(32),dateadd(hh,number,concat('2019-01-18',' ', '00:00')),120),1,16) as groupday,type
from
master..spt_values
where type = 'p' and datediff(hh,dateadd(hh,number,concat('2019-01-18',' ', '00:00')),concat('2019-01-18',' ', '23:00'))>=0
-- 按分钟的就自己可以yy了
......
四，与业务场景进行结合
有了连续的数据过后，当然就是以时间为主，进行左连接。就可以查出统计数据了。
下面我就说说我使用的两个统计案例（是采用存储过程来实现了，所以有@符号的是变量），给到大家，至于看不看得懂，就看你的能力了。
-- 按天统计交易笔数
select a.groupday, isnull(b.e, 0) 'feecount' from (
select
convert(nvarchar(10), dateadd(day, number, @paysdate),120) as groupday,type
from
master..spt_values
where
type = 'p' and number <= datediff(day, @paysdate, @payedate)
) a
left join
(select
convert(char(32),create_time,23) as d, count(*) as e
from
trade_log where create_time >= @paysdate and create_time<=@payedate
group by convert(char(32),create_time,23)) b on b.d=a.groupday
-- 按小时统计交易笔数
select a.groupday, isnull(b.e,0) 'feecount' from (
select
substring(convert(char(32),dateadd(hh,number,concat(@paysdate,' ', @paystime)),120),1,16) as groupday,type
from
master..spt_values
where
type = 'p' and datediff(hh,dateadd(hh,number,concat(@paysdate,' ', @paystime)),concat(@payedate,' ', @payetime))>=0
) a
left join (
select
convert(char(32),create_time,23) as d, datepart(hh,create_time) as h,
substring(convert(char(32),dateadd(hh,datepart(hh,create_time),convert(char(32),create_time,23)),120),1,16) as st,
count(*) as e
from
trade_log
where create_time >= @paysdate and create_time<=@payedate
and convert(char(8),create_time,108)>=@paystime and convert(char(8),create_time,108)<=@payetime
group by convert(char(32),create_time,23),datepart(hh,create_time)) b
on b.st=a.groupday order by groupday
五，总结及展望
掌握的知识点：
•熟悉了存储过程的语法和编写过程
•学习到了master..spt_values是什么？以及可以使用的场景？
•针对按时间进行统计，比如按天，小时进行统计的实现方法。
展望:
•局限性：这种方式目前只针对sqlserver, 但是目前大部分都是mysql。
ps：mysql按天，按周，按月，按时间段统计
自己做过mysql按天，按周，按月，按时间段统计，但是不怎么满意，后来找到这位大神的博客，转载一下，谢谢这位博主的分享
知识点：date_format
使用示例
select date_format(create_time,'%y%m%d') days,count(caseid) count from tc_case group by days; select date_format(create_time,'%y%u') weeks,count(caseid) count from tc_case group by weeks; select date_format(create_time,'%y%m') months,count(caseid) count from tc_case group by months;
date_format(date,format)
根据format字符串格式化date值。下列修饰符可以被用在format字符串中：
•%m 月名字(january……december)
•%w 星
期名字(sunday……saturday)
•%d 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
•%y 年, 数字, 4 位
•%y 年, 数字, 2 位
•%a 缩写的星期名字(sun……sat)
•%d 月份中的天数, 数字(00……31)
•%e 月份中的天数, 数字(0……31)
•%m 月, 数字(01……12)
•%c 月, 数字(1……12)
•%b 缩写的月份名字(jan……dec)
•%j 一年中的天数(001……366)
•%h 小时(00……23)
•%k 小时(0……23)
•%h 小时(01……12)
•%i 小时(01……12)
•%l 小时(1……12)
•%i 分钟, 数字(00……59)
•%r 时间,12 小时(hh:mm:ss [ap]m)
•%t 时间,24 小时(hh:mm:ss)
•%s 秒(00……59)
•%s 秒(00……59)
•%p am或pm
•%w 一个星期中的天数(0=sunday ……6=saturday ）
•%u 星期(0……52), 这里星期天是星期的第一天
•%u 星期(0……52), 这里星期一是星期的第一天
•%% 一个文字“%”。
总结
以上所述是www.887551.com给大家介绍的sqlserver/mysql按天、按小时、按分钟统计连续时间段数据,希望对大家有所帮助