如何为同source_id和event_user的时序连续记录生成series_id

你需要为同一source_id和event_user下按event_date连续的记录组分配唯一的series_id，这属于典型的Gaps and Islands（间隙与孤岛）问题，以下提供两种可行的PostgreSQL解决方案：

方法一：使用LAG()函数与累加求和 #

这种方法通过判断当前记录与前一条记录是否属于同一组，标记新组的起始点，再通过累加起始点标识生成全局唯一的series_id。

WITH ranked_data AS (
    SELECT 
        id,
        source_id,
        event_user,
        event_date,
        -- 对比当前记录与前一条的source_id和event_user，判断是否为新组
        CASE 
            WHEN LAG((source_id, event_user)) OVER (ORDER BY event_date) = (source_id, event_user)
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM tmp
),
series_groups AS (
    SELECT 
        *,
        -- 累加新组标识，生成全局唯一的series_id
        SUM(is_new_group) OVER (ORDER BY event_date) AS series_id
    FROM ranked_data
)
SELECT id, source_id, event_user, event_date, series_id
FROM series_groups
ORDER BY event_date;

方法二：使用双ROW_NUMBER()差值法 #

这是Gaps and Islands问题的经典解法，通过计算全局行号与分组行号的差值，将差值相同的记录归为同一组，再生成series_id。

WITH ranked_data AS (
    SELECT 
        id,
        source_id,
        event_user,
        event_date,
        -- 全局按event_date排序的行号
        ROW_NUMBER() OVER (ORDER BY event_date) AS global_rn,
        -- 按source_id和event_user分组后按event_date排序的行号
        ROW_NUMBER() OVER (PARTITION BY source_id, event_user ORDER BY event_date) AS user_rn
    FROM tmp
),
series_groups AS (
    SELECT 
        id,
        source_id,
        event_user,
        event_date,
        -- 对差值分组生成series_id，同一连续组的差值相同
        DENSE_RANK() OVER (ORDER BY source_id, event_user, global_rn - user_rn) AS series_id
    FROM ranked_data
)
SELECT id, source_id, event_user, event_date, series_id
FROM series_groups
ORDER BY event_date;

说明 #

• 两种方法都兼容source_id为UUID类型的场景，无需额外修改。
• 第一种方法逻辑更直观，适合快速理解；第二种方法是通用的Gaps and Islands解决方案，适用于更复杂的连续分组场景。
• 执行结果中，同一source_id和event_user的连续记录会被分配相同的series_id，被其他用户打断的记录组会生成新的series_id。

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