基于ID列区分连续与非连续日期范围并筛选全连续ID的SQL实现

需求与原始数据 #

首先明确我们的目标：要找出所有日期范围完全连续的ID——也就是某个ID的每一条记录的STRT_DT必须等于上一条记录的ENT_DT加1天；只要存在任意一段非连续的间隔，这个ID就被排除。

原始数据如下：

能看到ID=1的日期是完全连续的，ID=2在11/5/2020到11/21/2020之间有间隔，所以最终结果只需要保留ID=1。

你的尝试问题分析 #

你之前写的查询用了窗口函数计算日期差，但QUALIFY的条件逻辑搞反了——它会筛选出存在非连续的记录，而我们需要的是完全没有非连续情况的ID，而且也没完成对整个ID的聚合判断，所以达不到预期效果。

正确的SQL实现 #

这里提供两种通用方案，适配不同的SQL引擎：

方案1：GROUP BY + HAVING（通用所有SQL引擎） #

WITH ranked_data AS (
    SELECT 
        ID,
        STRT_DT,
        ENT_DT,
        -- 获取上一条记录的结束日期
        LAG(ENT_DT) OVER (PARTITION BY ID ORDER BY STRT_DT) AS prev_ent_dt
    FROM tabLE
)
SELECT DISTINCT ID
FROM ranked_data
GROUP BY ID
-- 统计当前ID下不符合连续条件的记录数，要求为0
HAVING COUNT(CASE 
               WHEN prev_ent_dt IS NOT NULL 
                    AND STRT_DT <> DATEADD(day, 1, prev_ent_dt) 
               THEN 1 
             END) = 0;

方案2：QUALIFY窗口过滤（适用于Snowflake、BigQuery等支持QUALIFY的引擎） #

如果你的SQL引擎支持QUALIFY（比如Snowflake、BigQuery），可以用更简洁的写法：

WITH ranked_data AS (
    SELECT 
        ID,
        STRT_DT,
        ENT_DT,
        LAG(ENT_DT) OVER (PARTITION BY ID ORDER BY STRT_DT) AS prev_ent_dt
    FROM tabLE
)
SELECT DISTINCT ID
FROM ranked_data
QUALIFY 
    -- 确保当前ID下没有任何一条记录不符合连续条件
    MAX(CASE 
          WHEN prev_ent_dt IS NOT NULL 
               AND STRT_DT <> DATEADD(day, 1, prev_ent_dt) 
          THEN 1 
          ELSE 0 
        END) OVER (PARTITION BY ID) = 0;

注意事项 #

不同SQL引擎的日期函数语法可能略有差异：

• MySQL：用DATE_ADD(prev_ent_dt, INTERVAL 1 DAY)代替DATEADD(day, 1, prev_ent_dt)
• PostgreSQL：用prev_ent_dt + INTERVAL '1 day'
• SQL Server：DATEADD(day, 1, prev_ent_dt)是正确的写法

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