PostgreSQL查询：获取最新未处理版本及关联最新已处理版本记录

之前用lag/lead不好写筛选条件的核心原因是这类偏移函数只能取相邻行的值，没法直接标记「从最高版本开始遍历到第一条Processed」的截断边界，换用带累计计算逻辑的窗口函数即可实现需求。

查询语句 #

假设目标表名为test_data，可直接运行如下SQL：

WITH marked AS (
    SELECT
        "Status",
        "Version",
        "id",
        -- 同id分组按版本降序遍历时，累计判断截至当前行是否出现过Processed状态
        BOOL_OR("Status" = 'Processed') OVER (
            PARTITION BY "id"
            ORDER BY "Version" DESC
            ROWS UNBOUNDED PRECEDING
        ) AS hit_processed,
        -- 标记同id下版本最高的第一条Processed记录
        ROW_NUMBER() OVER (
            PARTITION BY "id", "Status"
            ORDER BY "Version" DESC
        ) AS rn
    FROM test_data
)
SELECT "Status", "Version", "id"
FROM marked
WHERE
    -- 截至当前行还没碰到Processed，属于要保留的高版本记录
    NOT hit_processed
    -- 命中第一条Processed记录，保留后截断后续低版本记录
    OR ("Status" = 'Processed' AND rn = 1)
ORDER BY "id", "Version" DESC;

逻辑解释 #

• 用CTE先给所有行打标记：hit_processed表示从同id最高版本往下遍历到当前行时，是否已经碰到过Processed状态的记录；rn给同id下同状态的记录按版本降序编号，Processed状态下rn=1的就是遍历到的第一条Processed，也就是截断点。
• 筛选规则只保留两类行：遍历过程中还没碰到Processed的高版本记录、以及第一条碰到的Processed记录本身。
• 若某个id分组下完全没有Processed记录，所有行的hit_processed均为false，会返回该分组下全部记录，符合需求。

执行结果 #

基于给出的样例数据，执行后输出和预期完全匹配：

Status       Version  id
Unprocessed  6        abc
Processed    5        abc
Unprocessed  4        abc1
Processed    3        abc1
Unprocessed  2        abc2
Unprocessed  1        abc2

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