PostgreSQL中如何筛选各item_id对应最新run_date的记录

问题场景 #

查询items_run表时，执行以下语句：

SELECT object_id,  
       item_id,
       run_date
FROM items_run
WHERE run_date > '2023-07-30'

得到结果：

object_id  item_id  run_date
    0       1010    8/1/2023
    1       1020    8/3/2023
    2       1030    8/4/2023
    3       1010    8/5/2023
    4       1020    8/6/2023

需要调整查询，获取每个item_id对应最新run_date的行，正确预期结果应为：

object_id  item_id  run_date
    3       1010    8/5/2023
    4       1020    8/6/2023
    2       1030    8/4/2023

你之前尝试的条件WHERE run_date > '2023-07-30' AND run_date = (SELECT MAX(run_date) FROM items_run)返回空表，原因是这个子查询取的是整个表的全局最大run_date，而不是每个item_id各自的最大日期，自然无法匹配到所有item的最新记录。

解决方案 #

方法1：窗口函数（通用推荐）

用ROW_NUMBER()窗口函数按item_id分组，每组内按run_date倒序编号，取编号为1的行就是每组最新记录：

SELECT object_id, item_id, run_date
FROM (
    SELECT 
        object_id, 
        item_id,
        run_date,
        -- 按item_id分组，每组内按run_date倒序排，给行编号
        ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY run_date DESC) AS row_num
    FROM items_run
    WHERE run_date > '2023-07-30'
) AS temp
WHERE row_num = 1;

如果同一个item_id在同一最新日期有多行，需要保留所有同日期行的话，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。

方法2：关联子查询

先通过子查询算出每个item_id符合日期条件的最大run_date，再和原表关联匹配：

SELECT ir.object_id, ir.item_id, ir.run_date
FROM items_run ir
JOIN (
    SELECT item_id, MAX(run_date) AS latest_date
    FROM items_run
    WHERE run_date > '2023-07-30'
    GROUP BY item_id
) AS latest ON ir.item_id = latest.item_id AND ir.run_date = latest.latest_date
WHERE ir.run_date > '2023-07-30';

方法3：简化版（无重复最新日期时可用）

如果每个item_id的最新run_date唯一，可直接用MAX()窗口函数配合DISTINCT：

SELECT DISTINCT
    object_id,
    item_id,
    MAX(run_date) OVER (PARTITION BY item_id) AS run_date
FROM items_run
WHERE run_date > '2023-07-30';

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