sqlserver/mysql按天,按小时,按分钟统计连续时间段数据
程序员文章站
2022-04-15 15:27:49
文 | 子龙 有技术,有干货,有故事的斜杠青年 一,写在前面的话 最近公司需要按天,按小时查看数据,可以直观的看到时间段的数据峰值。接到需求,就开始疯狂百度搜索,但是搜索到的资料有很多都不清楚,需要自己去总结和挖掘其中的重要信息。现在我把分享出来了呢,希望大家喜欢。 针对sqlserver, 有几点 ......
文 | 子龙 有技术,有干货,有故事的斜杠青年
一,写在前面的话
最近公司需要按天,按小时查看数据,可以直观的看到时间段的数据峰值。接到需求,就开始疯狂百度搜索,但是搜索到的资料有很多都不清楚,需要自己去总结和挖掘其中的重要信息。现在我把分享出来了呢,希望大家喜欢。
针对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。
希望大神如果有mysql的实现方式,留下联系方式,小弟也虚心请教学习一下。
写得不好,望大家体谅。
上一篇: 数据库基本概念(二)
推荐阅读
-
mysql - php可以按天连续去两周的更新数据吗
-
MySQL实现按天统计数据的方法
-
sqlserver/mysql按天、按小时、按分钟统计连续时间段数据【推荐】
-
sqlserver/mysql按天,按小时,按分钟统计连续时间段数据
-
yii 框架实现按天,月,年,自定义时间段统计数据的方法分析
-
mysql之订单时间统计---按天---横向地区时间(一天)统计,纵向时间(按24小时)机柜统计
-
MySQL实现按天统计数据的方法
-
mysql - PHP按某月统计每个小时生成的数据
-
mysql - PHP按某月统计每个小时生成的数据
-
sqlserver/mysql按天、按小时、按分钟统计连续时间段数据【推荐】