如何用PostgreSQL查询获取同一ID的最新列值合并单行数据？

原始表数据 #

期望结果 #

可以通过PostgreSQL查询实现需求，以下提供两种适配不同版本的方案：

方案一：PostgreSQL 13+ 版本（推荐） #

利用PostgreSQL 13新增的IGNORE NULLS窗口函数特性，直接获取每个字段最后一次更新的非空值：

SELECT DISTINCT
    id,
    MAX(time) OVER (PARTITION BY id) AS time,
    LAST_VALUE(firstname) OVER (
        PARTITION BY id 
        ORDER BY time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
        IGNORE NULLS
    ) AS firstname,
    LAST_VALUE(lastname) OVER (
        PARTITION BY id 
        ORDER BY time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
        IGNORE NULLS
    ) AS lastname,
    LAST_VALUE(salary) OVER (
        PARTITION BY id 
        ORDER BY time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
        IGNORE NULLS
    ) AS salary,
    LAST_VALUE(location) OVER (
        PARTITION BY id 
        ORDER BY time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
        IGNORE NULLS
    ) AS location,
    LAST_VALUE(country) OVER (
        PARTITION BY id 
        ORDER BY time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
        IGNORE NULLS
    ) AS country
FROM your_table;

逻辑说明 #

• PARTITION BY id：按用户ID分组，单独处理每个用户的更新记录
• ORDER BY time：确保按时间顺序遍历记录，最新的更新排在最后
• LAST_VALUE(...) IGNORE NULLS：跳过字段为空的记录，直接取该字段最后一次更新的非空值
• MAX(time) OVER (PARTITION BY id)：获取当前用户的最新更新时间戳
• DISTINCT：窗口函数会为每一行生成结果，去重后得到每个用户的唯一聚合行

方案二：PostgreSQL 12及以下版本 #

对于不支持IGNORE NULLS的旧版本，使用ARRAY_AGG收集非空值并取最新的条目：

SELECT
    id,
    MAX(time) AS time,
    (ARRAY_AGG(firstname) FILTER (WHERE firstname IS NOT NULL) ORDER BY time DESC)[1] AS firstname,
    (ARRAY_AGG(lastname) FILTER (WHERE lastname IS NOT NULL) ORDER BY time DESC)[1] AS lastname,
    (ARRAY_AGG(salary) FILTER (WHERE salary IS NOT NULL) ORDER BY time DESC)[1] AS salary,
    (ARRAY_AGG(location) FILTER (WHERE location IS NOT NULL) ORDER BY time DESC)[1] AS location,
    (ARRAY_AGG(country) FILTER (WHERE country IS NOT NULL) ORDER BY time DESC)[1] AS country
FROM your_table
GROUP BY id;

逻辑说明 #

• ARRAY_AGG(...) FILTER (WHERE ... IS NOT NULL) ORDER BY time DESC：按时间倒序收集该字段的所有非空值，最新的更新排在数组首位
• [1]：提取数组第一个元素，即该字段的最新非空值
• MAX(time)：获取当前用户的最新更新时间戳
• GROUP BY id：按用户ID聚合，得到每个用户的唯一结果行

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