SQL Server计算含缺失月份的最近X个月平均值方法

嘿，这个问题我在项目里碰过好多次了——要把缺失月份纳入最近3个月的平均计算，核心就是先把断档的月份补全，不然直接算平均会漏掉那些销量为0的月份，结果肯定不准。下面给你一步步拆解具体方案：

第一步：生成完整的月度日期序列 #

首先得确保我们有从最早记录到最晚记录之间的所有月份第一天，哪怕原数据里没有对应记录。假设你用的是Python Pandas，操作如下：

import pandas as pd

# 先把date列转成标准datetime类型
sales_df['date'] = pd.to_datetime(sales_df['date'])

# 生成连续的月度日期序列（MS代表每月第一天）
min_date = sales_df['date'].min()
max_date = sales_df['date'].max()
full_dates = pd.date_range(start=min_date, end=max_date, freq='MS')

# 转成数据框用于后续合并
full_months = pd.DataFrame({'date': full_dates})

第二步：合并原数据，补全缺失月份的销量 #

把原销售数据和完整月份序列左连接，缺失的销量会显示为NaN，再把这些NaN替换成0（因为没记录意味着当月销量为0）：

# 合并数据，保留所有完整月份
full_sales = pd.merge(full_months, sales_df, on='date', how='left')

# 将缺失的销量填充为0
full_sales['units sold'] = full_sales['units sold'].fillna(0)

第三步：计算最近3个月的滑动平均值 #

现在数据是连续的了，就可以用滚动窗口函数计算最近3个月的平均：

# 计算严格的最近3个月平均（前2个月因数据不足会显示NaN）
full_sales['3_month_avg'] = full_sales['units sold'].rolling(window=3, min_periods=3).mean()

# 如果想让前2个月也显示对应平均（比如第一个月显示自身销量，第二个月显示前2个月平均），可以调整min_periods：
# full_sales['3_month_avg'] = full_sales['units sold'].rolling(window=3, min_periods=1).mean()

举个实际例子验证 #

比如你提到的场景：2017-03-01销量20，4月无记录，5月销量100。补全后4月销量为0，那5月的3个月平均就是(20+0+100)/3 = 40，完美把缺失的4月纳入了计算。

额外提示（如果用SQL的情况） #

要是你用SQL处理数据，思路完全一致：先通过generate_series（PostgreSQL）或递归CTE（MySQL）生成连续月份表，左连接原销售表填充0，再用窗口函数计算滚动平均。比如PostgreSQL的写法：

WITH full_months AS (
    SELECT generate_series(
        (SELECT MIN(date) FROM sales),
        (SELECT MAX(date) FROM sales),
        '1 month'::interval
    ) AS date
)
SELECT 
    fm.date,
    COALESCE(s.units_sold, 0) AS units_sold,
    AVG(COALESCE(s.units_sold, 0)) OVER (ORDER BY fm.date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 3_month_avg
FROM full_months fm
LEFT JOIN sales s ON fm.date = s.date;

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