PostgreSQL 分组取最新记录后应用WHERE条件的查询实现

错误原因 #

之前写法的核心问题是执行顺序错误：在窗口函数row_number()计算分组行号前，就提前添加了next_clean <= now()的过滤条件，导致窗口函数只能在「已经满足时间条件」的记录范围内排序取最新，同car_id下真正的最新记录如果不满足时间条件会被提前排除，旧的符合条件的记录会被误判为最新值返回，典型异常场景就是car_id=372的最新记录id=2df因next_clean在未来被提前过滤后，旧记录id=435被错误返回。

正确执行逻辑 #

严格按照需求拆分执行步骤，不能打乱顺序：

• 第一步：对car_wash全表记录做窗口分组，按car_id分区、created_on时间戳倒序排序，给每条记录标记分组内的行号，行号为1的即为对应car_id的最新记录，此阶段不添加任何业务过滤条件
• 第二步：从标记完成的结果中，筛出所有行号为1的记录，即得到每个car_id对应的唯一最新记录
• 第三步：对这批最新记录应用过滤规则，仅保留next_clean <= now()的记录，若某car_id的最新记录不满足该条件，直接排除该car_id的所有结果
• 按规则要求，end_at字段不参与筛选判断，无需追加相关条件

可直接运行的正确SQL #

SELECT
    id,
    car_id,
    created_on,
    next_clean
    -- 其余需要返回的字段可在此处列出
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY car_id 
            ORDER BY created_on DESC
        ) AS row_rank
    FROM car_wash
) ranked_record
WHERE row_rank = 1
  AND next_clean <= NOW();

结果校验 #

执行上述SQL后，car_id=372会因为其最新记录id=2df的next_clean晚于当前时间被整体排除，其余car_id均返回各自满足时间条件的最新记录，完全符合预期。

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