基于连续行时间差分组的SQL Server查询实现需求

核心思路 #

利用窗口函数LAG()计算相邻行的时间差，再通过累计求和生成分组ID，最后统计每个分组的行数并仅在分组起始行显示计数。

分步实现代码 #

1. 计算相邻行时间差并标记分组边界

首先对每个userId的数据按event_time排序（因id不连续，必须用时间排序保证连续性），计算当前行与上一行的时间差，同时生成分组ID：

WITH ranked_events AS (
    SELECT
        id,
        userId,
        event_time,
        -- 计算当前行与上一行的时间差（秒）
        DATEDIFF(second, LAG(event_time) OVER (PARTITION BY userId ORDER BY event_time), event_time) AS time_diff,
        -- 生成分组ID：时间差不在[-10,-1]范围时，开启新分组
        SUM(CASE 
                WHEN DATEDIFF(second, LAG(event_time) OVER (PARTITION BY userId ORDER BY event_time), event_time) BETWEEN -10 AND -1 
                THEN 0 
                ELSE 1 
            END)
            OVER (PARTITION BY userId ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM user_events
)

2. 统计分组行数并标记起始行

统计每个分组的总行数，仅在分组起始行（分组ID变化的第一行）显示计数，其他行留空：

SELECT
    id,
    userId,
    event_time,
    time_diff,
    -- 仅在分组起始行展示计数，其他行为NULL
    CASE
        WHEN group_id <> LAG(group_id) OVER (PARTITION BY userId ORDER BY event_time) 
             OR LAG(group_id) OVER (PARTITION BY userId ORDER BY event_time) IS NULL
        THEN COUNT(*) OVER (PARTITION BY userId, group_id)
        ELSE NULL
    END AS count
FROM ranked_events
ORDER BY userId, event_time;

代码说明 #

• 时间差计算：用LAG(event_time)获取上一行时间，DATEDIFF(second, ...)得到秒级时间差，严格匹配你要求的-1至-10秒范围。
• 分组ID生成：通过累计求和，每当时间差不符合条件时分组ID加1，确保连续符合条件的行归为同一分组。
• 计数标记：对比当前行与上一行的分组ID，判断是否为分组起始行，仅在起始行展示该分组的总行数，满足“分组计数记录在起始行”的要求。
• 用户独立分组：所有窗口函数都用PARTITION BY userId保证每个用户的分组独立计算。

示例验证 #

假设你的数据如下：

执行查询后结果：

完全符合分组规则：前3行为一个分组（计数3在起始行），后2行为一个分组（计数2在起始行），不符合时间差的行被正确断开分组。

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