Spark SQL：如何为用户会话内的多行分配相同Session_id

问题描述 #

现有用户操作数据集：

需要为每个从logged in到logged out的会话分配唯一的Session_id，期望结果如下：

实现方案 #

无需使用LAG函数，用累计求和窗口函数结合密度排名函数就能简洁实现需求：

步骤1：生成用户内部会话编号 #

按用户分区，对每行的logged in事件累计计数，得到每个用户内部的会话序号：

WITH user_sessions AS (
    SELECT 
        "User",
        Action,
        -- 每次遇到logged in就给当前用户的会话计数+1
        SUM(CASE WHEN Action = 'logged in' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY "User" ORDER BY 操作时间列) AS user_session_num
    FROM 你的表名
)

注意：请将操作时间列替换为表中记录操作时间的实际字段（如created_at），确保会话按时间顺序生成。若没有时间字段，需确认表的行顺序严格对应操作顺序，可临时用ORDER BY (SELECT NULL)（部分数据库支持），但不推荐，因为行顺序无法保证稳定。

步骤2：生成全局唯一Session_id #

对用户内部的会话编号做全局密度排名，得到连续递增的全局Session_id：

SELECT 
    "User",
    Action,
    DENSE_RANK() OVER (ORDER BY "User", user_session_num) AS Session_id
FROM user_sessions
ORDER BY "User", Session_id;

完整SQL示例 #

WITH user_sessions AS (
    SELECT 
        "User",
        Action,
        SUM(CASE WHEN Action = 'logged in' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY "User" ORDER BY created_at) AS user_session_num
    FROM user_actions
)
SELECT 
    "User",
    Action,
    DENSE_RANK() OVER (ORDER BY "User", user_session_num) AS Session_id
FROM user_sessions
ORDER BY "User", Session_id;

原理说明 #

• SUM(...) OVER (PARTITION BY "User" ORDER BY ...)：按用户分组，按时间顺序累计logged in事件的数量，每个用户的第1次登录对应会话1，第2次登录对应会话2，同一会话内的所有操作共享该编号。
• DENSE_RANK() OVER (ORDER BY "User", user_session_num)：将每个用户的内部会话编号转换为全局唯一的连续ID，确保不同用户的会话ID不重复且连续递增。

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