MySQL如何按每日从首个小时起的6小时分段统计温度平均值？

核心思路 #

利用HOUR()提取小时数，通过FLOOR(HOUR(created_at)/6)将小时划分为0-3共4个6小时区间，再结合日期分组计算平均温度。

完整SQL示例 #

假设你的表名为temperature_data，温度字段为temp，执行以下语句即可得到预期结果：

SELECT
    DATE(created_at) AS 统计日期,
    CASE 
        WHEN FLOOR(HOUR(created_at)/6) = 0 THEN '00:00-05:59'
        WHEN FLOOR(HOUR(created_at)/6) = 1 THEN '06:00-11:59'
        WHEN FLOOR(HOUR(created_at)/6) = 2 THEN '12:00-17:59'
        WHEN FLOOR(HOUR(created_at)/6) = 3 THEN '18:00-23:59'
    END AS 时间分段,
    ROUND(AVG(temp), 2) AS 平均温度 -- 可选：保留2位小数
FROM temperature_data
GROUP BY DATE(created_at), FLOOR(HOUR(created_at)/6)
ORDER BY 统计日期, FLOOR(HOUR(created_at)/6);

关键部分说明 #

• DATE(created_at)：提取created_at的日期部分，确保按自然日分组统计
• FLOOR(HOUR(created_at)/6)：将小时数除以6取整，生成0-3的分段标识，对应每日4个6小时区间
• CASE语句：将分段标识转换为直观的时间范围文本，方便阅读
• ROUND(AVG(temp), 2)：可选，将平均温度保留2位小数，提升结果可读性
• GROUP BY和ORDER BY：按日期和分段标识分组，并确保结果按时间顺序排列

特殊场景调整 #

如果你的“每日从首个小时开始”指的是当日数据中最早的记录小时而非自然日0点，可以通过子查询先获取每日的起始小时，再计算分段：

WITH daily_start AS (
    SELECT DATE(created_at) AS stat_date, MIN(HOUR(created_at)) AS start_hour
    FROM temperature_data
    GROUP BY DATE(created_at)
)
SELECT
    td.stat_date AS 统计日期,
    CONCAT(
        LPAD((ds.start_hour + 6 * FLOOR((HOUR(td.created_at) - ds.start_hour)/6)), 2, '0'),
        ':00-',
        LPAD((ds.start_hour + 6 * FLOOR((HOUR(td.created_at) - ds.start_hour)/6) + 5), 2, '0'),
        ':59'
    ) AS 时间分段,
    AVG(td.temp) AS 平均温度
FROM (SELECT DATE(created_at) AS stat_date, created_at, temp FROM temperature_data) td
JOIN daily_start ds ON td.stat_date = ds.stat_date
GROUP BY td.stat_date, FLOOR((HOUR(td.created_at) - ds.start_hour)/6)
ORDER BY td.stat_date, FLOOR((HOUR(td.created_at) - ds.start_hour)/6);

内容的提问来源于stack exchange，提问作者Md. Khalakuzzaman Khan