MySQL如何在指定时间范围内按5分钟间隔分组计算平均值

你的原有查询逻辑可以正确计算有数据的5分钟区间的平均值，但缺少无数据的时间区间条目，原因是MySQL默认不会为没有匹配记录的分组生成行。调整方案如下：

适用MySQL 8.0及以上版本（推荐） #

使用递归CTE生成查询范围内所有5分钟时间序列，再左关联业务数据即可补全空区间：

WITH RECURSIVE time_intervals AS (
    -- 定义查询起始时间
    SELECT '2021-08-23 20:40:00' AS interval_start
    UNION ALL
    -- 每次递归加5分钟，直到覆盖全部查询范围
    SELECT interval_start + INTERVAL 5 MINUTE
    FROM time_intervals
    WHERE interval_start < '2021-08-23 21:35:00'
)
SELECT 
    ti.interval_start AS t,
    1 AS site_id,
    -- 无数据的区间平均值默认返回0，需要返回NULL可去掉COALESCE函数
    COALESCE(AVG(shm.response_time), 0) AS c
FROM time_intervals ti
LEFT JOIN sites_health_metrics shm 
    ON shm.site_id = 1
    AND shm.created_at >= ti.interval_start
    AND shm.created_at < ti.interval_start + INTERVAL 5 MINUTE
GROUP BY ti.interval_start
ORDER BY ti.interval_start;

适用MySQL 5.x低版本兼容方案 #

如果不支持递归CTE，可以手动生成数字序列构造时间区间：

SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP('2021-08-23 20:40:00') + n * 300) AS t,
    1 AS site_id,
    COALESCE(AVG(shm.response_time), 0) AS c
FROM (
    -- 1小时共12个5分钟区间，根据查询范围调整数字数量即可
    SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
) numbers
LEFT JOIN sites_health_metrics shm
    ON shm.site_id = 1
    AND FLOOR(UNIX_TIMESTAMP(shm.created_at) DIV 300) * 300 = UNIX_TIMESTAMP('2021-08-23 20:40:00') + n * 300
GROUP BY t
ORDER BY t;

调整说明 #

• 需要修改查询时间范围时，只需对应修改语句中的起始、结束时间即可
• 无数据区间的返回值可通过调整COALESCE的第二个参数自定义，比如要返回NULL直接删除COALESCE包裹即可

内容的提问来源于stack exchange，提问作者Abner Gorostieta