按日期分组统计，补齐没有结果的日期，补0

最新推荐文章于 2024-02-23 10:30:00 发布

原创最新推荐文章于 2024-02-23 10:30:00 发布 · 2k 阅读

2 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种在数据库层面高效处理日期范围内的数据补全方法，通过创建包含指定日期范围所有日期的临时表，并将其与业务数据左连接，实现对缺失日期的自动补零，以确保数据分析的连续性和完整性。

在实际项目中，不免有数据分析模块，例如按时间分组，需要统计每天的数量，作为后端，可以在逻辑层进行处理，便利一边结果集，没有补0，也可以在数据层，查出来之后，直接就是想要的结果。下面介绍在数据层，直接获取已经处理好的数据。

这里利用存储过程，往临时表里插入指定日期范围的所有日期。

临时表结构如下：

CREATE TABLE `calendar` (
  `date` date NOT NULL,
  UNIQUE KEY `unique_date` (`date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

存储过程如下

DELIMITER $$


DROP PROCEDURE IF EXISTS `create_calendar`$$

CREATE DEFINER=`root`@`%` PROCEDURE `create_calendar`(s_date DATE, e_date DATE)
BEGIN
        SET @truncateSql = 'truncate table calendar';
        PREPARE tsql FROM @truncateSql; 
	EXECUTE tsql;
 
	WHILE s_date <= e_date DO
		INSERT IGNORE INTO calendar VALUES (DATE(s_date)) ;
		SET s_date = s_date + INTERVAL 1 DAY ;
	END WHILE ; 
 
END$$

DELIMITER ;

执行存储过程

CALL create_calendar('2019-09-01','2019-09-30')

结果如下

在业务层，先在业务表中进行统计查询，如下

SELECT DATE_FORMAT(t.`time`, '%Y-%m-%d') tdate, COUNT(*) num
              FROM tb_smoke_sensor_warn t
              WHERE  DATE_FORMAT(t.time,'%Y-%m-%d') >= '2019-09-01'
              AND DATE_FORMAT(t.time,'%Y-%m-%d') <= '2019-09-30'
            GROUP BY DATE_FORMAT(t.`time`, '%Y-%m-%d')

查询结果如下

将执行存储过程的临时表左连接统计查询的数据，就可以补齐不存在日期的数量

SELECT
  c.date,
  IFNULL(temp.num, 0) num
FROM
  calendar c
  LEFT JOIN
    (SELECT
      DATE_FORMAT(t.`time`, '%Y-%m-%d') tdate,
      COUNT(*) num
    FROM
      tb_smoke_sensor_warn t
    WHERE DATE_FORMAT(t.time, '%Y-%m-%d') >= '2019-09-01'
      AND DATE_FORMAT(t.time, '%Y-%m-%d') <= '2019-09-30'
    GROUP BY DATE_FORMAT(t.`time`, '%Y-%m-%d')) temp
    ON c.date = temp.tdate
ORDER BY DATE ASC

结果如下：