PostgreSQL 9.6中如何创建10天周期的滚动array_agg列

嘿，我来帮你搞定这个滚动聚合数组的问题！你之前的尝试只返回单个值，大概率是窗口函数的范围设置不对，或者没正确指定基于日期的窗口边界。下面一步步给你讲清楚怎么实现：

1. 先搞对滚动聚合的查询逻辑 #

首先，我们需要用array_agg()结合窗口函数，指定未来10天的窗口范围。因为是基于日期的滚动窗口，得用RANGE而不是ROWS（ROWS是按行计数，不适合日期间隔的场景）。假设你的表有date_col（日期/时间类型）和item_id（整数类型），正确的查询应该是这样：

SELECT
  date_col,
  item_id,
  -- 核心：指定窗口为当前行到未来10天的所有行
  array_agg(item_id) OVER (
    ORDER BY date_col
    RANGE BETWEEN CURRENT ROW AND INTERVAL '10 days' FOLLOWING
  ) AS next_10_days_items
FROM your_table_name;

这个查询会为每一行生成一个数组，包含当前行日期及之后10天内所有行的item_id。

2. 将聚合结果作为表的列持久化 #

PostgreSQL 9.6还不支持生成列（Generated Columns），所以我们需要手动添加列并填充数据，之后如果数据有更新，还可以用触发器维护。

步骤1：添加数组类型的列 #

ALTER TABLE your_table_name ADD COLUMN next_10_days_items integer[];

步骤2：用窗口函数更新列值 #

如果你的表有主键（比如id），用主键定位行更可靠：

WITH rolling_agg AS (
  SELECT
    id,
    array_agg(item_id) OVER (
      ORDER BY date_col
      RANGE BETWEEN CURRENT ROW AND INTERVAL '10 days' FOLLOWING
    ) AS agg_items
  FROM your_table_name
)
UPDATE your_table_name t
SET next_10_days_items = r.agg_items
FROM rolling_agg r
WHERE t.id = r.id;

如果没有主键，可以临时用ctid（PostgreSQL内部的行标识符）来定位：

WITH rolling_agg AS (
  SELECT
    ctid,
    array_agg(item_id) OVER (
      ORDER BY date_col
      RANGE BETWEEN CURRENT ROW AND INTERVAL '10 days' FOLLOWING
    ) AS agg_items
  FROM your_table_name
)
UPDATE your_table_name t
SET next_10_days_items = r.agg_items
FROM rolling_agg r
WHERE t.ctid = r.ctid;

3. 维护列的实时更新（可选） #

如果你的表数据会频繁插入、更新或删除，上面的静态更新就不够了，需要创建触发器来自动维护这个列：

第一步：创建触发器函数 #

CREATE OR REPLACE FUNCTION update_next_10_days_items()
RETURNS TRIGGER AS $$
BEGIN
  -- 当数据变化时，更新受影响的行（当前行前后10天的所有行）
  UPDATE your_table_name
  SET next_10_days_items = (
    SELECT array_agg(item_id) OVER (
      ORDER BY date_col
      RANGE BETWEEN CURRENT ROW AND INTERVAL '10 days' FOLLOWING
    )
    FROM your_table_name t
    WHERE t.id = your_table_name.id
  )
  WHERE date_col BETWEEN NEW.date_col - INTERVAL '10 days' AND NEW.date_col + INTERVAL '10 days';
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

第二步：绑定触发器到表 #

CREATE TRIGGER trigger_update_next_10_days_items
AFTER INSERT OR UPDATE OR DELETE ON your_table_name
FOR EACH ROW EXECUTE PROCEDURE update_next_10_days_items();

为什么之前的尝试只返回单个值？ #

大概率是这两个原因：

• 没有指定正确的窗口范围：默认窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，只会聚合到当前行，所以数组里只有当前的item_id；
• 用了ROWS代替RANGE：如果日期不是连续的，ROWS会按行数计数，而不是日期间隔，导致范围不对。

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