如何在PostgreSQL窗口中计算多个数组的并集？

你可以通过以下方式创建所需视图，同时获取每个学生的最新字段值和所有被修改过的列的集合：

方法一（PostgreSQL 11+ 适用，支持IGNORE NULLS） #

CREATE VIEW student_current_state AS
WITH latest_values AS (
  SELECT DISTINCT
    student_id,
    -- 获取每个学生最后一次修改的first_name（忽略NULL值）
    LAST_VALUE(first_name) OVER (
      PARTITION BY student_id 
      ORDER BY inserted_at 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) IGNORE NULLS AS first_name,
    -- 获取每个学生最后一次修改的last_name
    LAST_VALUE(last_name) OVER (
      PARTITION BY student_id 
      ORDER BY inserted_at 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) IGNORE NULLS AS last_name,
    -- 获取每个学生最后一次修改的birth_year
    LAST_VALUE(birth_year) OVER (
      PARTITION BY student_id 
      ORDER BY inserted_at 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) IGNORE NULLS AS birth_year
  FROM student_changes
),
all_modified_columns AS (
  -- 合并所有变更记录中的列名，去重后生成数组
  SELECT
    student_id,
    array_agg(DISTINCT change ORDER BY change) AS all_changes
  FROM student_changes, unnest(changes) AS change
  GROUP BY student_id
)
SELECT
  lv.student_id,
  lv.first_name,
  lv.last_name,
  lv.birth_year,
  amc.all_changes
FROM latest_values lv
JOIN all_modified_columns amc ON lv.student_id = amc.student_id;

方法二（兼容旧版PostgreSQL，无需IGNORE NULLS） #

如果你的PostgreSQL版本低于11，可以用以下替代方案：

CREATE VIEW student_current_state AS
WITH field_update_timestamps AS (
  -- 计算每个学生各字段的最后修改时间
  SELECT
    student_id,
    MAX(CASE WHEN 'first_name' = ANY(changes) THEN inserted_at END) AS first_name_last_updated,
    MAX(CASE WHEN 'last_name' = ANY(changes) THEN inserted_at END) AS last_name_last_updated,
    MAX(CASE WHEN 'birth_year' = ANY(changes) THEN inserted_at END) AS birth_year_last_updated
  FROM student_changes
  GROUP BY student_id
),
latest_values AS (
  -- 根据最后修改时间获取对应字段的最新值
  SELECT
    fut.student_id,
    sc_first.first_name,
    sc_last.last_name,
    sc_birth.birth_year
  FROM field_update_timestamps fut
  LEFT JOIN student_changes sc_first 
    ON fut.student_id = sc_first.student_id 
    AND fut.first_name_last_updated = sc_first.inserted_at
  LEFT JOIN student_changes sc_last 
    ON fut.student_id = sc_last.student_id 
    AND fut.last_name_last_updated = sc_last.inserted_at
  LEFT JOIN student_changes sc_birth 
    ON fut.student_id = sc_birth.student_id 
    AND fut.birth_year_last_updated = sc_birth.inserted_at
),
all_modified_columns AS (
  -- 合并所有变更记录中的列名，去重后生成数组
  SELECT
    student_id,
    array_agg(DISTINCT change ORDER BY change) AS all_changes
  FROM student_changes, unnest(changes) AS change
  GROUP BY student_id
)
SELECT
  lv.student_id,
  lv.first_name,
  lv.last_name,
  lv.birth_year,
  amc.all_changes
FROM latest_values lv
JOIN all_modified_columns amc ON lv.student_id = amc.student_id;

关键部分说明 #

1. 获取最新字段值：

   • 方法一使用LAST_VALUE窗口函数结合IGNORE NULLS，直接取每个学生分区内最后一个非NULL的字段值，按修改时间排序。
   • 方法二先计算每个字段的最后修改时间，再关联回原表获取对应时间点的字段值，兼容不支持IGNORE NULLS的旧版本。

2. 生成所有修改过的列集合：

   • 通过unnest(changes)将每个变更记录的列名数组拆分为单行数据，再用array_agg(DISTINCT ...)去重并重新组合为数组，得到每个学生所有被修改过的列名集合。

示例输出 #

针对你提供的样本数据，视图会返回：

student_id | first_name | last_name | birth_year |        all_changes
------------+------------+-----------+------------+------------------------
          1 | Bobby      | De Niro   |       1943 | {first_name,last_name,birth_year}
          2 |            |           |       1945 | {birth_year}

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