PostgreSQL合并同一contract_id的documents行 按id关联取最新非空值

实现方案 #

你可以先通过CTE查询出每个contract_id下a、b、c三个字段最新非空值对应的行主键id，再通过主键关联取对应字段值即可，完全符合你用timestamp判定优先级、用id关联的要求：

WITH field_latest_ids AS (
    SELECT
        contract_id,
        -- 取a字段非空的行里时间戳最大的对应的id
        (array_agg(id ORDER BY timestamp DESC) FILTER (WHERE a IS NOT NULL))[1] AS a_row_id,
        -- 取b字段非空的行里时间戳最大的对应的id
        (array_agg(id ORDER BY timestamp DESC) FILTER (WHERE b IS NOT NULL))[1] AS b_row_id,
        -- 取c字段非空的行里时间戳最大的对应的id
        (array_agg(id ORDER BY timestamp DESC) FILTER (WHERE c IS NOT NULL))[1] AS c_row_id
    FROM documents
    GROUP BY contract_id
)
SELECT
    fl.contract_id,
    doc_a.a,
    doc_b.b,
    doc_c.c
FROM field_latest_ids fl
-- 三次关联分别取对应字段值，关联条件用主键id，避免时间戳重复冲突
LEFT JOIN documents doc_a ON doc_a.id = fl.a_row_id
LEFT JOIN documents doc_b ON doc_b.id = fl.b_row_id
LEFT JOIN documents doc_c ON doc_c.id = fl.c_row_id;

说明

• 上述SQL针对你给出的示例数据可以直接输出预期结果
• 关联逻辑完全基于主键id，不会出现同时间戳多数据行的异常问题
• 如果后续需要新增合并字段，只需在CTE中新增对应字段的row_id查询，再新增一次关联即可

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