PostgreSQL中基于年月列计算同月份10年移动平均值的技术问询

嘿，我来帮你搞定这个移动平均的计算～你要的是对应月份过去10年的均值，比如1969年1月的均值只包含1960-1969年所有1月的数据，而你原来的SQL没有区分月份，会把不同月份的行都纳入计算，这就错啦。

正确的SQL写法 #

我们需要用PARTITION BY month把每个月份的数据单独分组，然后按年份排序，取当前年份及前9年的同月份数据计算均值：

SELECT 
    year,
    month,
    value,
    -- 核心窗口函数：按月份分区，按年份排序，取当前行及前9行（共10年）的同月份数据
    AVG(value) OVER (
        PARTITION BY month 
        ORDER BY year 
        ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) AS ma10_value
FROM table_name;

为什么原来的SQL不对？ #

你原来的语句AVG(value) OVER(ORDER BY year ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)没有按月份分区，会把当前行之前的9条记录（不管是哪个月份）都算进去。比如计算1969年1月的均值时，会包含1968年12月、11月…一直到1968年4月的数据，这完全不符合你要的「同月份10年均值」的规则。

可选：合并年月为日期列 #

如果需要把year和month合并成标准日期列，可以用make_date()函数，这样后续处理日期更方便：

SELECT 
    make_date(year, month, 1) AS date_column, -- 生成当月第一天的日期
    year,
    month,
    value,
    AVG(value) OVER (
        PARTITION BY month 
        ORDER BY year 
        ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) AS ma10_value
FROM table_name;

补充说明 #

• 如果你的表中每个(year, month)组合是唯一的（没有重复行），上面的语句直接用就行；
• 如果存在重复的年月数据，建议先按年月聚合value（比如取均值或求和），再计算移动平均：

  WITH monthly_agg AS (
      SELECT year, month, AVG(value) AS avg_value
      FROM table_name
      GROUP BY year, month
  )
  SELECT 
      year,
      month,
      avg_value,
      AVG(avg_value) OVER (
          PARTITION BY month 
          ORDER BY year 
          ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
      ) AS ma10_value
  FROM monthly_agg;
  

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