Oracle数据库：如何为每个日期获取两条最接近指定时间的记录？

针对你需要为每个日期获取最接近指定两个时间（如8点和20点）记录的需求，可以通过窗口函数+UNION ALL的方式实现，这种方案结构清晰，容易整合到包含多表连接、子查询的复杂业务逻辑中。

实现思路 #

将需求拆分为两个独立的查询单元：

1. 对每个日期，筛选出最接近当天8:00:00的记录
2. 对每个日期，筛选出最接近当天20:00:00的记录
   最后通过UNION ALL合并两个结果集，得到最终数据。

完整SQL代码 #

-- 获取每个日期最接近8点的记录
SELECT time, some_data
FROM (
    SELECT 
        time,
        some_data,
        -- 按日期分组，按与8点的时间差绝对值排序，取第一条
        ROW_NUMBER() OVER (
            PARTITION BY TRUNC(time) 
            ORDER BY ABS(time - (TRUNC(time) + INTERVAL '8' HOUR))
        ) AS rn
    FROM t1
)
WHERE rn = 1

UNION ALL

-- 获取每个日期最接近20点的记录
SELECT time, some_data
FROM (
    SELECT 
        time,
        some_data,
        -- 按日期分组，按与20点的时间差绝对值排序，取第一条
        ROW_NUMBER() OVER (
            PARTITION BY TRUNC(time) 
            ORDER BY ABS(time - (TRUNC(time) + INTERVAL '20' HOUR))
        ) AS rn
    FROM t1
)
WHERE rn = 1

-- 按时间排序结果
ORDER BY time;

适配复杂业务场景 #

如果你的实际业务查询包含多表连接、子查询等逻辑，只需将上述代码中的FROM t1替换为你的业务查询结果即可。例如：

-- 适配复杂多表查询的示例
SELECT time, some_data
FROM (
    SELECT 
        b.time,
        b.some_data,
        ROW_NUMBER() OVER (
            PARTITION BY TRUNC(b.time) 
            ORDER BY ABS(b.time - (TRUNC(b.time) + INTERVAL '8' HOUR))
        ) AS rn
    FROM table_a a
    JOIN table_b b ON a.id = b.a_id
    WHERE a.status = 'ACTIVE'
)
WHERE rn = 1

UNION ALL

SELECT time, some_data
FROM (
    SELECT 
        b.time,
        b.some_data,
        ROW_NUMBER() OVER (
            PARTITION BY TRUNC(b.time) 
            ORDER BY ABS(b.time - (TRUNC(b.time) + INTERVAL '20' HOUR))
        ) AS rn
    FROM table_a a
    JOIN table_b b ON a.id = b.a_id
    WHERE a.status = 'ACTIVE'
)
WHERE rn = 1

ORDER BY time;

结果验证 #

用你提供的示例数据执行上述SQL，将得到期望的结果：

1.1.2024 08:08:08       1
1.1.2024 20:20:20       4
2.1.2024 09:09:09       5
2.1.2024 16:16:16       7

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