PostgreSQL按id分组获取各属性最新非空值构造新表方案

数据老陈

2026-9-26

实现方案（PostgreSQL环境）

核心逻辑为对每个id下的单个属性单独回溯，取时间最近的非空值即可满足需求，两种常用实现方式如下：

方式1：使用`FIRST_VALUE`窗口函数（推荐，PostgreSQL 11+支持）

写法最简洁，利用窗口函数的IGNORE NULLS参数直接跳过空值取最新属性值：

CREATE TABLE 你的新表名 AS
SELECT DISTINCT
    id,
    FIRST_VALUE("attribute-1") IGNORE NULLS OVER (PARTITION BY id ORDER BY "timestamp" DESC) AS attribute_1,
    FIRST_VALUE("attribute-2") IGNORE NULLS OVER (PARTITION BY id ORDER BY "timestamp" DESC) AS attribute_2
FROM 你的原表名;

逻辑说明：

PARTITION BY id 按id分组单独处理每个用户的记录
ORDER BY "timestamp" DESC 按时间倒序排列，保证最新数据排在最前
IGNORE NULLS 跳过属性为空的行，取第一个（即时间最近的）非空值
末尾加DISTINCT去重，每个id仅保留一行结果

方式2：低版本PostgreSQL兼容写法

如果你的PostgreSQL版本低于11不支持IGNORE NULLS，可用排序编号的方式实现：

CREATE TABLE 你的新表名 AS
WITH attr1_rank AS (
    SELECT 
        id,
        "attribute-1" AS val,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY "timestamp" DESC) AS rn
    FROM 你的原表名
    WHERE "attribute-1" IS NOT NULL
),
attr2_rank AS (
    SELECT 
        id,
        "attribute-2" AS val,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY "timestamp" DESC) AS rn
    FROM 你的原表名
    WHERE "attribute-2" IS NOT NULL
)
SELECT 
    a1.id,
    a1.val AS attribute_1,
    a2.val AS attribute_2
FROM attr1_rank a1
JOIN attr2_rank a2 ON a1.id = a2.id
WHERE a1.rn = 1 AND a2.rn = 1;

逻辑说明：