MySQL如何按以09:15为起始的小时区间分组统计数据？

问题描述 #

我有一张存储分钟级数据的MySQL表，数据从09:15开始，部分分钟可能无数据，表结构及数据如下：

data_timestmap      data_value
------------------- ----------
2022-11-25 09:15:59 3149.00
2022-11-25 09:16:59 3150.00
2022-11-25 09:17:59 3148.70
2022-11-25 09:18:59 3143.95
2022-11-25 09:19:59 3143.15
2022-11-25 09:46:59 3144.00
2022-11-25 09:59:59 3131.20
2022-11-25 10:00:59 3136.50
2022-11-25 10:01:59 3137.95
2022-11-25 10:02:59 3139.95
2022-11-25 10:04:59 3139.90
2022-11-25 10:17:59 3132.40
2022-11-25 10:18:59 3128.30
2022-11-25 10:19:58 3130.75
2022-11-25 10:20:58 3130.00
2022-11-25 10:51:58 3124.05
2022-11-25 10:52:58 3123.75
2022-11-25 10:53:58 3121.50
2022-11-25 10:54:58 3119.05
2022-11-25 10:59:57 3120.85
2022-11-25 11:00:58 3120.95
2022-11-25 11:01:58 3121.45
2022-11-25 11:02:58 3121.35
2022-11-25 11:03:58 3119.25
2022-11-25 11:09:57 3127.05
2022-11-25 11:10:59 3125.40
2022-11-25 11:11:58 3120.85
2022-11-25 11:12:58 3121.00
2022-11-25 11:13:59 3121.15
2022-11-25 11:14:58 3119.30
2022-11-25 11:15:59 3120.35
2022-11-25 11:16:59 3120.80

我原本使用以下查询按自然小时分组：

SELECT max(data_timestamp), max(data_value), min(data_value)
FROM data_table 
GROUP BY ( HOUR( data_timestamp ) + FLOOR( MINUTE( data_timestamp )/60  ));

但这个查询得到的分组是自然小时区间（比如09:00-10:00），每组的max(data_timestamp)是2022-11-25 09:59:59、2022-11-25 10:59:59这类。我需要的是按09:15-10:15、10:15-11:15这类自定义区间分组，每组的max(data_timestamp)对应区间的结束时间（比如2022-11-25 10:15:59、2022-11-25 11:15:59），请问该如何实现？

解决方案 #

核心思路是先将所有时间往前偏移15分钟，再按自然小时分组，这样原时间的09:15-10:15会被映射到偏移后的09:00-10:00区间，以此实现自定义分组逻辑。

具体SQL查询 #

如果需要直接取每组内实际存在的最晚时间（符合你要的2022-11-25 10:15:59这类结果），可以用这个版本：

SELECT 
    MAX(data_timestamp) AS group_end_time,
    MAX(data_value) AS max_data_value,
    MIN(data_value) AS min_data_value
FROM data_table
GROUP BY 
    DATE_FORMAT(DATE_SUB(data_timestamp, INTERVAL 15 MINUTE), '%Y-%m-%d %H');

逻辑说明 #

1. 时间偏移：用DATE_SUB(data_timestamp, INTERVAL 15 MINUTE)把所有时间往前推15分钟，比如原时间2022-11-25 09:15:59变成2022-11-25 09:00:59，2022-11-25 10:15:59变成2022-11-25 10:00:59。
2. 分组依据：将偏移后的时间格式化到小时级别（%Y-%m-%d %H），这样原09:15-10:15的所有数据都会被分到同一个组。
3. 获取组内最晚时间：用MAX(data_timestamp)直接取该分组内实际存在的最晚时间，正好对应你需要的区间结束点（比如09:15-10:15组的最晚时间就是10:15左右的记录）。

如果需要固定显示区间的结束时间（比如不管数据是否存在，都显示10:15:00），可以调整查询为：

SELECT 
    DATE_FORMAT(DATE_ADD(DATE_FORMAT(DATE_SUB(data_timestamp, INTERVAL 15 MINUTE), '%Y-%m-%d %H:00:00'), INTERVAL 75 MINUTE), '%Y-%m-%d %H:%i:%s') AS group_end_time,
    MAX(data_value) AS max_data_value,
    MIN(data_value) AS min_data_value
FROM data_table
GROUP BY 
    DATE_FORMAT(DATE_SUB(data_timestamp, INTERVAL 15 MINUTE), '%Y-%m-%d %H');

内容的提问来源于stack exchange，提问作者Prasad DLV