MySQL实现按小时统计温湿度均值并补全缺失时段空记录

• 你编写的INSERT语句中，用子查询作为VALUES的输入项，但每个子查询返回了多列/多行结果，而VALUES语法要求每个位置只能传入单个值，因此触发Operand should contain 1 column(s)报错
• 逻辑层面也存在缺陷：两个子查询分别统计两个点位的数据，没有按小时做关联对齐，也没有实现缺数小时补全NULL记录的需求

实现思路 #

• 第一步生成覆盖统计时间范围的连续整小时序列，包含所有需要统计的时间节点
• 对原表按整小时、点位分组，计算各点位每小时的温湿度平均值
• 用连续时间序列左关联统计结果，通过行列转换得到两个点位的统计值，没有数据的时段统计值自动为NULL，最终插入结果表

完整SQL代码 #

INSERT INTO temp_humid_total(cuisine_temp, cuisine_humid, chambre_temp, chambre_humid, stamp)
SELECT
    AVG(IF(t.source = 'cuisine', t.temp, NULL)) AS cuisine_temp,
    AVG(IF(t.source = 'cuisine', t.humid, NULL)) AS cuisine_humid,
    AVG(IF(t.source = 'chambre', t.temp, NULL)) AS chambre_temp,
    AVG(IF(t.source = 'chambre', t.humid, NULL)) AS chambre_humid,
    h.hour_stamp AS stamp
FROM (
    -- 生成覆盖原表时间范围的连续整小时序列
    SELECT DATE_FORMAT(MIN(stamp), '%Y-%m-%d %H:00:00') + INTERVAL (a.n + b.n * 10) HOUR AS hour_stamp
    FROM temp_humid
    CROSS JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    HAVING hour_stamp <= (SELECT DATE_FORMAT(MAX(stamp), '%Y-%m-%d %H:00:00') FROM temp_humid)
) h
LEFT JOIN temp_humid t ON DATE_FORMAT(t.stamp, '%Y-%m-%d %H:00:00') = h.hour_stamp
GROUP BY h.hour_stamp
ORDER BY h.hour_stamp;

代码说明 #

• 子查询h通过数字笛卡尔积生成连续小时序列，默认支持最长100小时的时间跨度，如需统计更大范围可调整笛卡尔积的层数
• 用IF条件配合AVG聚合直接实现行转列，不需要多次子查询关联，执行效率更高
• 左关联逻辑保证无采集数据的小时也会保留记录，对应统计值自动为NULL，完全符合需求要求

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