sqlserver/mysql按天、按小时、按分钟统计连续时间段数据【推荐】
一,写在前面的话
最近公司需要按天,按小时查看数据,可以直观的看到时间段的数据峰值。接到需求,就开始疯狂百度搜索,但是搜索到的资料有很多都不清楚,需要自己去总结和挖掘其中的重要信息。现在我把分享出来了呢,希望大家喜欢。
针对sqlserver,有几点需要给大家说清楚(不懂的自行百度):
•master..spt_values是什么东西?能用来做什么?
•如何产生连续的时间段(年,月,天,小时,分钟)
二,master..spt_values是什么东西?能用来做什么呢?
相对固定通用的取数字的表,主要作用就是取连续数字,不过有个缺陷就是只能取到2047。可以执行下面语句就知道什么意思了。
selectnumberfrommaster..spt_valueswheretype='p'
三,如何产生连续的时间段(年,月,天,小时,分钟)
在实际的运用中,目前主要是产生连续的时间段。我准备了常用的操作,那下面的语句就分别展示出来。
--按年产生连续的 SELECT substring(CONVERT(NVARCHAR(10),DateAdd(YEAR,number,'2016-01-01'),120),1,4)ASGroupDay,type FROM master..spt_values WHEREtype='p'ANDnumber<=DateDiff(YEAR,'2016-01-01','2019-01-01') --按月产生连续的 SELECT substring(CONVERT(NVARCHAR(10),DateAdd(MONTH,number,'2019-01-01'),120),1,7)ASGroupDay,type FROM master..spt_values WHEREtype='p'ANDnumber<=DateDiff(MONTH,'2018-01-01','2019-01-01') --按天产生连续的 SELECT CONVERT(NVARCHAR(10),DateAdd(day,number,'2019-01-01'),120)ASGroupDay,type FROM master..spt_values WHEREtype='p'ANDnumber<=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)ASGroupDay,type FROM master..spt_values WHEREtype='p'ANDDATEDIFF(HH,DATEADD(HH,number,CONCAT('2019-01-18','','00:00')),CONCAT('2019-01-18','','23:00'))>=0 --按分钟的就自己可以YY了 ......
四,与业务场景进行结合
有了连续的数据过后,当然就是以时间为主,进行左连接。就可以查出统计数据了。
下面我就说说我使用的两个统计案例(是采用存储过程来实现了,所以有@符号的是变量),给到大家,至于看不看得懂,就看你的能力了。
--按天统计交易笔数 selecta.GroupDay,ISNULL(b.e,0)'feeCount'from( SELECT CONVERT(NVARCHAR(10),DateAdd(day,number,@paySdate),120)ASGroupDay,type FROM master..spt_values WHERE type='p'ANDnumber<=DateDiff(day,@paySdate,@payEdate) )a leftjoin (select convert(char(32),create_time,23)asd,count(*)ase from trade_logwherecreate_time>=@paySdateandcreate_time<=@payEdate groupbyconvert(char(32),create_time,23))bonb.d=a.GroupDay --按小时统计交易笔数 selecta.GroupDay,ISNULL(b.e,0)'feeCount'from( SELECT substring(convert(char(32),DATEADD(HH,number,CONCAT(@paySdate,'',@paySTime)),120),1,16)ASGroupDay,type FROM master..spt_values WHERE type='p'ANDDATEDIFF(HH,DATEADD(HH,number,CONCAT(@paySdate,'',@paySTime)),CONCAT(@payEdate,'',@payETime))>=0 )a leftjoin( select convert(char(32),create_time,23)asd,datepart(hh,create_time)ash, substring(convert(char(32),DATEADD(HH,datepart(hh,create_time),convert(char(32),create_time,23)),120),1,16)asst, count(*)ase from trade_log wherecreate_time>=@paySdateandcreate_time<=@payEdate andconvert(char(8),create_time,108)>=@paySTimeandconvert(char(8),create_time,108)<=@payETime groupbyconvert(char(32),create_time,23),datepart(hh,create_time))b onb.st=a.GroupDayorderbyGroupDay
五,总结及展望
掌握的知识点:
•熟悉了存储过程的语法和编写过程
•学习到了master..spt_values是什么?以及可以使用的场景?
•针对按时间进行统计,比如按天,小时进行统计的实现方法。
展望:
•局限性:这种方式目前只针对sqlserver,但是目前大部分都是mysql。
ps:MySQL按天,按周,按月,按时间段统计
自己做过MySQL按天,按周,按月,按时间段统计,但是不怎么满意,后来找到这位大神的博客,转载一下,谢谢这位博主的分享
知识点:DATE_FORMAT
使用示例
selectDATE_FORMAT(create_time,'%Y%m%d')days,count(caseid)countfromtc_casegroupbydays; selectDATE_FORMAT(create_time,'%Y%u')weeks,count(caseid)countfromtc_casegroupbyweeks; selectDATE_FORMAT(create_time,'%Y%m')months,count(caseid)countfromtc_casegroupbymonths;
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)
•%pAM或PM
•%w一个星期中的天数(0=Sunday……6=Saturday)
•%U星期(0……52),这里星期天是星期的第一天
•%u星期(0……52),这里星期一是星期的第一天
•%%一个文字“%”。
总结
以上所述是小编给大家介绍的sqlserver/mysql按天、按小时、按分钟统计连续时间段数据,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!
如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!