PostgreSQL查询：按ID分组选取不同日期的最新两条记录

测试数据 #

现有测试表数据如下：

id   audit_id   val     updated_at
1    11         43      October 09, 2021, 07:55 AM
1    12         34      October 11, 2021, 11:03 PM
1    13         88      January 23, 2022, 01:03 AM
1    14         34      January 23, 2022, 09:41 AM
2    21         200     June 28, 2021, 08:07 PM
2    22         200     December 23, 2021, 03:20 PM
2    23         205     January 12, 2022, 10:15 AM
2    24         211     May 13, 2022, 04:02 AM

需求规则 #

• 按id字段分组，返回每个id对应的2条最新条目
• 同日期（仅取updated_at的日期部分，忽略时分秒）的记录需去重，仅保留当天时间最晚的1条
• 最终期望返回结果：

id   audit_id   val     updated_at
1    12         34      October 11, 2021, 11:03 PM
1    14         34      January 23, 2022, 09:41 AM
2    23         205     January 12, 2022, 10:15 AM
2    24         211     May 13, 2022, 04:02 AM

实现方案 #

你提到的分区窗口函数思路是正确的，不需要用到lag函数，分两层窗口排序即可完成需求：

1. 第一层排序处理同日期去重：按id + updated_at的日期值分区，同分区内按完整时间戳倒序排名，只保留排名为1的记录，即每个id下每个日期仅留最新的1条
2. 第二层排序处理取数逻辑：对去重后的记录按id分区，按完整时间戳倒序排名，每个分区取排名≤2的记录，就是每个id下最新的2条不同日期的记录

以MySQL 8.0/PostgreSQL为例，可直接执行的SQL如下：

WITH date_dedup AS (
    SELECT
        id,
        audit_id,
        val,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY id, DATE(updated_at)
            ORDER BY updated_at DESC
        ) AS rn
    FROM test_table
),
latest_rank AS (
    SELECT
        id,
        audit_id,
        val,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY id
            ORDER BY updated_at DESC
        ) AS rn
    FROM date_dedup
    WHERE rn = 1
)
SELECT id, audit_id, val, updated_at
FROM latest_rank
WHERE rn <= 2
ORDER BY id, updated_at;

适配说明：不同数据库取日期部分的函数存在差异，Oracle可替换为TRUNC(updated_at)，SQL Server可替换为CAST(updated_at AS DATE)，其余逻辑不变。

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