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

这篇具有很好参考价值的文章主要介绍了MYSQL按照小时、按天、按月分组统计,无数据补0。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

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

现在市面既有补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 dateTime			
	FROM  mysql.help_category 
	WHERE @i<23
	order by dateTime asc

效果如下:
mysql 按天分组,mysql,数据库

关联要查询的表后

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 t1
LEFT JOIN
(
	SELECT date_format(td.create_time, '%H') AS hours, count(*) as count
	FROM test td
	WHERE
	td.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

结果如下:
mysql 按天分组,mysql,数据库
按天分组,统计指定某月的数据,无数据补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 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DATEDIFF(
	'2022-12-15',
	date_sub( '2022-12-12', INTERVAL 1 DAY ))

结果如下:
mysql 按天分组,mysql,数据库
关联业务表数据后:

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 ) dateTime
	FROM mysql.help_topic WHERE help_topic_id < DATEDIFF('2023-06-30',date_sub( '2023-06-01', INTERVAL 1 DAY ))
) t1
LEFT JOIN (
	SELECT date_format(td.create_time, '%m-%d') AS month, count(*) as count FROM test td
	WHERE
		td.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

执行结果:
mysql 按天分组,mysql,数据库
注意!!! 在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 ) dateTime
	FROM mysql.help_topic WHERE help_topic_id < DATEDIFF(#{queryEndTime,jdbcType=TIMESTAMP},date_sub(#{queryStartTime,jdbcType=TIMESTAMP}, INTERVAL 1 DAY ))
) t1
LEFT JOIN (
	SELECT date_format(td.create_time, '%m-%d') AS month, count(*) as count FROM test td
	WHERE
		td.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 

结果如下:
mysql 按天分组,mysql,数据库
关联业务表后:

SELECT t1.index,IFNULL(t2.count,0) FROM (
	SELECT @a:=@a + 1 AS `index` FROM mysql.help_topic,(SELECT @a:=0) temp LIMIT 12 
) t1
LEFT JOIN (
	SELECT date_format(td.create_time, '%m') AS month, count(*) as count FROM test td
	WHERE
	td.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

结果如下:
mysql 按天分组,mysql,数据库文章来源地址https://www.toymoban.com/news/detail-774706.html

到了这里,关于MYSQL按照小时、按天、按月分组统计,无数据补0的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • mysql统计近7天数据量,,按时间戳分组

    可以使用以下 SQL 语句来统计近7天的数据量,并按时间戳分组。如果某一天没有数据,则将其填充为0。 解释说明: DATE_FORMAT(FROM_UNIXTIME( timestamp ), \\\'%Y-%m-%d\\\') AS date 将时间戳转换为日期格式,例如 2021-03-01。 COUNT(*) AS count 统计每天数据的数量。 WHERE timestamp = UNIX_TIMESTAMP(DATE_SUB

    2024年02月13日
    浏览(58)
  • mysql查询一周内的数据(起始日期为星期一)、根据一周不同星期进行分组统计

    其中YEARWEEK ( time, 1) 获取的是时间的年以及是该年内的第几周,而其中的1则代表以星期一为一周的第一天。 不加1的情况就是,获取的数据是以星期日为一周的第一天的,与我们的需求不符。 直接在YEARWEEK ( now(), 1) 加上-1即是减一周,也就是上周。 数字1则代表星期一,2代表

    2024年02月13日
    浏览(49)
  • MYSQL之查询按日期分组统计

    1.1、dayofyear、dayofmonth、dayofweek dayofyear(date) 函数返回日期位于所在年份的第几天,范围是1 ~ 366 dayofmonth(date) 函数返回日期位于所在月份的第几天,范围是1 ~ 31 dayofweek(date) 函数返回日期位于所在周的第几天,范围是1 ~ 7 查询语句 查询结果    需要注意的是,如果是 dayofmon

    2024年02月02日
    浏览(42)
  • Mysql的分组查询和统计计算

    1.聚合函数 聚合函数实现数据的统计计算,用于计算表中的数据,返回单个计算结果。聚合函数包括COUNT、SUM、AVG、MAX、MIN等函数,下面分别介绍。 (1)COUNT函数 COUNT函数用于计算组中满足条件的行数或总行数。 语法格式: COUNT ( { [ ALL | DISTINCT ] 表达式 } | * ) 其中,ALL表示

    2024年02月16日
    浏览(39)
  • MySQL场景分析--按照班级查询,并统计各班级男、女的人数和总人数

    现在有一张学生表,字段如下。请按照班级查询,并统计各班级男、女的人数和总人数。 Group by SQL GROUP BY 子句用来根据指定的字段对结果集(选取的数据)进行分组,如果某些记录的指定字段具有相同的值,那么它们将被合并为一条数据。 通俗地理解,GROUP BY 子句将根据指

    2024年02月05日
    浏览(62)
  • MySQL、Oracle按年、月、日、季度 分组统计

    我们经常遇到一些需求,需要我们在sql语句中对日期进行分类统计,而mysql和oracle又有一些不同,下面是我总结的MySQL、Oracle按年、月、日、季度 分组统计,以后你在遇到类似的问题,在小王这一篇就可以搞定啦!    1、MySQL按年、月、日、季度 分组统计 首先我们来讲讲my

    2024年01月23日
    浏览(33)
  • 【工作记录】mysql中实现分组统计的三种方式

    前言 实际工作中对范围分组统计的需求还是相对普遍的,本文记录下在mysql中通过函数和sql完成分组统计的实现过程。 数据及期望 比如我们获取到了豆瓣电影top250,现在想知道各个分数段的电影总数. 表数据如下: 期望结果: 实现方案 主要思路是根据score的范围设置别名,然

    2024年02月13日
    浏览(55)
  • MySQL根据出生日期查询年龄,以及对年龄进行分组统计

    表stu数据如下 1. 根据出生日期查询年龄 返回结果: 函数DATE():提取日期或日期/时间表达式的日期部分; 函数CURDATE():返回当前的日期; 函数TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2):计算两个日期的时间差,unit是计算时间差的单位,可以是SECOND秒、MINUTE分钟、HOUR小时、DAY天

    2024年02月11日
    浏览(62)
  • Python学习——数据分组统计、分组运算及透视

    分割 split : 按照键值(key)或者分组变量将数据分组 应用 apply : 对每个组应用函数, 通常是累计,转换或过滤函数 组合 combine : 将每一组的结果合并成一个输出组 常用功能 新增加一列 年龄ew 将填充后的年龄补充上去 数据聚合(agg):一般指的是能够从数组产生的标量值的数

    2024年02月10日
    浏览(47)
  • 【Spark手机流量日志处理】使用SparkSQL按月统计流量使用量最多的用户

    🚀 作者 :“大数据小禅” 🚀 文章简介 :本篇文章属于Spark系列文章,专栏将会记录从spark基础到进阶的内容 🚀 内容涉及到Spark的入门集群搭建,核心组件,RDD,算子的使用,底层原理,SparkCore,SparkSQL,SparkStreaming等,Spark专栏地址.欢迎小伙伴们订阅💪 SparkSQL简介 Spark

    2023年04月15日
    浏览(47)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包