详解sqlserver/mysql按天、按小时、按分钟统计连续时间段数据

你的人生财富导师 08-05 642

前言：

目前朋友们对“mysql按时间段查询”可能比较关注，兄弟们都需要剖析一些“mysql按时间段查询”的相关文章。那么小编在网摘上网罗了一些对于“mysql按时间段查询””的相关知识，希望各位老铁们能喜欢，各位老铁们快快来了解一下吧！

一、概述

最近公司需要按天，按小时查看数据，可以直观的看到时间段的数据峰值。接到需求，就开始疯狂百度搜索，但是搜索到的资料有很多都不清楚，需要自己去总结和挖掘其中的重要信息。现在我把分享出来了呢，希望大家喜欢。

针对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)