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

Question

MySQL语句查询：
将1天24小时按每5分钟分成小段，即
00:00:00 - 00:04:59 、
00:05:00 - 00:09:59 、
00:10:00 - 00:14:59 、
00:15:00 - 00:19:59 、
00:20:00 - 00:24:59 、
...
23:50:00 - 23:54:59 、
23:55:00 - 23:59:59 。

---

现有一张表，表中有一日期字段，类型为datetime
要求按天的维度统计 每个时间段内的数据数量，时间段内没有数据显示为0，其结果固定为288条。

求最简洁效率最高的写法~~

进阶写法，通过修改传参， 可按每10分钟的范围进行统计，这样固定结果就是 144条。

Answer 1

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

本需求的难点在于补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

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

Answer 2

根据你帖子的情况，这里假设:

1. 数据来源表是 t_in，统计结果表是 t_out；
2. 为简单起见，我们每隔一段时间统计 t_in 当中的 value 字段平均值，输出到 t_out 的 avg_value 字段；
3. 因为统计维度为天，所以 t_out 中要有一个 stat_date 主键，表示这条记录是哪天的，并且是每天一条记录；
4. 出于增量计算的需要，t_out 中还要有一个 cnt 字段，表示这个平均值是来自多少条记录的。

那么接下来我们要实现的，就是设计一个 SQL 语句，每隔一段时间执行，用于更新 t_out 表中的当天记录。过程就是：

1. 从 t_in 查询本次要捞取的记录，假设是 N 条；
2. 拼加上 t_out 当天的记录，如果有的话就得到 N+1 条；
3. 对这些记录重新计算 avg_value 和 cnt；
4. 将计算结果存回到 t_out。

这么做的好处就是，对数据库的负载保持稳定，每次处理的记录数不超过 N+1 条。那么具体的 SQL 怎么写呢，下面是一个例子。考虑到你用的是 MySQL，所以使用 on duplicate key update 可以将插入和更新写成一条。

insert into t_out
select
  (sum(avg_value) / sum(cnt)) as avg_value,
  sum(cnt) as cnt
  '2024-12-31' as stat_date
from (
  select
    sum(value) as avg_value, count(1) as cnt
  from t_in where [本次要查询的记录范围]
  union all
  select
    avg_value, cnt 
  from t_out where stat_date='2024-12-31'
) tt
on duplicate key update
  avg_value=tt.avg_value,
  cnt=tt.cnt

Answer 3

1、另外再建一张表tableB,包含start_time和end_time,存储 00:00:00 - 00:04:59 等分段信息；
2、判断 日期字段 在start_time和end_time 之间，就可以统计数量了;
3、如果要改成10分钟间隔的，修改tableB即可

Answer 4

SELECT FROM_UNIXTIME(s), FROM_UNIXTIME(s+299), c
FROM (
    SELECT FLOOR(UNIX_TIMESTAMP(t)/300)*300 AS s, COUNT(1) AS c
    FROM tbl
    GROUP BY s
    ORDER BY s ASC
) a;

Answer 5

我在浏览器里，用 DuckDB 试了一下（在线重现（可能消耗 500 MB 内存）），
用了 3 种方式实时生成 time_intervals 表，
可在 1 秒内分组统计完 10,000,000 条数据，并在缺失处补 0。

浏览器运行截图

1. 硬件：7 年前的低压轻薄本，有点慢。本地跑只需 0.1 秒（看来 wasm 的效率……）。
2. 受限于图片高度，时间间隔改成了 1 小时。可在 conf 表里改回来。
3. DuckDB 没有 json_table 或 json_each，所以方法三 intervals_json 麻烦些，用上了窗口函数来算数组下标。。
4. 浏览器内 DuckDB 似乎获取不了时区，所以慢了 8 小时。
5. MySQL 可参考方法二和三。

Answer 6

-- create table
CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    score INT,
    timestamp TIMESTAMP
);
-- test data
INSERT INTO scores (score, timestamp) VALUES
(80, '2024-04-01 09:00:00'),
(85, '2024-04-01 09:01:00'),
(75, '2024-04-01 09:02:00'),
(90, '2024-04-01 09:03:00'),
(95, '2024-04-01 09:04:00'),
(85, '2024-04-01 09:05:00'),
(70, '2024-04-01 09:06:00'),
(80, '2024-04-01 09:07:00'),
(75, '2024-04-01 09:08:00'),
(85, '2024-04-01 09:09:00'),
(90, '2024-04-01 09:10:00'),
(80, '2024-04-01 09:11:00'),
(85, '2024-04-01 09:12:00'),
(75, '2024-04-01 09:13:00'),
(90, '2024-04-01 09:14:00'),
(95, '2024-04-01 09:15:00');
-- query
SELECT 
    CONCAT(MIN(timestamp), ' - ', MAX(timestamp)) AS time_interval,
    AVG(score) AS average_score
FROM 
    scores
GROUP BY 
    UNIX_TIMESTAMP(timestamp) DIV (5 * 60)
ORDER BY 
    MIN(timestamp);