如何查询过去12个月平均值？优化月度统计年统计计算方案

嘿，自连接12次确实是个既繁琐又低效的办法——别说是写起来麻烦，数据量上去之后性能会直接崩掉。下面给你几个更优雅的解决方案，适配不同数据库场景：

1. 窗口函数（最推荐，主流数据库通用） #

现在几乎所有现代数据库（MySQL 8+、PostgreSQL、SQL Server、Oracle等）都支持窗口函数，这是处理这类滚动统计的最优解。它能一行代码搞定，性能还拉满。

基础写法（按行数匹配） #

如果你的月度记录是连续无缺失的（每个月都有一条数据），可以直接按行数取前11条加当前行，计算平均值：

SELECT
  date_col,
  value_col,
  -- 计算当前行及往前11行的平均值，正好12个月
  AVG(value_col) OVER (
    ORDER BY date_col
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ) AS rolling_12_month_avg
FROM monthly_stats;

进阶写法（按日期范围匹配） #

如果存在月度数据缺失的情况（比如某个月没统计数据），按行数计算会出错——这时候用日期范围来匹配更准确：

-- PostgreSQL 示例
SELECT
  date_col,
  value_col,
  AVG(value_col) OVER (
    ORDER BY date_col
    RANGE BETWEEN INTERVAL '11 months' PRECEDING AND CURRENT ROW
  ) AS rolling_12_month_avg
FROM monthly_stats;

-- SQL Server 示例
SELECT
  date_col,
  value_col,
  AVG(value_col) OVER (
    ORDER BY date_col
    RANGE BETWEEN DATEADD(month, -11, CURRENT ROW) AND CURRENT ROW
  ) AS rolling_12_month_avg
FROM monthly_stats;

这种写法会自动匹配当前日期往前12个月内的所有记录，不管中间有没有缺失。

2. 子查询聚合（兼容老版本数据库） #

如果你的数据库版本比较老，不支持窗口函数，可以用关联子查询来实现，虽然性能不如窗口函数，但比自连接12次靠谱多了：

SELECT
  m1.date_col,
  m1.value_col,
  -- 子查询筛选当前日期往前11个月到当前的所有记录，计算平均
  (SELECT AVG(m2.value_col)
   FROM monthly_stats m2
   WHERE m2.date_col >= DATEADD(month, -11, m1.date_col)
     AND m2.date_col <= m1.date_col) AS rolling_12_month_avg
FROM monthly_stats m1;

额外注意事项 #

• 日期格式统一：确保你的日期列是标准的月度日期（比如每个月的最后一天2017-12-31或第一天2017-12-01），这样日期计算不会出错。
• 补全缺失数据：如果有月度记录缺失，建议先用CTE生成连续的月度日期表，再和你的统计表左连接补全缺失值（比如补0），这样滚动平均的结果才准确。
• 索引优化：给date_col加索引，能大幅提升窗口函数或子查询的查询速度，尤其是数据量大的时候。

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