Redshift SQL中基于滑动窗口保留患者最新告警的高效实现

原始表结构与数据 #

假设有一张告警表（简化版），结构及数据如下：

需求说明 #

针对每个patient_id，仅保留给定窗口周期（如window_size = 7天）内的最新告警，处理逻辑如下：

• 窗口范围为「某一天」到「该天+window_size天」的连续日期
• 从每个患者的最晚告警时间开始倒推，筛选该窗口内的最新告警，之后排除该窗口内的所有告警，重复此过程直到处理完该患者的所有告警
• 表中包含大量患者，且告警时间与ID顺序混乱

预期输出（window_size=7时） #

处理后得到的告警表如下：

高效实现方案 #

核心思路 #

利用**递归CTE（Common Table Expression）**实现倒推式窗口筛选，结合窗口函数快速定位每个阶段的最新告警，避免全表扫描或低效循环，适合大数据量场景。

SQL代码实现（以PostgreSQL为例，其他数据库可调整语法） #

WITH RECURSIVE filtered_alerts AS (
    -- 初始步骤：获取每个患者的最晚告警，作为第一个筛选结果
    SELECT 
        patient_id,
        alert_id,
        alert_timestamp,
        -- 计算当前窗口的起始时间（最晚时间 - window_size）
        alert_timestamp - INTERVAL '7 days' AS window_start
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY alert_timestamp DESC) AS rn
        FROM alerts
    ) t
    WHERE rn = 1

    UNION ALL

    -- 递归步骤：从剩余告警中，筛选不在已选窗口内的最新告警
    SELECT 
        a.patient_id,
        a.alert_id,
        a.alert_timestamp,
        a.alert_timestamp - INTERVAL '7 days' AS window_start
    FROM (
        SELECT 
            a.*,
            ROW_NUMBER() OVER (PARTITION BY a.patient_id ORDER BY a.alert_timestamp DESC) AS rn
        FROM alerts a
        JOIN filtered_alerts fa 
            ON a.patient_id = fa.patient_id
            -- 只保留当前患者中，时间早于已选窗口起始时间的告警
            AND a.alert_timestamp < fa.window_start
        -- 排除已被选中的告警
        WHERE NOT EXISTS (
            SELECT 1 FROM filtered_alerts fa2 
            WHERE fa2.patient_id = a.patient_id AND fa2.alert_id = a.alert_id
        )
    ) a
    WHERE rn = 1
)
-- 最终结果按患者ID和时间排序
SELECT patient_id, alert_id, alert_timestamp
FROM filtered_alerts
ORDER BY patient_id, alert_timestamp;

性能优化要点 #

• 索引优化：给(patient_id, alert_timestamp)建立联合索引，大幅提升窗口函数和递归关联的查询速度
• 数据集逐步缩小：递归CTE每次只处理未被筛选的告警，避免重复扫描全表
• 数据库适配：MySQL 8.0+需将INTERVAL '7 days'改为INTERVAL 7 DAY；SQL Server需改为DATEADD(DAY, -7, alert_timestamp)

非递归替代方案（适合不支持递归CTE的数据库） #

WITH ranked_alerts AS (
    SELECT 
        *,
        -- 计算每个告警对应的分组窗口起始时间：从最晚时间倒推，每7天一个分组
        DATE_TRUNC('day', 
            MAX(alert_timestamp) OVER (PARTITION BY patient_id) 
            - (MAX(alert_timestamp) OVER (PARTITION BY patient_id) - alert_timestamp)
        ) - INTERVAL '7 days' * FLOOR(
            (MAX(alert_timestamp) OVER (PARTITION BY patient_id) - alert_timestamp) / INTERVAL '7 days'
        ) AS group_window_start
    FROM alerts
),
grouped_latest AS (
    SELECT 
        patient_id,
        alert_id,
        alert_timestamp,
        ROW_NUMBER() OVER (PARTITION BY patient_id, group_window_start ORDER BY alert_timestamp DESC) AS rn
    FROM ranked_alerts
)
SELECT patient_id, alert_id, alert_timestamp
FROM grouped_latest
WHERE rn = 1
ORDER BY patient_id, alert_timestamp;

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