如何查询指定ID在给定日期范围内的日期区间间隙？

你的核心问题在于原SQL只检查了查询区间内启动的记录与上一条的间隙，但没覆盖以下关键场景：

• 查询区间的起始日期早于第一条记录的开始日期
• 查询区间完全落在两条记录的间隙之中
• 查询区间的结束日期晚于最后一条记录的结束日期（含END_DATE为NULL的情况）

下面是修正后的SQL，能覆盖所有你提到的间隙场景，最终返回1表示存在间隙，0表示无间隙：

WITH processed_data AS (
    SELECT 
        ID,
        START_DATE,
        -- 将NULL的END_DATE替换为查询结束日期或当前日期（取较小值，避免超出查询范围）
        COALESCE(END_DATE, LEAST(CURRENT_DATE, Z_SECOND_DATE)) AS END_DATE
    FROM TBL
    WHERE ID = X_ID
    -- 筛选和查询区间有重叠的记录：记录的START_DATE <= 查询结束，且处理后的END_DATE >= 查询开始
    AND START_DATE <= Z_SECOND_DATE
    AND COALESCE(END_DATE, CURRENT_DATE) >= Y_FIRST_DATE
    ORDER BY START_DATE
),
gaps_check AS (
    SELECT
        -- 检查第一条记录是否晚于查询起始日期
        CASE WHEN MIN(START_DATE) > Y_FIRST_DATE THEN 1 ELSE 0 END AS start_gap,
        -- 检查连续记录之间的间隙是否与查询区间重叠
        MAX(CASE WHEN LAG(END_DATE) OVER (ORDER BY START_DATE) < START_DATE 
                 AND LAG(END_DATE) OVER (ORDER BY START_DATE) < Z_SECOND_DATE 
                 AND START_DATE > Y_FIRST_DATE 
                 THEN 1 ELSE 0 END) AS middle_gap,
        -- 检查最后一条记录是否早于查询结束日期
        CASE WHEN MAX(END_DATE) < Z_SECOND_DATE THEN 1 ELSE 0 END AS end_gap
    FROM processed_data
)
SELECT 
    CASE WHEN start_gap = 1 OR middle_gap = 1 OR end_gap = 1 THEN 1 ELSE 0 END AS has_gap
FROM gaps_check;

逻辑解释： #

1. processed_data CTE：

   • 先处理END_DATE为NULL的情况，替换为当前日期和查询结束日期的较小值，避免我们关心的范围超出查询区间
   • 筛选出和查询区间有重叠的记录，减少不必要的计算

2. gaps_check CTE：

   • start_gap：如果第一条记录的开始日期晚于查询起始日，说明查询开始到第一条记录之间存在间隙
   • middle_gap：遍历每一条记录，对比它的开始日期和上一条的结束日期，如果两者之间有间隙，且这个间隙和查询区间有重叠（即上一条结束在查询结束前，当前开始在查询起始后），则标记为存在间隙
   • end_gap：如果最后一条记录的结束日期（处理后）早于查询结束日，说明最后一条记录结束到查询结束之间存在间隙

3. 最终判断：只要三个间隙场景中有一个成立，就返回存在间隙

针对你示例的验证： #

• 示例1，查询范围2019-10-02到2020-02-28：middle_gap会检测到第一条记录的END_DATE（2019-09-30）早于第二条的START_DATE（2020-03-01），且这个间隙完全落在查询区间内，返回1
• 示例1，查询范围2019-05-05到2019-09-01：三个检查项都为0，返回0
• 示例1，查询范围2019-05-05到2019-10-02：middle_gap检测到间隙（2019-09-30到2019-10-02重叠），返回1

原SQL的问题分析： #

原SQL只筛选了START_DATE在查询区间内的记录，当查询区间完全落在两条记录的间隙中时，没有符合条件的记录，SUM结果为0，但实际上存在间隙。修正后的SQL通过检查三个关键场景，覆盖了所有可能的间隙情况。

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