PostgreSQL中分组内按优先级和日期取Top1数据的SQL实现

需求回顾 #

按fk字段分组，每组选取优先级最高（值为1时优先级最高）的记录；若优先级相同，则选取日期最新的记录。

原始表结构与数据 #

fk | priority | date
1       1       4-1-2023
1       1       4-3-2023
1       2       4-5-2023
2       3       4-1-2023
2       2       4-2-2023
3       3       4-6-2023

期望查询结果 #

fk | priority | date
1       1       4-3-2023
2       2       4-2-2023
3       3       4-6-2023

解决方案1：使用窗口函数ROW_NUMBER() #

这是处理分组筛选问题的通用方案，逻辑清晰易维护：

SELECT fk, priority, date
FROM (
    SELECT 
        fk, 
        priority, 
        date,
        -- 按fk分组，先按优先级升序（值越小优先级越高）、再按日期降序（最新在前）排序，给每条记录编号
        ROW_NUMBER() OVER (
            PARTITION BY fk 
            ORDER BY priority ASC, date DESC
        ) AS rn
    FROM your_table_name -- 替换为你的实际表名
) ranked
WHERE rn = 1; -- 保留每组编号为1的最优记录

逻辑说明

• PARTITION BY fk：将数据按fk拆分为独立分组，每组单独计算排序编号
• ORDER BY priority ASC, date DESC：先确保优先级最高的记录排在组内最前，同优先级下最新日期的记录靠前
• ROW_NUMBER()：为每组内的记录分配唯一编号，组内第一条（最优）记录编号为1，外层查询筛选该编号即可得到目标结果

解决方案2：使用PostgreSQL专属DISTINCT ON语法 #

PostgreSQL提供了更简洁的语法实现需求：

SELECT DISTINCT ON (fk) fk, priority, date
FROM your_table_name -- 替换为你的实际表名
ORDER BY fk, priority ASC, date DESC;

逻辑说明

• DISTINCT ON (fk)：强制每个fk只返回一条记录
• ORDER BY子句必须以fk开头，后续的priority ASC和date DESC用于指定每组选取哪条记录，确保优先级最高、日期最新的记录被选中

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