大厂面试题-如何计算最大同时在线人数

标签：11 同时在线面试题 10 08 00 2024 大厂 id

1/题目背景

现有各直播间的用户访问记录表（live_events），表中每一条记录表示，一个用户何时进入了一个直播间，又在何时离开了该直播间。现请你统计各直播间最大同时在线人数。

2/数据准备

-- 创建 live_events 表
CREATE TABLE live_events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    room_id INT NOT NULL,
    enter_time DATETIME NOT NULL,
    leave_time DATETIME NOT NULL
);

-- 插入示例数据
INSERT INTO live_events (user_id, room_id, enter_time, leave_time) VALUES
(1, 101, '2024-08-10 10:00:00', '2024-08-10 10:15:00'),
(2, 101, '2024-08-10 10:05:00', '2024-08-10 10:20:00'),
(3, 101, '2024-08-10 10:10:00', '2024-08-10 10:25:00'),
(4, 102, '2024-08-10 10:05:00', '2024-08-10 10:30:00'),
(5, 102, '2024-08-10 10:15:00', '2024-08-10 10:35:00'),
(6, 103, '2024-08-10 10:00:00', '2024-08-10 10:05:00'),
(7, 103, '2024-08-10 10:03:00', '2024-08-10 10:10:00'),
(8, 104, '2024-08-10 10:10:00', '2024-08-10 10:15:00'),
(9, 104, '2024-08-10 10:12:00', '2024-08-10 10:20:00'),
(10, 104, '2024-08-10 10:18:00', '2024-08-10 10:25:00'),
(11, 101, '2024-08-10 10:20:00', '2024-08-10 10:30:00'),
(12, 101, '2024-08-10 10:25:00', '2024-08-10 10:35:00'),
(13, 102, '2024-08-10 10:20:00', '2024-08-10 10:40:00'),
(14, 103, '2024-08-10 10:15:00', '2024-08-10 10:25:00'),
(15, 103, '2024-08-10 10:20:00', '2024-08-10 10:30:00'),
(16, 104, '2024-08-10 10:25:00', '2024-08-10 10:35:00'),
(17, 101, '2024-08-10 10:30:00', '2024-08-10 10:45:00'),
(18, 102, '2024-08-10 10:35:00', '2024-08-10 10:50:00'),
(19, 104, '2024-08-10 10:30:00', '2024-08-10 10:40:00'),
(20, 101, '2024-08-10 10:40:00', '2024-08-10 10:50:00'),
(21, 103, '2024-08-10 10:40:00', '2024-08-10 10:55:00'),
(22, 102, '2024-08-10 10:45:00', '2024-08-10 10:55:00'),
(23, 103, '2024-08-10 10:50:00', '2024-08-10 11:00:00'),
(24, 104, '2024-08-10 10:50:00', '2024-08-10 11:00:00'),
(25, 102, '2024-08-10 10:50:00', '2024-08-10 11:05:00'),
(26, 101, '2024-08-10 10:55:00', '2024-08-10 11:05:00'),
(27, 101, '2024-08-10 11:00:00', '2024-08-10 11:15:00'),
(28, 102, '2024-08-10 11:00:00', '2024-08-10 11:10:00'),
(29, 104, '2024-08-10 11:00:00', '2024-08-10 11:10:00'),
(30, 103, '2024-08-10 11:00:00', '2024-08-10 11:15:00'),
(31, 101, '2024-08-10 11:10:00', '2024-08-10 11:20:00'),
(32, 102, '2024-08-10 11:10:00', '2024-08-10 11:25:00'),
(33, 103, '2024-08-10 11:15:00', '2024-08-10 11:25:00'),
(34, 104, '2024-08-10 11:20:00', '2024-08-10 11:30:00'),
(35, 101, '2024-08-10 11:20:00', '2024-08-10 11:30:00'),
(36, 102, '2024-08-10 11:25:00', '2024-08-10 11:35:00'),
(37, 103, '2024-08-10 11:25:00', '2024-08-10 11:40:00'),
(38, 104, '2024-08-10 11:30:00', '2024-08-10 11:40:00'),
(39, 101, '2024-08-10 11:30:00', '2024-08-10 11:40:00'),
(40, 102, '2024-08-10 11:35:00', '2024-08-10 11:45:00'),
(41, 103, '2024-08-10 11:40:00', '2024-08-10 11:50:00'),
(42, 104, '2024-08-10 11:40:00', '2024-08-10 11:50:00'),
(43, 101, '2024-08-10 11:40:00', '2024-08-10 11:55:00'),
(44, 102, '2024-08-10 11:45:00', '2024-08-10 12:00:00'),
(45, 103, '2024-08-10 11:50:00', '2024-08-10 12:00:00'),
(46, 104, '2024-08-10 11:50:00', '2024-08-10 12:00:00'),
(47, 101, '2024-08-10 11:55:00', '2024-08-10 12:05:00'),
(48, 102, '2024-08-10 12:00:00', '2024-08-10 12:10:00'),
(49, 103, '2024-08-10 12:00:00', '2024-08-10 12:15:00'),
(50, 104, '2024-08-10 12:00:00', '2024-08-10 12:10:00');

3/解题思路

事件时间点：每个用户的进入和离开直播间的时间都是一个事件。进入事件表示人数增加，离开事件表示人数减少。
- 进入和离开事件：每次用户进入时，在线人数增加1；每次离开时，在线人数减少1。
时间排序和累积计数：将所有事件按时间排序后，我们可以通过累积计数来获得任意时刻的在线人数。随着时间的推移，我们需要识别出某个时刻的最大在线人数。
- 累积在线人数：通过逐次遍历排序后的事件时间点，动态计算当前在线人数。
最终目标：找出那个时间点，这个时间点就是最大同时在线人数的发生时刻。
- 最大在线人数时刻：在累积在线人数的过程中，记录下在线人数达到最大值的时刻。

4/解答

--第一步，生成进入和离开直播间的每一个事件列成表
--其实可以看出每个直播间其人数发生变化的过程日志
WITH event_list AS(
	--进入直播间事件
	SELECT user_id
	,room_id
	,enter_time AS event_time
  ,1 AS online_status
  FROM live_events
  UNION ALL
  --退出直播间事件
  SELECT user_id
  ,room_id
  ,leave_time AS event_time
  ,-1 AS online_status
  FROM live_events
 ),
 --第二步，计算每个直播间每个时刻现存人数
 online_status AS(
	 SELECT room_id
	 ,event_time
	 ,online_status
	 ,SUM(online_status)OVER(PARTITION BY room_id ORDER BY event_time ASC) 
				AS current_online
	 --可能会存在同一时刻发生多个进或者出直播间的事件
	 --那么如果你选择优先计算进入直播间的，你可以
	 --SUM(online_status) OVER (PARTITION BY room_id ORDER BY event_time ASC,
																 -- online_status DESC) AS current_online
	 FROM event_list
	 ORDER BY room_id,event_time
	),
	--第三步，为求得的每个时刻的现存人数作排序
	rank_online AS(
	SELECT room_id
	,event_time
	,current_online
	,RANK() OVER( PARTITION BY room_id ORDER BY current_online DESC) AS rk
	--有可能会存在多个时刻存在相同的在线人数，那就可能会存在多条rk=1的记录
	--如果这个时候你需要在多个拥有相同在线人数的时刻中选择最早的那个时刻
	--RANK() OVER( PARTITION BY room_id ORDER BY current_online DESC,event_time) AS rk
	FROM online_status
	);
	SELECT room_id
	,event_time
	,current_online
	FROM rank_online
	WHERE rk = 1
	ORDER BY room_id;

图-最终结果

图-event_list

图-online_status

图-rank_online

5/总结

该类题有什么经典业务场景呢？
- 在线教育平台需要统计在某个时间点的在线学生人数，进一步分析用户的学习习惯和活跃时间段
- 电商平台在大促期间，如“双十一”、黑色星期五等，统计最大在线人数，进一步优化用户购买路径，提升转化率
- 在多人在线游戏中，统计最大同时在线玩家人数，结合在线人数高峰数据，可以进一步分析玩家活跃时间和游戏热度
在处理同一时刻最大在线人数的 SQL 查询中，关键步骤包括
- 生成事件列表：使用 UNION ALL 合并用户的进入和离开事件，形成一个统一的事件数据集
- 计算累计在线人数：利用 SUM() 窗口函数，按时间顺序累积计算每个房间的在线人数。
- 标识最大在线人数：通过 RANK() 标记每个房间在线人数最多的时刻。
  - 如果仅需要求最大在线人数值：可以使用 MAX() 函数。但需要注意，MAX() 函数无法直接获取最大值对应的具体时间点。如果要找到最大值出现的时刻，还需要结合子查询或连接来实现。
  - 如果想直接带出时间点或者查看各时刻在线人数的整体排序：可以使用 RANK()、DENSE_RANK() 或 ROW_NUMBER() 这三个排序窗口函数。不过，具体选择哪个函数，还是要根据业务需求来决定。RANK() 可以找到所有最大在线人数的时刻，整体排名不连续；DENSE_RANK() 也可以找出所有最大在线人数的时刻，整体的排名连续； ROW_NUMBER() ：如果需要区分每个时刻的唯一性，即使它们的在线人数相同。
- 筛选结果：选取 RANK() 为 1 的记录，即每个房间在线人数最多的时间点。

标签：11,同时在线,面试题,10,08,00,2024,大厂,id
From： https://blog.csdn.net/Yaoo415/article/details/142553514