SSMS 2018：日期间隙检测与Gap_Days计算异常求助

问题 #

我查阅了多篇关于日期间隙检测的文章，认为自己已接近解决问题，但还需些许帮助。我的查询语句提取了不同日期并统计每日记录数，新增了“Gap_Days”列，该列应在与上一日期无间隙时返回0，否则返回与上一日期的间隔天数。但目前所有Gap_Days均为0，而实际上缺失了10月24日和10月25日，因此10月26日的Gap_Days应为2（上一日期是10月23日）。

附上我的SQL语句：

SELECT DISTINCT Run_Date, COUNT(Run_Date) AS Daily_Count,
        Gap_Days = Coalesce(DateDiff(Day,Lag(Run_Date) Over (partition by Run_Date order by Run_Date DESC), Run_Date)-1,0)
FROM tblUnitsOfWork
WHERE (Run_Date >= '2022-10-01')
GROUP BY Run_Date
ORDER BY Run_Date DESC;

查询结果：

Run_Date                Daily_Count Gap_Days
2022-10-29 00:00:00.000 8431        0
2022-10-28 00:00:00.000 8204        0
2022-10-27 00:00:00.000 8705        0
2022-10-26 00:00:00.000 7885        0
2022-10-23 00:00:00.000 7485        0
2022-10-22 00:00:00.000 8699        0
2022-10-21 00:00:00.000 9212        0
2022-10-20 00:00:00.000 9220        0

解决方法 #

问题出在两个地方：

1. partition by Run_Date完全多余：你已经按Run_Date分组，每个分组里只有唯一的一个日期，Lag(Run_Date)取到的就是当前行的日期，差值自然为0。
2. 窗口函数内的order by Run_Date DESC逻辑错误：降序排列会让Lag取到排序后下一行的日期（更晚的日期），而非上一个更早的日期。

修正后的SQL：

SELECT 
    Run_Date, 
    COUNT(Run_Date) AS Daily_Count,
    Gap_Days = COALESCE(DATEDIFF(Day, LAG(Run_Date) OVER (ORDER BY Run_Date ASC), Run_Date) - 1, 0)
FROM tblUnitsOfWork
WHERE Run_Date >= '2022-10-01'
GROUP BY Run_Date
ORDER BY Run_Date DESC;

逻辑说明： #

• 移除partition by Run_Date后，窗口函数会在所有分组后的日期范围内，找到当前日期的上一个更早日期
• DATEDIFF(Day, 上一日期, 当前日期)得到两个日期的天数差：连续日期差值为1，减1后返回0；间隔N天的日期差值为N+1，减1后正好是缺失的天数（比如23日到26日差值为3，3-1=2，符合需求）

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