如何基于时间差分组用户事件并生成以成功事件收尾的序列？

核心思路是通过窗口函数组合，先划分事件组，再在组内生成递增序列。以下是具体实现步骤（以SQL为例）：

1. 标记新事件组的起始行 #

首先判断每一行是否是新事件组的开始，判断条件包含两个核心规则：

• 当前事件与上一个事件的时间间隔超过阈值（比如3分钟，覆盖你提到的1-2分钟间隔，避免微小时间差导致的误判）
• 上一个事件是成功事件（成功事件是组的终点，后续事件必然属于新组）

用LAG()函数获取上一行的时间戳和success_key，结合CASE WHEN完成标记：

WITH step1 AS (
    SELECT 
        user_id,
        timestamp,
        success_key,
        -- 标记新组起始：首次行/间隔超阈值/上一行是成功事件
        CASE 
            WHEN LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) IS NULL THEN 1
            WHEN timestamp - LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) > INTERVAL '3 minutes' THEN 1
            WHEN LAG(success_key) OVER (PARTITION BY user_id ORDER BY timestamp) IS NOT NULL THEN 1
            ELSE 0
        END AS is_new_group
    FROM user_attempts
)

2. 生成事件组ID #

基于上一步的标记，用累加函数SUM() OVER()为每个事件分配组ID，同一组内的事件会得到相同ID：

, step2 AS (
    SELECT 
        *,
        SUM(is_new_group) OVER (PARTITION BY user_id ORDER BY timestamp) AS group_id
    FROM step1
)

3. 生成组内尝试序列 #

最后在每个组内按时间排序，用ROW_NUMBER()生成从1开始的序列：

SELECT 
    timestamp,
    success_key,
    ROW_NUMBER() OVER (PARTITION BY user_id, group_id ORDER BY timestamp) AS "Event Sequence"
FROM step2
ORDER BY user_id, timestamp;

关键细节说明 #

• 阈值调整：如果实际场景中1-2分钟的间隔有波动，可以微调INTERVAL '3 minutes'为更合适的值（比如2.5分钟）
• 成功事件的边界作用：即使两个事件间隔很短，但上一个是成功事件，下一个仍会被判定为新组，完全符合你“序列在成功事件处终止”的需求
• 首次尝试成功：这类事件会单独成为一个组，序列为1，完美匹配示例场景

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