SQL Server：计算年日维度的水库入库流量平滑移动平均值

嘿，这个环形移动平均的需求挺贴合实际场景的，毕竟年日是循环的周期数据，年初的前10天其实对应上年年末的日期。我来给你提供一个通用的实现方案：

核心思路 #

因为常规的移动窗口函数不支持环形周期，我们可以通过扩展数据集的方式来模拟环形窗口：把原数据复制一份，将复制后的年日字段加365，这样年初的日期就能关联到上年年末的记录（对应复制数据里的366-730区间），年尾的日期同理能关联到下年年初的记录。

完整SQL代码 #

假设你的子查询sub包含两个字段：day_of_year（年日，1-365）和daily_inflow（水库日均入库流量），那么完善后的查询如下：

WITH extended_sub AS (
    -- 原数据集 + 复制一份年日+365的数据集，模拟环形周期
    SELECT day_of_year, daily_inflow FROM sub
    UNION ALL
    SELECT day_of_year + 365, daily_inflow FROM sub
)
SELECT 
    original.day_of_year,
    original.daily_inflow,
    -- 计算前后10天（含当日）的21天移动平均值
    AVG(ext.daily_inflow) AS rolling_21d_avg
FROM sub original
-- 关联扩展数据集，筛选出当前年日前后10天的所有记录
JOIN extended_sub ext 
    ON ext.day_of_year BETWEEN original.day_of_year - 10 AND original.day_of_year + 10
GROUP BY original.day_of_year, original.daily_inflow
ORDER BY original.day_of_year;

代码解释 #

1. CTE extended_sub：将原数据复制并偏移365天，这样我们就有了1-730的连续年日范围，覆盖了完整的环形周期。
2. 自连接筛选窗口：通过BETWEEN original.day_of_year -10 AND original.day_of_year +10的条件，自动覆盖了环形场景：
   • 对于年日1，筛选范围是-9到11，扩展数据集中的356（1-10+365）到365会被匹配，加上原数据的1-11，正好是21天的范围
   • 对于年日55，筛选范围是45到65，直接匹配原数据的对应区间即可
3. 分组计算平均值：按原数据的年日和日均流量分组，计算关联到的所有记录的平均值，就是我们需要的平滑移动平均。

这个方案适用于大多数主流数据库（PostgreSQL、MySQL、SQL Server等），不需要依赖特定的数据库特性。

内容的提问来源于stack exchange，提问作者Ole Marius Gloppestad