PostgreSQL中如何筛选每个关键词的最新更新记录？

需求：从ledger表中查询每个关键词对应的最新更新记录，每个关键词仅返回一条（保留updatedAt最晚的那条），并包含表的全部字段。

以下是几种实用的实现方式：

方法一：窗口函数（推荐） #

利用ROW_NUMBER()窗口函数分组排序，精准筛选每组的最新记录：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY keyword ORDER BY updatedAt DESC) AS row_num
    FROM ledger
) ranked_ledger
WHERE row_num = 1;

解析：

• PARTITION BY keyword：将数据按关键词分组
• ORDER BY updatedAt DESC：每组内按更新时间倒序排列，最新记录排在首位
• 外层筛选row_num = 1，得到每个关键词的唯一最新记录

方法二：关联子查询 #

先通过子查询获取每个关键词的最新更新时间，再关联原表拿到完整数据：

SELECT l.*
FROM ledger l
JOIN (
    SELECT keyword, MAX(updatedAt) AS latest_updated
    FROM ledger
    GROUP BY keyword
) latest_times 
    ON l.keyword = latest_times.keyword 
    AND l.updatedAt = latest_times.latest_updated;

注意：如果同一关键词存在多条更新时间完全相同的最新记录，此方法会返回所有这些记录；若需强制每个关键词仅一条，建议用窗口函数或DISTINCT ON。

方法三：PostgreSQL特有DISTINCT ON语法 #

这是PostgreSQL专为这类场景设计的简洁写法，代码更紧凑：

SELECT DISTINCT ON (keyword) *
FROM ledger
ORDER BY keyword, updatedAt DESC;

解析：

• DISTINCT ON (keyword)：按关键词去重，保留每组的第一条记录
• ORDER BY子句必须先写分组字段keyword，再按updatedAt DESC排序，确保每组取到最新的那条记录

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