PostgreSQL：窗口函数结合FILTER实现时间滑动求和的问题排查

嗨，我来帮你梳理下你遇到的问题，以及给出正确的解决方案：

先分析你当前语句的问题 #

你尝试用FILTER结合窗口函数的思路没问题，但窗口的定义和FILTER的逻辑搭配错了：

1. 第一个语句Select *, Sum(quantity) filter (where time between time - interval '29 day' and time) over () from t1

   • over ()表示窗口是整个数据集，没有排序或范围限制。
   • FILTER里的time between time - interval '29 day' and time，这里的两个time都是当前行的时间，所以这个条件对每一行来说都是“当前行的时间在自己的29天范围内”，也就是永远为真。最终SUM计算的是全表所有quantity的总和，自然得到全量求和的结果。

2. 第二个语句Select *, Sum(quantity) filter (where time between time - interval '29 day' and time - interval '1 day') over () from t1

   • 同样窗口是整个数据集，FILTER的条件是找全表中时间在当前行前29天到前1天的行。如果当前行是前29天内的记录（比如2020-01-01），全表中没有符合条件的行，就会返回NULL；就算有符合条件的行，也是全表中所有满足该时间范围的行的总和，不是相对于当前行的滑动窗口结果。

正确的解决方案 #

要实现每一行对应前29天的滑动求和，应该直接用窗口函数的框架子句来定义时间范围，这比FILTER更简洁高效：

方法1：使用RANGE INTERVAL（推荐，要求PostgreSQL 11+）

这个方法基于实际日期范围计算，即使有缺失日期也能准确计算：

SELECT
  time,
  sum_quantity,
  -- 计算当前行前29天（不含当天）的总和
  SUM(sum_quantity) OVER (
    ORDER BY time
    RANGE BETWEEN INTERVAL '29 days' PRECEDING AND INTERVAL '1 day' PRECEDING
  ) AS rolling_29d_sum
FROM t1;

• ORDER BY time确保窗口按时间顺序排序，框架范围基于时间轴计算。
• RANGE BETWEEN INTERVAL '29 days' PRECEDING AND INTERVAL '1 day' PRECEDING明确指定了窗口范围：从当前行日期往前推29天，到往前推1天的所有行，正好对应你要的“前29天总和”（比如2020-01-30的行，范围就是2020-01-01到2020-01-29）。
• 如果想让前29天内的行（比如2020-01-01）显示0而不是NULL，可以用COALESCE包裹SUM：

  COALESCE(SUM(sum_quantity) OVER (...), 0) AS rolling_29d_sum
  

方法2：使用ROWS框架（仅适用于无缺失日期的场景）

如果你的数据保证每一天都有且只有一行记录，可以用行数量来定义框架：

SELECT
  time,
  sum_quantity,
  SUM(sum_quantity) OVER (
    ORDER BY time
    ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING
  ) AS rolling_29d_sum
FROM t1;

这个方法按行的位置计算，取当前行之前的29行（不含当前行），但如果有日期缺失，结果会不准确，所以优先用方法1。

补充：为什么FILTER不适合这个场景？ #

FILTER是在已经定义好的窗口范围内过滤行，但你需要的是基于当前行动态调整的窗口范围，直接用窗口框架子句就能精准实现，不需要额外的FILTER。如果硬要结合FILTER，你需要把窗口定义为整个表，然后在FILTER里判断其他行的time是否在当前行的29天范围内，但这样效率极低，远不如直接用框架子句高效。

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