MySQL 当天,当周,当月,年份统计记录
一。前言
需求:统计本周每天的告警记录,统计本月每天的告警记录,统计本年的每月告警记录
要求:当统计本周时,则是输出本周的所有天数,统计的天数没有记录时则是返回0,其其它的也是按照这种要求。
例如:统计当前周的记录,今天是星期五,即有五天,所有就是统计这五天内的每一天记录树。
原始数据记录 处理数据
时间 名称 时间 数量
2021-09-24 11:04:20 xxx 2021-09-24 10->10
2021-09-22 10:10:00 xxx 2021-09-23 null->0
2021-09-22 10:40:00 xxx 2021-09-22 20->20
2021-09-21 11:20:00 xxx 2021-09-21 null->0
2021-09-20 12:00:00 xxx 2021-09-20 12->12
.............
二。分析
从要求中,我们了解到时间是有序,而且可以通过左连接得到上面的格式要求,所有操作过程可以分为两步:
1,我们需要一个有序的时间序列。怎么得到有序的时间序列?
2,得到统计数量。时间精确到时分秒,怎么能有效提前记录?
3,有序时间序列 左连接 统计数量
三。得到有序的时间序列
有序嘛,就是有序的记录,从时间格式看,可以通过一个数字就可以拼接时间,最终得到有序的时间序列。
通过mysql的时间操作函数拼接时间。(date_add)
1,date_add:对日期进行增减
2,date_format: 对日期格式化('2021-09-23 10:00:00' --> '2021-09-23')
1)本次需要借用MySQL的 mysql.help_topic 表的id序列
从图片可以看到0-642的有序数字,正好可以拿来拼接时间
2)拼接有序的时间序列
SELECT date_format( date_add('2021-09-23 10:00:00', INTERVAL - t.help_topic_id DAY ), '%Y-%m-%d' ) AS orderly_time FROM mysql.help_topic t WHERE t.help_topic_id <5
四。统计数量
五。左连接查询
从图中,我们已经得到基本的数据模型了,但是统计数量是为空,所有需要进一步处理数据:
使用函数:IFNULL, 如下图。
六。中间总结
从上面的sql语句可以看出,基本上变化不是很大,只有几个是可变的:
1,生成时间序列的范围:上面是使用一个固定值5来生成5个时间,从这个中,我们可以控制查询的时间范围,比如当月的每天记录,当周的每天记录,选择两个时间的每天记录。
2,时间格式化:示例中格式化到天'%Y-%m-%d',我们也可以控制到时'%Y-%m-%d %H:00:00'
3,时间的递减:示例是固定递减类型是day,当我们格式化时间到小时时,需要替换递减类型为hour
所以:总结了三个可变参数,用来控制时间的查询,接下来是使用Java实现的简单示例
七。java示例
因为sql语句的已经在上面分析过了,所