主页 / coding / 问题 / 79193544
Accepted
walkingbas
Asked: 2024-11-16 02:21:06 +0800 CST

如何在 SQL 中有效地查找符合匹配条件的连续行？

  • 772

我有一个跟踪用户活动的数据集，我需要识别满足特定条件的连续行。表结构和示例数据如下：

表：UserActivity

用户身份 活动 时间戳
1 登录 2023-11-01 08:00:00
1 页面视图 2023-11-01 08:10:00
1 登录 2023-11-01 08:15:00
1 登出 2023-11-01 08:20:00
2 登录 2023-11-01 09:00:00
2 登录 2023-11-01 09:05:00
2 页面视图 2023-11-01 09:10:00

我想识别同一用户的连续行具有相同活动及其时间戳的情况。例如：

对于 UserID = 2，有两个连续的登录活动。像这样。

预期输出

用户身份 活动 开始时间戳 结束时间戳
2 登录 2023-11-01 09:00:00 2023-11-01 09:05:00

我尝试使用自连接将每一行与前一行进行比较，但对于大型数据集来说，这会导致计算成本过高。我也尝试使用LAG和LEAD函数，但无法有效地对连续行进行分组。

sql

2 个回答

  1. 来自 stackexchange 用户 tombom 的替代解决方案适应策略 https://dba.stackexchange.com/questions/188775/selecting-consecutive-rows-with-same-values

    此解决方案可处理每个 UserID+Activity 连续 2 个以上的重复项。

    SELECT userID, 
       Activity, 
       MIN(Timestamp) as starttime,
       MAX(Timestamp) as endtime
FROM (
    SELECT
    t.*
    , @groupNumber := IF(@prev_userID != userID OR @prev_Activity != Activity, @groupNumber + 1, @groupNumber) AS gn
    , @prev_userID := userID
    , @prev_Activity := Activity
    FROM Example t
    , (SELECT @groupNumber := 0, @prev_userID := NULL, @prev_Activity := NULL) var_init_subquery
    ORDER BY userID, Timestamp
) sq
GROUP BY gn, userID, Activity
HAVING Count(*) > 1

    小提琴

    • 2
  2. Best Answer

    以下是使用 LAG 的可能解决方案。此解决方案假设每个 UserID+Activity 的重复项不超过 2 个。

    WITH CTE AS
(
SELECT UserID, Activity, 
        LAG(USERID) OVER (ORDER BY TimeStamp)  as PrevID, 
        LAG(Activity) OVER (ORDER BY TimeStamp)  as PrevActivity, 
        LAG(TimeStamp) OVER (ORDER BY TimeStamp)  as StartTimeStamp, 
        TimeStamp as EndTimeStamp
FROM Example
ORDER BY TimeStamp
)
SELECT UserID, Activity, StartTimeStamp, EndTimeStamp
FROM CTE
WHERE UserID=PrevID AND Activity=PrevActivity
ORDER BY StartTimeStamp

    小提琴

    用户身份 活动 开始时间戳 结束时间戳
    2 登录 2023-11-01 09:00:00 2023-11-01 09:05:00
    • 1

相关问题