mysql - 如何在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

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

Answer 2

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

Answer 3

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

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_outselect  (sum(avg_value) / sum(cnt)) as avg_value,  sum(cnt) as cnt  '2024-12-31' as stat_datefrom (  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') tton duplicate key update  avg_value=tt.avg_value,  cnt=tt.cnt

Answer 4

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

本需求的难点在于补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 counsFROM     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 counsFROM     interface_access_frequency     WHERE        DATE(create_time) = '2024-04-27'GROUP BY     grouped_timeORDER BY create_time DESC )UNIONSELECT  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 countsFROM     interface_access_frequency     WHERE        DATE(create_time) = '2024-04-27'GROUP BY     grouped_time)UNIONSELECT  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 5

为了按每5分钟间隔汇总一天的数据量，你可以使用MySQL的DATE_FORMAT函数结合GROUP BY子句来实现。下面是一个可能的解决方案，其中假设你的表名为your_table，日期字段名为datetime_column：

SELECT     CONCAT(        DATE_FORMAT(datetime_column, '%Y-%m-%d %H'),        LPAD(FLOOR(MINUTE(datetime_column) / 5), 2, '0'),        ':00:00'    ) AS time_interval,    COALESCE(COUNT(*), 0) AS countFROM     your_tableWHERE     DATE(datetime_column) = CURDATE()GROUP BY     time_intervalORDER BY     time_interval;

这个查询会按每5分钟间隔生成一个时间区间，并对每个时间区间的数据进行计数。如果某个时间区间内没有数据，则使用COALESCE函数将其计数显示为0。

对于进阶写法，要按每10分钟范围进行统计，你可以简单地将MINUTE(datetime_column) / 5改为MINUTE(datetime_column) / 10，这样结果就会固定为144条。

SELECT     CONCAT(        DATE_FORMAT(datetime_column, '%Y-%m-%d %H'),        LPAD(FLOOR(MINUTE(datetime_column) / 10), 2, '0'),        ':00:00'    ) AS time_interval,    COALESCE(COUNT(*), 0) AS countFROM     your_tableWHERE     DATE(datetime_column) = CURDATE()GROUP BY     time_intervalORDER BY     time_interval;

这些查询假设你只对当前日期的数据进行统计。如果你需要统计其他日期，可以相应地修改WHERE子句中的条件。