不使用LEAD/LAG函数统计点击流用户会话起止时间的SQL实现

核心思路 #

不用窗口偏移函数也能实现连续用户分组，核心是通过双行号的差值标记连续组：

• 先给同个session里的所有记录按访问时间排序，生成全局流水号
• 再给同个session、同一个user的记录按访问时间排序，生成用户维度的流水号
• 如果用户没有切换，两个流水号会同步加1，差值固定不变；一旦切换用户，全局流水号加1，但用户流水号要么重置要么跳号，差值就会变化，所以差值相同的同用户记录就是同一个连续访问段

实现代码 #

WITH dual_rn AS (
    SELECT
        session_id,
        TRIM(user_id) AS user_id, -- 样例输入user_id带空格可加该步骤清理
        session_time,
        ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY session_time) AS rn_global,
        ROW_NUMBER() OVER(PARTITION BY session_id, user_id ORDER BY session_time) AS rn_user
    FROM session_detail -- 替换为实际表名
),
continuous_group AS (
    SELECT
        *,
        rn_global - rn_user AS group_flag
    FROM dual_rn
)
SELECT
    session_id,
    user_id,
    MIN(session_time) AS session_start_time,
    MAX(session_time) AS session_end_time
FROM continuous_group
GROUP BY session_id, user_id, group_flag
ORDER BY session_start_time;

验证说明 #

代入提供的样例数据执行后，输出结果和期望格式完全一致：前四条u1记录差值固定为0归为一组，中间四条u2记录差值固定为4归为一组，最后四条u1记录差值固定为4，因为user_id不同分组时自动拆分，最终得到三段连续访问记录。

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