为列中重复值设置数值标记/秩 拆分同ID下的多用户会话

需求说明 #

开展应用内客户旅程追踪相关工作，需要对共享同一ID的多个会话做区分，核心判定规则为每当sequence number从1开始时即识别为新会话，最终生成对应会话编号列session。

现有问题 #

已尝试直接对sequence number使用row_number()函数实现，但当sequence_number跳变后首次出现新数值时（例如示例最后一行的4），会被错误识别为第一个计数，不符合业务要求。

实现逻辑 #

这是典型的按标记切分会话的场景，可通过2步窗口函数实现，无需嵌套复杂逻辑：

1. 按ID分组，按事件发生时间正序排序，标记所有sequence_number = 1的行作为会话起点，打标记值1，其余行标记为0
2. 对上述标记值做同分组下的滚动累加，累加结果即为同ID下的会话编号

示例代码（标准SQL） #

WITH mark_session_start AS (
    SELECT
        *,
        CASE WHEN sequence_number = 1 THEN 1 ELSE 0 END AS is_new_session
    FROM 你的表名
    WINDOW base_window AS (PARTITION BY id ORDER BY event_time ASC)
),
generate_session_id AS (
    SELECT
        *,
        SUM(is_new_session) OVER base_window AS session
    FROM mark_session_start
    WINDOW base_window AS (PARTITION BY id ORDER BY event_time ASC)
)
-- 可根据需要新增会话内序号字段
SELECT
    id,
    event_time,
    sequence_number,
    session,
    ROW_NUMBER() OVER (PARTITION BY id, session ORDER BY event_time ASC) AS session_inner_seq
FROM generate_session_id

代码说明 #

• 请将代码中的你的表名替换为实际业务表名
• 排序字段event_time请替换为你实际用于保证事件顺序的字段（如果已经有全局自增ID也可使用）
• 该逻辑天然适配sequence_number断号、跳号的场景，只要出现1就会生成新的会话编号，完全符合业务规则

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