sqlservermysql按天、按⼩时、按分钟统计连续时间段数
据【推荐】
sql中select是什么意思⼀，写在前⾯的话
最近公司需要按天，按⼩时查看数据，可以直观的看到时间段的数据峰值。接到需求，就开始疯狂百度搜索，但是搜索到的资料有很多都不清楚，需要⾃⼰去总结和挖掘其中的重要信息。现在我把分享出来了呢，希望⼤家喜欢。
针对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), 这⾥星期⼀是星期的第⼀天
•%% ⼀个⽂字“%”。
总结
以上所述是⼩编给⼤家介绍的sqlserver/mysql按天、按⼩时、按分钟统计连续时间段数据，希望对⼤家有所帮助，如果⼤家有任何疑问请给我留⾔，⼩编会及时回复⼤家的。在此也⾮常感谢⼤家对⽹站的⽀持！
如果你觉得本⽂对你有帮助，欢迎转载，烦请注明出处，谢谢！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论