Oracle中按日期获取每日最新timestamp对应记录的SQL问题

需求说明 #

需要从数据中检索出每日最新timestamp对应的记录，示例数据如下：

id    type     timestamp           , etc
1      a       07/10/2022 12:54:59
2      a       07/10/2022 12:50:59
3      b       05/10/2022 12:54:59
4      c       05/10/2022 10:54:59
5      d       01/09/2022 12:54:59
6      c       01/09/2022 12:54:50

预期结果：

id    type     timestamp           , etc
1      a       07/10/2022 12:54:59
3      b       05/10/2022 12:54:59
5      d       01/09/2022 12:54:59

现有SQL的问题 #

你当前的SQL仅完成了表关联、条件过滤和排序，没有实现筛选每日最新记录的核心逻辑，同时存在两个可优化点：

• 用to_char(p.TIMESTAMP, 'DD/MM/YYYY')做日期范围查询，会导致timestamp字段的索引失效，查询性能低下，应该直接用日期类型做比较
• 仅靠order by p.id desc无法确保拿到当日最新记录，必须结合分组+取最大值的逻辑

修正后的SQL方案 #

可以用窗口函数ROW_NUMBER()实现需求，按日期分组后，每组内按timestamp降序排序，取行号为1的记录：

SELECT code, time_stamp, TYPE1
FROM (
    SELECT 
        c.code, 
        TO_CHAR(p.TIMESTAMP, 'DD/MM/YYYY HH24:MI:ss') AS time_stamp, 
        p.TYPE1,
        -- 按日期分组，每组内按timestamp降序标记行号
        ROW_NUMBER() OVER (
            PARTITION BY TRUNC(p.TIMESTAMP) 
            ORDER BY p.TIMESTAMP DESC
        ) AS rn
    FROM table1 p
    INNER JOIN table2 c ON c.id3 = p.id2
    WHERE p.id1 = 1234
      AND p.id2 = 1
      AND p.type1 = 'X'
      AND c.CODE = 'XYZ'
      -- 直接用日期类型做范围查询，避免索引失效
      AND p.TIMESTAMP BETWEEN TO_DATE('01/05/2011', 'DD/MM/YYYY') 
                          AND TO_DATE('31/05/2011', 'DD/MM/YYYY') + INTERVAL '23:59:59' HOUR TO SECOND
) t
WHERE rn = 1 -- 只保留每组的第一条（当日最新记录）
ORDER BY code, time_stamp DESC;

关键说明 #

• TRUNC(p.TIMESTAMP)：将timestamp截断到日期部分，实现按日分组
• ROW_NUMBER() OVER (...)：给每个日期组内的记录按timestamp降序编号，最新的记录行号为1
• 日期范围查询改用TO_DATE转换字符串为日期类型，同时将结束日期调整为当月最后一天的23:59:59，避免漏掉当天最后时刻的记录

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