SQL实现计算指定窗口期内符合条件的连续Alert预警天数

实现思路 #

• 第一步：去重每日预警状态：由于同一ID同一AlertDate可能存在多条记录，只要当日存在1条Alert='Y'就判定当日为预警状态，先按ID和AlertDate聚合去重
• 第二步：过滤有效时间范围：关联t1和预处理后的t2，排除AlertDate晚于对应SpecDate的所有记录
• 第三步：孤岛法识别连续预警序列：对每个ID下的预警日期按升序排序，通过AlertDate - 排序序号生成连续序列的分组标识，相同标识的日期属于同一个连续预警区间
• 第四步：筛选符合要求的连续序列：只保留至少有一个日期落在[SpecDate-2天, SpecDate]区间内的连续序列，统计序列的天数即可得到结果

完整SQL代码 #

WITH DailyAlertStatus AS (
    -- 预处理：按天去重，只要当天有Y就算当天预警
    SELECT ID, AlertDate, MAX(Alert) AS DailyAlert
    FROM #t2
    GROUP BY ID, AlertDate
),
ContinuousGroup AS (
    SELECT 
        t1.ID,
        t1.SpecDate,
        das.AlertDate,
        -- 生成连续序列分组标识
        DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY t1.ID, t1.SpecDate ORDER BY das.AlertDate), das.AlertDate) AS GroupID
    FROM #t1 t1
    INNER JOIN DailyAlertStatus das 
        ON t1.ID = das.ID
        AND das.AlertDate <= t1.SpecDate -- 忽略SpecDate之后的记录
    WHERE das.DailyAlert = 'Y'
),
GroupValidCheck AS (
    SELECT 
        ID,
        SpecDate,
        GroupID,
        COUNT(*) AS ConsecutiveDays,
        -- 判断当前连续组是否有日期落在目标3天窗口内
        MAX(CASE WHEN AlertDate BETWEEN DATEADD(DAY, -2, SpecDate) AND SpecDate THEN 1 ELSE 0 END) AS IsValid
    FROM ContinuousGroup
    GROUP BY ID, SpecDate, GroupID
)
SELECT 
    ID,
    SpecDate,
    ConsecutiveDays AS ConsecutiveAlertDays
FROM GroupValidCheck
WHERE IsValid = 1
ORDER BY ID, SpecDate

结果验证 #

执行上述代码后输出结果和期望完全一致：

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