如何在SQL中高效查找符合条件的连续行？

针对你的需求，推荐使用窗口函数分组法，既避免自连接的高计算成本，又能高效对连续相同活动的行进行分组，适合大数据场景。

实现思路 #

核心是通过两个窗口函数生成的行号差值，将同一用户下连续相同的Activity归为同一分组：

1. 对每个用户的活动按时间戳排序，生成全局行号
2. 对每个用户+活动的组合按时间戳排序，生成组内行号
3. 两个行号的差值相同的行，即为连续相同的活动组
4. 最后筛选出组内记录数≥2的分组，提取起止时间

完整SQL代码 #

WITH ActivityGroups AS (
    SELECT
        UserID,
        Activity,
        Timestamp,
        -- 每个用户的全局行号（按时间排序）
        ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Timestamp) AS global_row,
        -- 每个用户+活动组合的组内行号（按时间排序）
        ROW_NUMBER() OVER (PARTITION BY UserID, Activity ORDER BY Timestamp) AS activity_row
    FROM UserActivity
),
ContinuousGroups AS (
    SELECT
        UserID,
        Activity,
        MIN(Timestamp) AS StartTimestamp,
        MAX(Timestamp) AS EndTimestamp,
        COUNT(*) AS ActivityCount
    FROM ActivityGroups
    -- 用行号差作为连续分组的标识
    GROUP BY UserID, Activity, (global_row - activity_row)
    -- 筛选出至少有2条连续记录的分组
    HAVING COUNT(*) >= 2
)
SELECT UserID, Activity, StartTimestamp, EndTimestamp
FROM ContinuousGroups
ORDER BY UserID, StartTimestamp;

代码解释 #

1. ActivityGroups CTE：

   • global_row：给每个用户的活动按时间顺序分配唯一行号，确保同一用户的活动按时间排序。
   • activity_row：给每个用户的同一活动按时间顺序分配行号，比如用户2的两次Login会被分配1和2。
   • 连续相同的Activity，global_row - activity_row的结果会一致，非连续的则不同。比如用户2的两次Login：
     • 第一次Login：global_row=1，activity_row=1 → 差值0
     • 第二次Login：global_row=2，activity_row=2 → 差值0
     • 后续PageView：global_row=3，activity_row=1 → 差值2
       这样就把连续的Login归为同一分组。

2. ContinuousGroups CTE：

   • 按UserID、Activity和行号差值分组，计算每组的最早/最晚时间戳，以及记录数。
   • 通过HAVING COUNT(*) >=2筛选出至少有2条连续记录的分组，也就是你需要的连续相同活动。

3. 最终查询：提取需要的字段，按用户和起始时间排序。

效果验证 #

用你提供的示例数据执行该SQL，会得到期望的输出：

这种方法的计算效率远高于自连接，因为窗口函数是单次扫描表完成计算，适合处理大数据集。

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