MySQL 5.6：基于土壤采样日期区间聚合天气数据（非子查询）

需求说明 #

使用MySQL 5.6，现有两张业务表：

• tblSoilSample：土壤样本表，每周产生1-2条采样记录，包含采样日期ObsDate和精确采样时间ObsDateTime
• weatherbridge：天气数据表，全天候每5分钟生成一条记录，包含记录时间record_time、当前温度TempOutCur、当前降雨量RainCur

需计算连续土壤采样区间内的平均温度和降雨总量，最终输出格式如下：

非子查询实现SQL #

SELECT
    si.ObsDate,
    ROUND(AVG(w.TempOutCur), 1) AS AvgIntervalTemp,
    ROUND(SUM(w.RainCur), 2) AS TotalIntervalRain
FROM
    (
        -- 生成采样时间区间：利用用户变量记录上一次采样时间，避免嵌套子查询
        SELECT
            ObsDate,
            @prev_start AS StartDateTime,
            @prev_start := ObsDateTime AS EndDateTime
        FROM
            tblSoilSample,
            (SELECT @prev_start := '1970-01-01 00:00') init_var
        ORDER BY
            ObsDateTime
    ) si
INNER JOIN
    weatherbridge w
ON
    w.record_time BETWEEN si.StartDateTime AND si.EndDateTime
GROUP BY
    si.ObsDate
ORDER BY
    si.ObsDate;

代码说明 #

1. 生成采样区间：通过用户变量@prev_start追踪上一次采样的精确时间，每条采样记录的区间开始时间为上一次采样时间，结束时间为当前采样时间。初始化变量设为早于所有业务数据的时间，确保第一条采样区间能覆盖初始时间到首次采样的所有天气数据。
2. 关联天气数据：将生成的采样区间表与天气数据表通过时间范围关联，筛选出每个区间内的所有天气记录。
3. 聚合计算：按采样日期分组，用AVG()计算区间平均温度，SUM()计算区间降雨总量，ROUND()调整小数位数匹配输出要求。

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