MySQL时间间隔统计：查询24小时内5分钟区间所有传感器平均流速

方案实现 #

你当前的SQL用SUM()聚合得到的是每个5分钟窗口所有流速的总和，要获取平均值只需将聚合函数替换为AVG()即可，建议同时返回时间区间字段方便后续图表渲染：

SELECT 
    -- 格式化输出5分钟窗口的起始时间，可直接作为图表X轴
    FROM_UNIXTIME(UNIX_TIMESTAMP(`timestamp`) DIV 300 * 300) AS time_interval,
    AVG(total_rate) AS avg_rate, -- 所有传感器的平均流速
    SUM(total_rate) AS total_rate -- 可保留总和字段，辅助判断高峰时段
FROM sensor_stats 
WHERE `timestamp` > NOW() - INTERVAL 24 HOUR
GROUP BY UNIX_TIMESTAMP(`timestamp`) DIV 300
ORDER BY time_interval ASC

特殊场景适配 #

如果同个传感器在单个5分钟窗口内存在多条上报数据，需要先单独计算每个传感器的时段平均，再统计全传感器平均，可使用以下写法：

WITH single_sensor_stats AS (
    SELECT 
        sensor_id,
        UNIX_TIMESTAMP(`timestamp`) DIV 300 AS time_slot,
        AVG(total_rate) AS single_sensor_avg
    FROM sensor_stats
    WHERE `timestamp` > NOW() - INTERVAL 24 HOUR
    GROUP BY sensor_id, time_slot
)
SELECT
    FROM_UNIXTIME(time_slot * 300) AS time_interval,
    AVG(single_sensor_avg) AS all_sensor_avg_rate
FROM single_sensor_stats
GROUP BY time_slot
ORDER BY time_interval ASC

结果使用说明 #

• 返回的time_interval字段直接对应每个5分钟区间的起始时间，绑定到图表X轴即可
• 可根据业务需求选择用avg_rate（平均流速）或total_rate（总流速）作为Y轴数据，判断全天流速最高的时段

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