文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MYSQL按照小时、按天、按月分组统计,无数据补0

2023-09-10 16:18

关注

在开发过程中,写统计接口时,总会遇到按时、按天、按月统计,无数据进行补零的业务需求

现在市面既有补0的做法;也有不补0的做法:

不补0对于数据统计来说简洁明了,一个单表分组查询完事。(也就是在前端展示时,某个时间没有数据则不展示该时间,对于用户来说,其实可以接受)
补0的做法在市面相对更广泛一些,就是某个时间没有数据也展示出来该时间且对应数据补0。

以下为根据时间序列进行关联查询

   目的:按小时、天、月进行分组统计,没有数据的时间段自动补零 (只能实现一个时间段内的数据条数统计)   实现方式:     基于左连接的方式进行查询    左表的数据为根据时间序列建立的时间段(比如:一天24小时,00-23  一个月:05-01 ~ 05-31)  右表的数据为业务表根据指定查询条件、对时间进行指定格式化后分组得到(分组目的:和左表生成的时间进行对应,以做关联)     左右两表按照时间进行关联 使用IFNULL方法进行为null补0

按小时分组,统计指定某天的数据,无数据补0

获取某天每小时的数据,若是想获取24小时的话,则小时设为23,若是想获取当天动态的话,可以用sysdate()代替CONCAT('2022-12-12',' 23:59:59')SET  @i=-1;SELECT  DATE_FORMAT(DATE_SUB(CONCAT('2022-12-12',' 23:59:59'),INTERVAL ( (@i:=@i+1) ) HOUR ),'%H') AS dateTimeFROM  mysql.help_category WHERE @i<23order by dateTime asc

效果如下:
在这里插入图片描述

关联要查询的表后

SELECT t1.date,IFNULL(t2.count,0) AS count FROM( SELECT @a:=@a + 1 AS `index`, DATE_FORMAT(ADDDATE( sysdate() ,INTERVAL @a HOUR),'%H') AS date FROM mysql.help_topic,(SELECT @a:=0) temp LIMIT 24 ) AS t1LEFT JOIN(SELECT date_format(td.create_time, '%H') AS hours, count(*) as countFROM test tdWHEREtd.create_time BETWEEN '2023-06-19 00:00:00' AND '2023-06-19 23:59:59'group by date_format(td.create_time, '%H')) t2 ON t1.date = t2.hours ORDER BY t1.date asc

结果如下:
在这里插入图片描述
按天分组,统计指定某月的数据,无数据补0

利用Mysql服务器自带的系统表,进行获取某段日期的每天时间2022-12-12 :开始日期;2022-12-25 :结束日期;SELECT RIGHT( date_add( date_sub( '2022-12-12', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ), 5 ) dateTime FROMmysql.help_topic WHEREhelp_topic_id < DATEDIFF('2022-12-15',date_sub( '2022-12-12', INTERVAL 1 DAY ))

结果如下:
在这里插入图片描述
关联业务表数据后:

SELECT t1.dateTime,IFNULL(t2.count,0) FROM (SELECT RIGHT ( date_add( DATE_SUB('2023-06-01', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ), 5 ) dateTimeFROM mysql.help_topic WHERE help_topic_id < DATEDIFF('2023-06-30',date_sub( '2023-06-01', INTERVAL 1 DAY ))) t1LEFT JOIN (SELECT date_format(td.create_time, '%m-%d') AS month, count(*) as count FROM test tdWHEREtd.create_time  BETWEEN '2023-06-01 00:00:00' AND '2023-06-30 23:59:59'group by date_format(td.create_time, '%m-%d')) t2 ON t1.dateTime = t2.month ORDER BY t1.dateTime asc

执行结果:
在这里插入图片描述
注意!!! 在mybatis中需动态查询则需要进行格式化处理(不处理则查出所有日期都为0)

SELECT t1.dateTime,IFNULL(t2.count,0) FROM (SELECT RIGHT ( date_add( DATE_SUB(date_format(#{queryStartTime,jdbcType=TIMESTAMP}, '%Y-%m-%d'), INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ), 5 ) dateTimeFROM mysql.help_topic WHERE help_topic_id < DATEDIFF(#{queryEndTime,jdbcType=TIMESTAMP},date_sub(#{queryStartTime,jdbcType=TIMESTAMP}, INTERVAL 1 DAY ))) t1LEFT JOIN (SELECT date_format(td.create_time, '%m-%d') AS month, count(*) as count FROM test tdWHEREtd.create_time  BETWEEN '2023-06-01 00:00:00' AND '2023-06-30 23:59:59'group by date_format(td.create_time, '%m-%d')) t2 ON t1.dateTime = t2.month ORDER BY t1.dateTime asc

按月分组,统计指定某年的数据,无数据补0

SELECT @a:=@a + 1 AS `index` FROM mysql.help_topic,(SELECT @a:=0) temp LIMIT 12 

结果如下:
在这里插入图片描述
关联业务表后:

SELECT t1.index,IFNULL(t2.count,0) FROM (SELECT @a:=@a + 1 AS `index` FROM mysql.help_topic,(SELECT @a:=0) temp LIMIT 12 ) t1LEFT JOIN (SELECT date_format(td.create_time, '%m') AS month, count(*) as count FROM test tdWHEREtd.create_time  BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59'group by date_format(td.create_time, '%m')) t2 ON t1.index = t2.month ORDER BY t1.index asc

结果如下:
在这里插入图片描述

来源地址:https://blog.csdn.net/qq_47614329/article/details/131455666

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯