如何在SQL中计算5年与10年移动平均值并复刻Excel格式？

假设你的数据存储在名为monthly_data的表中，表结构包含以下字段：

• year：年份（整数类型，如2000、2001）
• month：月份（整数类型，1-12）
• value：每月的目标数值（数值类型，如DECIMAL）

核心思路 #

你需要的是同月份的跨年度滑动平均：

• 5年移动平均：仅当当前月份拥有连续5年的历史同月份数据时，计算过去5年（当前年份-5 至 当前年份-1）的同月份数值平均值
• 10年移动平均：仅当当前月份拥有连续10年的历史同月份数据时，计算过去10年（当前年份-10 至 当前年份-1）的同月份数值平均值

实现SQL #

SELECT
    year,
    month,
    value,
    -- 计算5年移动平均：仅当窗口内有5条有效数据时返回平均值，否则留空
    CASE
        WHEN COUNT(value) OVER (PARTITION BY month ORDER BY year ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) = 5
        THEN AVG(value) OVER (PARTITION BY month ORDER BY year ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING)
        ELSE NULL
    END AS five_year_moving_avg,
    -- 计算10年移动平均：仅当窗口内有10条有效数据时返回平均值，否则留空
    CASE
        WHEN COUNT(value) OVER (PARTITION BY month ORDER BY year ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) = 10
        THEN AVG(value) OVER (PARTITION BY month ORDER BY year ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING)
        ELSE NULL
    END AS ten_year_moving_avg
FROM monthly_data
ORDER BY year, month;

关键说明 #

1. 分区与排序：通过PARTITION BY month将数据按月份分组，确保只计算同月份的历史数据；ORDER BY year保证按年份顺序滑动。
2. 窗口范围：
   • 5年平均使用ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING，表示取当前行之前的第5行到第1行（对应过去5年的同月份数据）
   • 10年平均使用ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING，对应过去10年的同月份数据
3. 空值处理：用CASE WHEN判断窗口内的有效数据条数是否满足要求（5条或10条），不满足时返回NULL，对应“无足够历史数据留空”的需求。

示例验证 #

以你提到的例子：

• 2005年4月：窗口包含2000-2004年4月的数据（共5条），会计算这5个值的平均值
• 2010年8月：窗口包含2000-2009年8月的数据（共10条），会计算这10个值的平均值
• 2004年4月：若数据从2000年开始，窗口仅包含2000-2003年4月的数据（共4条），不满足5条的要求，5年移动平均字段留空

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