经典sql题（七）查找直播间最大在线人数

使用 SQL 分析房间用户状态变化

本文将详细介绍如何使用 SQL 的窗口函数和聚合函数，分析用户在房间中的状态变化，目标是计算每个房间指定时间段内的最大用户状态。

示例数据

假设我们的数据表包含以下字段：

第一步：提取事件并标记类型

在这一步，我们将从原始数据中提取每个用户的登录和退出事件，并为每个事件分配一个类型标记（1 表示登录，-1 表示退出）。

提取登录事件

SELECT 
    room_id,
    user_id,
    UNIX_TIMESTAMP(login_time) AS event_time,
    1 AS user_type
FROM 
    table
WHERE 
    FROM_UNIXTIME(UNIX_TIMESTAMP(login_time), '%Y%m%d') = '20230310'
    AND (HOUR(login_time) BETWEEN 12 AND 13)

结果示例

提取退出事件

SELECT 
    room_id,
    user_id,
    UNIX_TIMESTAMP(logout_time) AS event_time,
    -1 AS user_type
FROM 
    table
WHERE 
    FROM_UNIXTIME(UNIX_TIMESTAMP(logout_time), '%Y%m%d') = '20230310'
    AND (HOUR(logout_time) BETWEEN 12 AND 13)

结果示例

合并结果

我们将登录和退出事件通过 UNION ALL 合并，以获得完整的事件列表。

SELECT 
    room_id,
    user_id,
    event_time,
    user_type
FROM (
    -- 登录事件
    SELECT 
        room_id,
        user_id,
        UNIX_TIMESTAMP(login_time) AS event_time,
        1 AS user_type
    FROM 
        table
    WHERE 
        FROM_UNIXTIME(UNIX_TIMESTAMP(login_time), '%Y%m%d') = '20230310'
        AND (HOUR(login_time) BETWEEN 12 AND 13)
    UNION ALL
    -- 退出事件
    SELECT 
        room_id,
        user_id,
        UNIX_TIMESTAMP(logout_time) AS event_time,
        -1 AS user_type
    FROM 
        table
    WHERE 
        FROM_UNIXTIME(UNIX_TIMESTAMP(logout_time), '%Y%m%d') = '20230310'
        AND (HOUR(logout_time) BETWEEN 12 AND 13)
) AS events

合并结果示例

第二步：计算累积用户状态

使用窗口函数累积计算用户状态。

SELECT
    room_id,
    user_id,
    user_type,
    SUM(user_type) OVER (PARTITION BY room_id ORDER BY event_time) AS status
FROM (
    -- 上一步的查询
) AS events

结果示例

第三步：求最大状态

通过聚合函数求每个房间的最大状态。

SELECT
    room_id,
    MAX(status) AS max_status
FROM (
    -- 上一步的状态查询
) AS status_calculation
GROUP BY
    room_id;

结果示例

解析

• 数据准备：合并登录和退出事件，标记事件类型。
• 计算状态：使用 SUM() OVER 计算累积状态。
• 求最大状态：通过 MAX(status) 获取最大用户状态。