Redshift中合并连续时间段的同邮箱记录实现方案

原始表结构及数据 #

期望结果 #

问题说明 #

直接执行以下GROUP BY语句会把所有相同id和email的记录合并成一行，最终只得到2条结果，不符合需求：

select id, email, min(start_dt), max(end_dt)
from table
group by id, email;

我们需要的是合并连续时间段的相同email记录，中间被其他email打断的非连续记录需保留单独行。

解决方案 #

无需使用递归CTE，通过窗口函数即可实现，核心思路是标记出连续的记录组，再对组内数据聚合：

WITH ranked_data AS (
    SELECT 
        id,
        email,
        start_dt,
        end_dt,
        -- 标记新组：当前记录的start_dt不是上一条end_dt的次日，或为组内第一条记录
        SUM(CASE 
            WHEN LAG(end_dt) OVER (PARTITION BY id ORDER BY start_dt) + INTERVAL '1 day' = start_dt 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY id ORDER BY start_dt) AS group_id
    FROM your_table_name
)
SELECT 
    id,
    email,
    MIN(start_dt) AS start_dt,
    MAX(end_dt) AS end_dt
FROM ranked_data
GROUP BY id, email, group_id
ORDER BY start_dt;

代码解释 #

1. LAG(end_dt) OVER (PARTITION BY id ORDER BY start_dt)：获取同一id下，上一条记录的结束日期
2. SUM(...) OVER (...) AS group_id：通过累加标记值，将连续的相同email记录归为同一个group_id
3. 最终按id、email、group_id分组，取每组的最小开始日期和最大结束日期，得到期望结果

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