Snowflake中带条件的LEAD函数应用：区域受影响时长计算问题

核心思路 #

针对两种场景的统计痛点，核心是先去重连续重复的受影响标记，再为每个有效受影响事件匹配最近的后续恢复事件，最后拆分跨天时并按维度汇总：

• 过滤上周数据，区分受影响事件（AFFECTED_ZONE为1-10）和恢复事件（AFFECTED_ZONE=0）
• 去重同一ID下连续重复的同区域受影响标记，避免重复计算
• 为每个受影响事件匹配最近的恢复时间，无恢复事件则用统计周期内的结束时间兜底
• 拆分跨天的受影响时长到对应日期，确保按天统计的准确性

实现SQL #

WITH affected_events AS (
    -- 筛选上周的受影响事件，标记是否保留当前记录（去重连续重复的同区域）
    SELECT 
        id,
        affected_zone,
        event_time,
        CASE 
            WHEN LAG(affected_zone) OVER (PARTITION BY id ORDER BY event_time) = affected_zone 
            THEN 0 
            ELSE 1 
        END AS keep_flag
    FROM your_table
    WHERE 
        affected_zone BETWEEN 1 AND 10
        AND event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
        AND event_time < CURDATE()
),
filtered_affected AS (
    -- 只保留有效受影响事件（去掉连续重复的标记）
    SELECT id, affected_zone, event_time
    FROM affected_events
    WHERE keep_flag = 1
),
recovery_events AS (
    -- 筛选上周的恢复事件
    SELECT 
        id,
        event_time AS recovery_time
    FROM your_table
    WHERE 
        affected_zone = 0
        AND event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
        AND event_time < CURDATE()
),
matched_events AS (
    -- 为每个受影响事件匹配最近的后续恢复时间，无恢复则用当天结束时间
    SELECT 
        fa.id,
        fa.affected_zone,
        fa.event_time AS start_time,
        COALESCE(
            (SELECT MIN(re.recovery_time) FROM recovery_events re WHERE re.id = fa.id AND re.recovery_time > fa.event_time),
            DATE_ADD(DATE(fa.event_time), INTERVAL 1 DAY)
        ) AS actual_end_time
    FROM filtered_affected fa
),
daily_duration AS (
    -- 拆分跨天时长到对应日期，计算当天内的秒数
    SELECT 
        id,
        affected_zone,
        DATE(start_time) AS stat_date,
        TIMESTAMPDIFF(SECOND, start_time, LEAST(actual_end_time, DATE_ADD(DATE(start_time), INTERVAL 1 DAY))) AS duration
    FROM matched_events
    UNION ALL
    SELECT 
        id,
        affected_zone,
        DATE(actual_end_time) AS stat_date,
        TIMESTAMPDIFF(SECOND, DATE(actual_end_time), actual_end_time) AS duration
    FROM matched_events
    WHERE actual_end_time > DATE_ADD(DATE(start_time), INTERVAL 1 DAY)
)
-- 按ID、区域、日期汇总总受影响时长（秒）
SELECT 
    id,
    affected_zone,
    stat_date,
    SUM(duration) AS total_affected_seconds
    -- 如需转换为分钟/小时，可改为 SUM(duration)/60 或 SUM(duration)/3600
FROM daily_duration
GROUP BY id, affected_zone, stat_date
ORDER BY id, stat_date, affected_zone;

关键说明 #

1. 去重连续标记：用LAG函数对比当前与上一条记录的区域值，只保留连续重复中的第一个，解决同一区域重复标记导致的多算问题。
2. 匹配恢复事件：通过关联子查询找到每个受影响事件之后最早的恢复时间，确保多区域连续受影响后统一恢复时，所有受影响事件都能正确匹配到同一个恢复时间。
3. 跨天处理：用UNION ALL拆分跨天的时长，将受影响时间拆分为开始当天和结束当天两部分，保证按天统计的准确性。
4. 边界处理：对于统计周期内未恢复的事件，用当天结束时间作为恢复时间，避免遗漏未恢复的时长。

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