MySQL用户行为日志SQL实现：新增会话内事件序列与生命周期会话序列

原始数据 #

我在MySQL中存储着一份用户行为日志数据集，每个用户拥有唯一的user_id，系统记录用户会话过程中的事件动作日志。数据表包含user_id、session_id、dateTime、event字段，原始数据如下：

需求说明 #

需要编写MySQL查询语句，保留原始数据的同时，新增两个计算列：

• event_seq：每个用户会话内的事件动作序列，按事件发生时间排序，从1开始递增
• session_seq：用户整个生命周期内的会话序列，按会话的最早事件时间排序，从1开始递增

预期输出 #

解决方案SQL #

SELECT 
    user_id,
    session_id,
    dateTime,
    event,
    -- 会话内事件序列：按用户+会话分组，按时间排序生成行号
    ROW_NUMBER() OVER (PARTITION BY user_id, session_id ORDER BY dateTime) AS event_seq,
    -- 用户会话序列：先获取每个会话的最早时间，再按用户分组对会话排序
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY session_start_time) AS session_seq
FROM (
    -- 子查询：为每个会话添加最早时间字段
    SELECT 
        *,
        MIN(dateTime) OVER (PARTITION BY user_id, session_id) AS session_start_time
    FROM user_behavior_logs
) AS sub;

语句说明 #

1. event_seq：使用ROW_NUMBER()窗口函数，以user_id和session_id作为分区依据，按dateTime升序排序，为每个会话内的事件生成从1开始的连续序号。
2. session_seq：先通过子查询用MIN(dateTime)获取每个用户每个会话的最早事件时间session_start_time，再在外层用DENSE_RANK()窗口函数，以user_id为分区依据，按session_start_time升序排序，为用户的每个会话生成唯一的序列编号。

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