仅含单个日期列与重复条件的SQL孤岛（gaps and islands）问题求解

问题原因 #

你之前的代码将NoLinkingReason放入PARTITION BY子句，会提前将不同原因的行拆分到独立分区，无法识别相邻不同原因的行之间的日期间隔，导致孤岛计算错误。

正确实现SQL #

WITH MarkIslandStarts AS (
    SELECT 
        ChartShiftWeekdayDetailId,
        DateValue,
        NoLinkingReason,
        -- 判定是否为新孤岛起点：首行/原因变化/日期间隔大于1天
        CASE WHEN 
            LAG(DateValue, 1) OVER (PARTITION BY ChartShiftWeekdayDetailId ORDER BY DateValue) IS NULL
            OR LAG(NoLinkingReason, 1) OVER (PARTITION BY ChartShiftWeekdayDetailId ORDER BY DateValue) <> NoLinkingReason
            OR DATEDIFF(DAY, LAG(DateValue, 1) OVER (PARTITION BY ChartShiftWeekdayDetailId ORDER BY DateValue), DateValue) > 1
            THEN 1 ELSE 0 END AS is_new_island
    FROM ClearedData
),
GenerateIslandId AS (
    SELECT 
        *,
        -- 累加起点标记，生成每个孤岛的唯一ID
        SUM(is_new_island) OVER (
            PARTITION BY ChartShiftWeekdayDetailId 
            ORDER BY DateValue 
            ROWS UNBOUNDED PRECEDING
        ) AS island_id
    FROM MarkIslandStarts
)
SELECT 
    ChartShiftWeekdayDetailId,
    MIN(DateValue) AS IslandStartsOn,
    MAX(DateValue) AS IslandEndsOn,
    MAX(NoLinkingReason) AS NoLinkingReason
FROM GenerateIslandId
GROUP BY ChartShiftWeekdayDetailId, island_id
ORDER BY ChartShiftWeekdayDetailId, IslandStartsOn;

逻辑说明 #

1. 第一层CTE仅按ChartShiftWeekdayDetailId分区，按日期排序后标记所有新孤岛的起点
2. 第二层CTE对起点标记做累加运算，同一个孤岛的所有行会得到相同的island_id
3. 最后按主ID和孤岛ID分组，聚合得到每个孤岛的起止日期与对应原因

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