MySQL：计算每日午夜重置的最近24小时降水量滚动累计

这个需求我之前处理气象数据时碰到过，核心痛点就是dailyrainin每天午夜会重置为0，直接用普通窗口函数求和最近24小时的记录肯定会出错——跨天的时候前一天的累计值被重置，没法直接累加。

核心思路 #

对于任意时间点T，最近24小时的降水量累计可以拆成两部分：

• 前一天的剩余时段：从T-24小时到前一天午夜的降水量，等于前一天的总降水量减去T-24小时时刻的dailyrainin值（因为dailyrainin是当天累计值，差值就是这段时间的增量）
• 当天的累计时段：从当天午夜到T的降水量，就是当前时刻的dailyrainin值（本身就是累计值）

把这两部分相加，就是T时刻的24小时滚动累计值。

实现代码（MySQL 8.0+ 支持CTE） #

WITH daily_totals AS (
    -- 计算每一天的总降水量（当天dailyrainin的最大值，因为午夜重置为0）
    SELECT 
        DATE(localdate) AS rain_date,
        MAX(dailyrainin) AS total_daily_rain
    FROM weather_data
    GROUP BY DATE(localdate)
)
SELECT
    w.localdate,
    w.dailyrainin,
    -- 滚动累计 = 前一天剩余时段雨量 + 当天累计雨量
    COALESCE(
        (SELECT dt.total_daily_rain 
         FROM daily_totals dt 
         WHERE dt.rain_date = DATE(DATE_SUB(w.localdate, INTERVAL 24 HOUR))),
        0
    ) -
    COALESCE(
        (SELECT d2.dailyrainin 
         FROM weather_data d2 
         WHERE d2.localdate = DATE_SUB(w.localdate, INTERVAL 24 HOUR)),
        0
    ) +
    w.dailyrainin AS runtotal
FROM weather_data w
ORDER BY w.localdate;

兼容低版本MySQL（无CTE） #

如果你的MySQL版本低于8.0，不支持CTE，可以把每日总降水量的计算直接嵌入子查询：

SELECT
    w.localdate,
    w.dailyrainin,
    COALESCE(
        (SELECT MAX(d.dailyrainin) 
         FROM weather_data d 
         WHERE DATE(d.localdate) = DATE(DATE_SUB(w.localdate, INTERVAL 24 HOUR))),
        0
    ) -
    COALESCE(
        (SELECT d2.dailyrainin 
         FROM weather_data d2 
         WHERE d2.localdate = DATE_SUB(w.localdate, INTERVAL 24 HOUR)),
        0
    ) +
    w.dailyrainin AS runtotal
FROM weather_data w
ORDER BY w.localdate;

关键细节说明 #

• COALESCE的作用：处理边界情况，比如当T-24小时没有对应数据（比如表的起始时间晚于这个点），默认取0避免空值错误。
• 数据唯一性：确保localdate字段是唯一的（因为是5分钟间隔存储，理论上每个时间点只有一条记录），否则子查询可能返回多个值导致报错。
• 缺失数据处理：如果存在5分钟间隔的缺失记录，你可能需要调整子查询，用MAX(d2.localdate) <= DATE_SUB(w.localdate, INTERVAL 24 HOUR)来获取最近的有效记录值，不过假设你的数据是完整的5分钟间隔，当前代码就足够。

示例验证 #

拿你给出的片段数据举例：

• 对于2018-04-24 00:05:00（dailyrainin=0.01），T-24小时是2018-04-23 00:05:00（假设该时刻dailyrainin=0），前一天总降水量是0.17，那么滚动累计就是0.17 - 0 + 0.01 = 0.18，符合预期。

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