无窗口函数按会话序号计算平均会话时长（SQL求助）

步骤拆解（无窗口函数） #

因为不能用窗口函数，我们分三步实现：

1. 聚合每个会话的基本信息
   先把每个用户的每个会话的总时长、开始时间计算出来：

SELECT 
    id,
    session,
    MIN(server_time) AS session_start,  -- 会话的最早开始时间
    SUM(event_duration) AS session_duration  -- 会话总时长（所有事件时长之和）
FROM 
    your_table_name  -- 替换成你的表名
GROUP BY 
    id, session

这一步会得到每个用户每个会话的核心数据，比如用户1的两个会话分别对应时长68秒（36+32）和41秒。

2. 给每个会话分配序号
   通过自连接统计每个会话的顺序：对于每个会话，统计同一用户下开始时间更早的会话数量，加1就是该会话的序号（比如第一个会话没有更早的，序号为1）：

SELECT 
    s1.id,
    s1.session,
    s1.session_duration,
    COUNT(s2.session) + 1 AS session_number
FROM 
    (第一步的子查询结果) s1
LEFT JOIN 
    (第一步的子查询结果) s2 
    ON s1.id = s2.id AND s1.session_start > s2.session_start
GROUP BY 
    s1.id, s1.session, s1.session_duration

3. 按序号计算平均时长
   最后把上面的结果按会话序号分组，计算平均时长：

完整SQL代码 #

SELECT 
    session_number,
    ROUND(AVG(session_duration), 2) AS average_session_duration  -- 保留两位小数
FROM (
    -- 第二步：给会话分配序号
    SELECT 
        s1.id,
        s1.session,
        s1.session_duration,
        COUNT(s2.session) + 1 AS session_number
    FROM (
        -- 第一步：聚合会话信息
        SELECT 
            id,
            session,
            MIN(server_time) AS session_start,
            SUM(event_duration) AS session_duration
        FROM 
            your_table_name
        GROUP BY 
            id, session
    ) s1
    LEFT JOIN 
        (
        SELECT 
            id,
            session,
            MIN(server_time) AS session_start
        FROM 
            your_table_name
        GROUP BY 
            id, session
        ) s2 
        ON s1.id = s2.id AND s1.session_start > s2.session_start
    GROUP BY 
        s1.id, s1.session, s1.session_duration
) AS ranked_sessions
GROUP BY 
    session_number
ORDER BY 
    session_number;

补充说明 #

• 如果需要排除未结束的会话（比如只有level_started事件的会话，总时长为0），可以在第一步子查询中添加HAVING SUM(event_duration) > 0。
• 替换代码中的your_table_name为你实际使用的表名。

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