如何按每个device_id匹配最接近日期，左连接event表与incident表

现有SQL存在的问题 #

你编写的子查询语句无法满足需求，核心问题有两个：

1. 加了event_time <= T2.incident_time的限制，只能匹配早于等于事件时间的记录，无法覆盖晚于incident_time但距离更近的记录（比如你示例中C设备2021-01-17的incident对应的最近event是2021-01-18，就会被这个条件过滤掉）
2. 标量子查询只能返回单个字段，无法直接获取event_time、var1、var2等多个需要的字段，扩展性极差

JOIN实现方案 #

通用方案（支持所有带窗口函数的数据库：MySQL8+、PostgreSQL、Spark SQL等） #

用ROW_NUMBER窗口函数按时间差排序取最接近的记录：

SELECT
    t2.device_id,
    t2.incident_time,
    t1.event_id,
    t1.event_time,
    t1.var1,
    t1.var2
FROM T2
LEFT JOIN (
    SELECT
        t1.*,
        t2.device_id AS t2_device_id,
        t2.incident_time,
        -- 按两个时间的绝对差排序，最近的排第1
        ROW_NUMBER() OVER(
            PARTITION BY t1.device_id, t2.incident_time
            ORDER BY ABS(/* 不同数据库替换为对应时间差计算函数 */
                EXTRACT(EPOCH FROM (t1.event_time - t2.incident_time)) -- PostgreSQL写法
                -- TIMESTAMPDIFF(SECOND, t1.event_time, t2.incident_time) -- MySQL写法
            ) ASC
        ) AS rn
    FROM T1
    INNER JOIN T2 ON t1.device_id = t2.device_id
) t1 ON t2.device_id = t1.t2_device_id 
    AND t2.incident_time = t1.incident_time 
    AND t1.rn = 1;

PostgreSQL专属简化方案 #

用PostgreSQL特有的DISTINCT ON语法可以更简洁实现：

SELECT DISTINCT ON (t2.device_id, t2.incident_time)
    t2.device_id,
    t2.incident_time,
    t1.event_id,
    t1.event_time,
    t1.var1,
    t1.var2
FROM T2
LEFT JOIN T1 ON t2.device_id = t1.device_id
ORDER BY 
    t2.device_id, 
    t2.incident_time,
    ABS(EXTRACT(EPOCH FROM (t1.event_time - t2.incident_time))) ASC;

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