Oracle SQL：按最近时间戳实现Table1与Table2的Left Join

数据表结构与数据 #

table1 #

table2 #

需求 #

将table1与table2做Left Join，关联规则为：同item下，匹配table2中last_dt与table1的end time时间差最小的记录，最终得到如下结果：

解决方案（SQL实现） #

利用窗口函数ROW_NUMBER()对时间差排序，筛选出每个table1记录对应的最接近时间的table2记录：

SELECT 
    t1.item,
    t1.`end time`,
    t2.value
FROM table1 t1
LEFT JOIN (
    SELECT 
        t1_sub.item,
        t1_sub.`end time`,
        t2.value,
        ROW_NUMBER() OVER (
            PARTITION BY t1_sub.item, t1_sub.`end time`
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, t2.last_dt, t1_sub.`end time`)) ASC
        ) AS rn
    FROM table1 t1_sub
    INNER JOIN table2 t2 ON t1_sub.item = t2.item
) t2 ON t1.item = t2.item 
    AND t1.`end time` = t2.`end time`
    AND t2.rn = 1;

逻辑说明 #

1. 子查询中，先将table1的每条记录与同item的table2记录做关联，得到所有可能的组合；
2. 通过ROW_NUMBER()按table1的item和end time分组，对每组内的table2记录按与end time的时间差绝对值从小到大排序，标记排名rn；
3. 外层查询只保留排名为1的记录，即时间差最小的那条table2数据，最终得到符合要求的Left Join结果。

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