如何在MySQL中按每5分钟间隔汇总一天的数据量？

通过一天的努力，这个需求终于搞定，现将实现方案发出来供大家参考。

本需求的难点在于补0。想了好多办法都被否决掉了，最终的方案是新建一张表来存储时间段，虽然不是最佳方案，但是目前可以快速解决这个问题，如果有大神在不建表的情况搞定，膜拜一个。废话不多说，上表

CREATE TABLE `time_intervals` (
  `grouped_time` time DEFAULT NULL
)

很简单吧，就一个字段。接下来给表插入数据，手动输入太慢，代码太麻烦，直接写个存储过程，就可以搞定，上代码：

DELIMITER //  
CREATE PROCEDURE InsertTimeIntervals()  
BEGIN  
    DECLARE currentTime TIME DEFAULT '00:00:00';  
    DECLARE endTime TIME DEFAULT '23:55:00';  
    TRUNCATE  TABLE time_intervals;
    WHILE currentTime <= endTime DO  
        INSERT INTO time_intervals (grouped_time) VALUES (currentTime);  
        SET currentTime = ADDTIME(currentTime, '00:05:00');  
    END WHILE;  
END //  
DELIMITER ;

以上存储过程为每5分钟分割时间段。执行 CALL InsertTimeIntervals();

一共288行

接下来就是写SQL了，为了方便大家阅读和理解，我把SQL先拆分开，一步一步组装，方便大家能理解思路.

第一步：先按每5分钟查出实际数据，这个数据是没有补0的，SQL如下：

SELECT 
    DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') - INTERVAL (MINUTE(create_time) MOD 5) MINUTE AS grouped_time,
    COUNT(*) AS couns
FROM 
    interface_access_frequency 
    WHERE  
      DATE(create_time) = '2024-04-27'
GROUP BY 
    grouped_time;

第二步，使用UNION 链接 grouped_time表，进行补0，SQL如下

(SELECT 
    DATE_FORMAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') - INTERVAL (MINUTE(create_time) MOD 5) MINUTE,'%H:%i') AS grouped_time,
    COUNT(*) AS couns
FROM 
    interface_access_frequency 
    WHERE  
      DATE(create_time) = '2024-04-27'
GROUP BY 
    grouped_time
ORDER BY create_time DESC )
UNION
SELECT  DATE_FORMAT(grouped_time, '%H:%i') AS grouped_time,0 AS couns FROM time_intervals;

但是这样处理后，会有重复记录，所以第三步，再进行一次 GROUP BY ，就可以获得预期的数据了，最终SQL如下：

SELECT grouped_time,MAX(counts) AS counts FROM
((SELECT 
    DATE_FORMAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') - INTERVAL (MINUTE(create_time) MOD 5) MINUTE,'%H:%i') AS grouped_time,
    COUNT(*) AS counts
FROM 
    interface_access_frequency 
    WHERE  
      DATE(create_time) = '2024-04-27'
GROUP BY 
    grouped_time)
UNION
SELECT  DATE_FORMAT(grouped_time, '%H:%i') AS grouped_time,0 AS counts FROM time_intervals) a
 GROUP BY  grouped_time
  ORDER BY grouped_time ASC;

完美。

示例中业务表名为interface_access_frequency，时间字段为create_time，使用时记得修改！

使用阿里云数据库，版本8.0

如有发现错误好着更好解决办法，请留言斧正~~