SQL如何查找日期并匹配两列中最接近的日期值

需求说明 #

需要为现有数据表新增Closest Date列，对每行的LY Date（即当前Date对应的去年同期日期），匹配全表Date列中与它差值最小的日期值。

实现思路 #

• 对原表做自关联，将每行的LY Date与全表所有Date值做候选匹配
• 计算两个日期的差值绝对值，值越小说明匹配度越高
• 按差值排序后取首位的Date值，即为当前行的Closest Date

通用实现代码 #

以下为兼容多数SQL数据库的写法，可根据实际使用的数据库替换日期差函数：

WITH date_diff_calc AS (
    SELECT
        t1.*,
        t2.Date AS candidate_date,
        -- 计算两个日期的间隔天数绝对值，不同数据库替换为对应日期差函数即可
        ABS(DATEDIFF(t1.`LY Date`, t2.Date)) AS day_diff,
        -- 按差值升序排序，差值相同时优先取更早的日期，可按需调整排序规则
        ROW_NUMBER() OVER (PARTITION BY t1.Date ORDER BY ABS(DATEDIFF(t1.`LY Date`, t2.Date)), t2.Date) AS rn
    FROM 你的表名 t1
    CROSS JOIN 你的表名 t2
)
SELECT
    Date,
    `LY Date`,
    candidate_date AS `Closest Date`
FROM date_diff_calc
WHERE rn = 1;

注意事项 #

• 数据量较大时可优化关联逻辑，比如增加WHERE t2.Date BETWEEN DATE_SUB(t1.LY Date, INTERVAL 30 DAY) AND DATE_ADD(t1.LY Date, INTERVAL 30 DAY)限制候选日期范围，减少计算量
• 如需保留差值相同的多个匹配结果，将ROW_NUMBER函数替换为RANK即可
• 不同数据库日期函数适配：Oracle可直接用t1."LY Date" - t2.Date计算日期间隔，PostgreSQL可使用ABS(EXTRACT(DAY FROM (t1."LY Date" - t2.Date)))

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