mysqlsql按日期统计(sqlserver/mysql按天、按小时、按分钟统计连续时间段数据推荐)
mysqlsql按日期统计
sqlserver/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)
•%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按天、按小时、按分钟统计连续时间段数据,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对开心学习网网站的支持!
如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!
- SQLServer中防止并发插入重复数据的方法详解(SQLServer中防止并发插入重复数据的方法详解)
- SQLSERVER 拼接含有变量字符串案例详解(SQLSERVER 拼接含有变量字符串案例详解)
- sqlserver备份整表数据的语句(SqlServer批量备份多个数据库且删除3天前的备份)
- sqlserver 锁表原因(SQL Server学习笔记之事务、锁定、阻塞、死锁用法详解)
- sqlserver访问远程数据库(SQL Server实现跨库跨服务器访问的方法)
- sqlserver创建带参数的存储过程(SQLServer存储过程实现单条件分页)
- sqlserver常用流控语句(SQL Server实现自动循环归档分区数据脚本详解)
- sqlserver并发性能(sql server中的任务调度与CPU深入讲解)
- sqlserver管理工具远程连接(MSSQLSERVER不同版本设置开启远程连接sa配置)
- sqlserver数据库如何分页(SQL server分页的4种方法示例很全面)
- sqlserver统计表大小(SqlServer 垂直分表减少程序改动)
- SQLServer将数据导出为SQL脚本
- 在sqlserver中如何看表格的类型(SQL Server 中的数据类型隐式转换问题)
- sqlserver判断null(SQL Server索引超出了数组界限的解决方案)
- mybatis执行sql源码解析(mybatis调用sqlserver存储过程返回结果集的方法)
- sqlserver中根据日期时间获取秒数(sql server编写通用脚本实现获取一年前日期的方法)
- CellPress旗下的6 期刊,国人友刊来了解一下吧(CellPress旗下的6期刊国人友刊来了解一下吧)
- ()
- SCI检索 SSCI检索 EI检索 ISTP检索 CSCD检索简介(SCI检索SSCI检索EI检索)
- 参考文献里期刊名称的写法,你知道吗(参考文献里期刊名称的写法)
- 硕博期刊 SCI SSCI CSSCI分不清 一文带你看懂主流期刊分类(硕博期刊SCISSCI)
- 辱华品牌新百伦官宣新代言人IU,个别粉丝希望get爱豆同款(辱华品牌新百伦官宣新代言人IU)
热门推荐
- python读取txt文件每一行数据(python读取txt文件并取其某一列数据的示例)
- ftp服务器怎么搭建linux(Linux下使用vsftp搭建FTP服务器附参数说明)
- python获取游戏画面信息(python游戏开发之视频转彩色字符动画)
- js弹出框代码(js实现自动锁屏功能)
- mysql行级锁使用教程(浅析MySQL的lru链表)
- docker容器内部怎么查看id(docker容器中无法获取宿主机hostname的解决方案)
- laravel服务器设置(基于Laravel-admin 后台的自定义页面用法详解)
- js怎么防抖(JS防抖节流函数的实现与使用场景)
- css实现水平垂直居中的方式有哪些(css让容器水平垂直居中的7种方式)
- tracert命令图解(tracert命令怎么用?tracert命令使用详解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9