MYSQL按照小时、按天、按月分组统计,无数据补0
作者:mmseoamin日期:2023-12-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 dateTime			
	FROM  mysql.help_category 
	WHERE @i<23
	order by dateTime asc

效果如下:

MYSQL按照小时、按天、按月分组统计,无数据补0,在这里插入图片描述,第1张

关联要查询的表后

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按照小时、按天、按月分组统计,无数据补0,在这里插入图片描述,第2张

按天分组,统计指定某月的数据,无数据补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按照小时、按天、按月分组统计,无数据补0,在这里插入图片描述,第3张

关联业务表数据后:

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按照小时、按天、按月分组统计,无数据补0,在这里插入图片描述,第4张

注意!!! 在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按照小时、按天、按月分组统计,无数据补0,在这里插入图片描述,第5张

关联业务表后:

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按照小时、按天、按月分组统计,无数据补0,在这里插入图片描述,第6张