03 | 直播间最大在线人数

原创已于 2025-06-24 14:41:02 修改 · 1k 阅读

0 GEO检测

标签

#大数据 #数据仓库 #数据分析 #sql #面试

于 2025-06-24 14:21:09 首次发布

SQL面试真题详解专栏收录该内容

8 篇文章 ¥9.90 ¥99.00

订阅专栏

超级会员免费看

一、问题定义与业务场景

目标：统计每个直播间在任意时刻的最大同时在线人数。
数据特征：

用户进入直播间时记录in_datetime，离开时记录out_datetime。
同一用户可能多次进出同一直播间（需独立计算）。

应用场景：直播流量监控、服务器资源分配、主播绩效评估。

二、数据准备与建表语句

2.1 建表语句

CREATE TABLE live_events (
    user_id BIGINT COMMENT '用户ID',
    live_id INT COMMENT '直播间ID',
    in_datetime DATETIME COMMENT '进入时间',
    out_datetime DATETIME COMMENT '离开时间'
) COMMENT '直播间访问记录表';

2.2 样例数据

INSERT INTO live_events VALUES
(100, 1, '2025-06-01 19:05:00', '2025-06-01 19:25:00'),
(100, 1, '2025-06-01 19:30:00', '2025-06-01 19:53:00'),
(101, 1, '2025-06-01 19:15:00', '2025-06-01 20:30:00'),
(102, 1, '2025-06-01 19:20:00', '2025-06-01 19:40:00'),
(103, 2, '2025-06-01 21:01:00', '2025-06-01 22:00:00'),
(104, 2, '2025-06-01 21:05:00', '2025-06-01 21:30:00');

三、SQL解决方案

3.1 核心思路

事件标记：将进入记为+1，离开记为-1，合并为统一时间线。
累积求和：按直播间分区，按时间排序，实时计算在线人数。
峰值提取：取每个直播间的累积人数最大值。

3.2 完整代码

SELECT
    live_id,
    MAX(online_count) AS max_user_count
FROM (
    SELECT
        live_id,
        event_time,
        SUM(user_change) OVER (
            PARTITION BY live_id 
            ORDER BY event_time, user_change DESC
        ) AS online_count
    FROM (
        -- 进入事件（标记+1）
        SELECT 
            user_id, 
            live_id, 
            in_datetime AS event_time, 
            1 AS user_change
        FROM live_events
        UNION ALL
        -- 离开事件（标记-1）
        SELECT 
            user_id, 
            live_id, 
            out_datetime AS event_time, 
            -1 AS user_change
        FROM live_events
    ) combined_events
) cumulative_sum
GROUP BY live_id;

计算结果：

3.3 执行步骤详解

步骤	关键操作	作用	示例数据（live_id=1）
1. 合并事件	`UNION ALL` 合并进出记录	生成统一时间序列	`(in, 19:05:00, +1)` → 进入
			`(out, 19:25:00, -1)` → 离开
2. 累积求和	`SUM() OVER(PARTITION BY live_id)`	实时计算每个时间点在线人数	19:15:00：累计2人（连续进入）
3. 峰值提取	`MAX(online_count)`	取直播间内累积人数的最大值	直播间1的峰值：3人

四、边界情况与优化

4.1 边界场景处理

同时发生事件：
- 若同一毫秒既有进入也有离开，需确保离开事件优先计算（通过ORDER BY event_time, user_change DESC实现）。
- 例如：19:30:00同时进入1人离开1人，优先计算-1避免虚高。

4.2 性能优化方案

索引设计（MySQL/PostgreSQL）：

CREATE INDEX idx_live_time ON live_events(live_id, in_datetime, out_datetime);

数据倾斜处理（超大直播间）：对于不同查询引擎，设置相应的参数

SET spark.sql.shuffle.partitions=200;  -- 增加Shuffle分区数

4.3 变体问题扩展

全局最大在线人数（不分直播间）：

SELECT MAX(online_count) 
FROM cumulative_sum;

五、总结

核心方法：UNION ALL合并事件 + SUM() OVER()累积计算是最高效的解法。
面试陷阱：
- 事件同时性处理；
- 用户快速进出时的去重逻辑（样例数据中user_id=100在直播间1进出两次）。
真题参考：字节跳动、阿里等大厂高频考题，扩展问题常涉及“实时在线人数曲线”。