SQL技术问询：如何获取目标日期前有记录的最近日期并计算差值

我来帮你搞定这个日期间隙的难题！你原来的SQL依赖日期连续，一旦哪天没有数据，就找不到匹配的前序记录，导致差值出问题。下面给你两种适配不同数据库场景的解决方案：

核心思路 #

针对每个id，我们需要精准找到其目标日期之前最近的有数据记录的日期对应的value，而不是固定找前一天。这里优先推荐用窗口函数，简洁又高效；如果你的数据库版本较低，也可以用自关联+子查询的方式实现。

适用于支持标准窗口函数的数据库（SQL Server、PostgreSQL、MySQL 8.0+） #

直接用LAG()窗口函数就能轻松解决，语法简洁性能还好：

SELECT 
    date,
    id,
    (value - LAG(value) OVER (PARTITION BY id ORDER BY date)) AS 'difference-from-previous'
FROM t1
WHERE date = '2/28/2019' -- 指定你要查询的目标日期
ORDER BY id;

代码说明： #

• PARTITION BY id：把数据按id分组，确保只在同一个id的范围内找前序记录
• ORDER BY date：对每个分组内的记录按日期排序，保证取到的是最近的前一条有效记录
• LAG(value)：获取当前记录的上一条（按排序规则）的value值，直接计算差值即可

运行后会得到你想要的结果：

如果需要查看所有日期的差值，去掉WHERE date = '2/28/2019'条件就行。

适用于不支持窗口函数的老版本数据库（如MySQL < 8.0） #

要是你的数据库版本较低，不支持窗口函数，可以用自关联+子查询的方式实现：

SELECT 
    a.date,
    a.id,
    (a.value - b.value) AS 'difference-from-previous'
FROM t1 a
LEFT JOIN t1 b 
    ON a.id = b.id 
    AND b.date = (
        -- 子查询找到当前id下，比目标日期小的最大日期（即最近的有数据的日期）
        SELECT MAX(date) 
        FROM t1 
        WHERE id = a.id AND date < a.date
    )
WHERE a.date = '2/28/2019'
ORDER BY a.id;

代码说明： #

• 子查询SELECT MAX(date) FROM t1 WHERE id = a.id AND date < a.date：精准定位每个id在目标日期之前最近的有效记录日期
• LEFT JOIN确保即使某个id在目标日期之前没有记录，结果也会保留（差值为NULL，符合逻辑）

为什么原来的SQL会失效？ #

你原来的INNER JOIN搭配dateadd(day, -1, a.DATE)的逻辑，只能匹配到目标日期前一天恰好有记录的情况。一旦日期有间隙（比如周末无数据），b.DATE就找不到匹配值，导致这条记录被过滤或者差值为NULL，而上面的两种方法都能完美规避这个问题。

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