基于Lag/Lead的分组：SQL Server 2014用户活动序列分组需求

在SQL Server 2014环境下，有一张跟踪用户活动的表，结构包含USER_ID、EVENT（如LOGIN、COMPLETE等）、EVENT_DATE字段，原始数据如下：

需要生成SEQ序列字段，规则为：同一用户中，与前一条事件间隔小于60天的记录共享相同SEQ值，期望结果如下：

当前编写的测试代码无法实现需求，代码如下：

WITH testTable (USERID, EVENT, EVENT_DATE) AS
(
    SELECT 15552221111, 'LOGIN', '2022-06-01'   UNION ALL
    SELECT 15552221111, 'COMPLETE', '2022-06-01'   UNION ALL
    SELECT 15552221111, 'LOGIN', '2022-09-01'    UNION ALL
    SELECT 15552221111, 'SHUTDOWN', '2022-09-11'    UNION ALL
    SELECT 15552222222, 'LOGIN', '2022-04-01'    UNION ALL
    SELECT 15552222222, 'PROCESSING',  '2022-04-08 '  UNION ALL
    SELECT 15552222222, 'PROCESSING', '2022-06-10'    UNION ALL
    SELECT 15552222222, 'COMPLETE', '2022-06-11'    UNION ALL
    SELECT 15552222222, 'LOGIN', '2022-09-08'     
)     
SELECT 
    USERID
    , EVENT
    , EVENT_DATE
    , LEAD (EVENT_DATE, 1, 0) OVER (PARTITION BY USERID ORDER BY EVENT_DATE) NEXT_DATE
    , ROW_NUMBER() OVER (PARTITION BY USERID ORDER BY EVENT_DATE) RECORD_SEQ    
FROM testTable 

要实现该需求，核心是通过窗口函数识别用户事件间隔是否超过60天，再累计生成SEQ值。针对SQL Server 2014的特性，实现代码如下：

WITH testTable (USER_ID, EVENT, EVENT_DATE) AS
(
    SELECT 15552221111, 'LOGIN', CAST('2022-06-01' AS DATE) UNION ALL
    SELECT 15552221111, 'COMPLETE', CAST('2022-06-08' AS DATE) UNION ALL
    SELECT 15552221111, 'LOGIN', CAST('2022-09-01' AS DATE) UNION ALL
    SELECT 15552221111, 'SHUTDOWN', CAST('2022-09-11' AS DATE) UNION ALL
    SELECT 15552222222, 'LOGIN', CAST('2022-04-01' AS DATE) UNION ALL
    SELECT 15552222222, 'PROCESSING', CAST('2022-04-08' AS DATE) UNION ALL
    SELECT 15552222222, 'PROCESSING', CAST('2022-06-10' AS DATE) UNION ALL
    SELECT 15552222222, 'COMPLETE', CAST('2022-06-11' AS DATE) UNION ALL
    SELECT 15552222222, 'LOGIN', CAST('2022-09-08' AS DATE)
),
ranked_events AS (
    SELECT 
        USER_ID,
        EVENT,
        EVENT_DATE,
        -- 获取当前记录的上一条事件日期
        LAG(EVENT_DATE) OVER (PARTITION BY USER_ID ORDER BY EVENT_DATE) AS PREV_EVENT_DATE,
        -- 判断当前记录与上一条间隔是否超过60天，超过则标记为1，否则0
        CASE 
            WHEN DATEDIFF(DAY, LAG(EVENT_DATE) OVER (PARTITION BY USER_ID ORDER BY EVENT_DATE), EVENT_DATE) > 60 THEN 1
            ELSE 0
        END AS IS_NEW_GROUP
    FROM testTable
),
grouped_events AS (
    SELECT 
        USER_ID,
        EVENT,
        EVENT_DATE,
        -- 累计求和标记值，生成SEQ
        SUM(IS_NEW_GROUP) OVER (PARTITION BY USER_ID ORDER BY EVENT_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + 1 AS SEQ
    FROM ranked_events
)
SELECT USER_ID, EVENT, EVENT_DATE, SEQ
FROM grouped_events
ORDER BY USER_ID, EVENT_DATE;

代码说明 #

1. ranked_events CTE：使用LAG函数获取每个用户的上一条事件日期，通过DATEDIFF计算间隔天数，判断是否超过60天，生成分组标记IS_NEW_GROUP。
2. grouped_events CTE：对每个用户的分组标记进行累计求和，再加1（第一条记录无前置事件，标记为0，累计后加1得到初始SEQ=1），最终生成符合规则的SEQ值。
3. 最后按用户和事件日期排序输出结果，与期望结果一致。

内容的提问来源于stack exchange，提问作者Depth of Field