之后会不定期更新每日一题sql系列。
SQL面试题每日一题系列内容均来自于网络以及实际使用情况收集，如有雷同，纯属巧合。
1.题目
**问题1：**如下为某直播平台各主播的开播及关播时间数据明细，现在需要计算该平台最高峰期同时在线的主播人数。
问题2：以下为某直播间用户上线与下线的时间数据明细，现求该直播间最高峰同时在线的用户人数。
以上两个问法为同一问题。
2.基础数据准备
create table if not exists temp.user_login_info (
`id` bigint comment '用户id',
`start_time` string comment '上线时间',
`end_time` string comment '下线时间'
) comment '用户上下线时间测试'
数据预览
| id | start_time | end_time |
|---|---|---|
| 1 | 2024-05-05 07:59:06 | 2024-05-05 08:57:54 |
| 2 | 2024-05-05 08:14:02 | 2024-05-05 08:51:32 |
| 3 | 2024-05-05 08:38:10 | 2024-05-05 08:38:28 |
| 4 | 2024-05-05 08:41:22 | 2024-05-05 08:42:03 |
| 5 | 2024-05-05 08:33:39 | 2024-05-05 08:52:19 |
| 6 | 2024-05-05 08:54:50 | 2024-05-05 08:56:07 |
| 7 | 2024-05-05 08:56:12 | 2024-05-05 08:57:42 |
| 8 | 2024-05-05 08:21:43 | 2024-05-05 08:21:48 |
| 9 | 2024-05-05 07:59:58 | 2024-05-05 08:13:42 |
| 10 | 2024-05-05 08:20:05 | 2024-05-05 08:29:42 |
3.问题分析
查询同时最大人数，考察的是对拉链转化为日志的处理方式以及聚合开窗函数的累积计算的使用。聚合开窗函数使用详见SQL窗口函数之聚合函数类
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
4.解题SQL
1.生成日志流水
对原始数据进行处理，生成主播上下线的日志流水数据，增加标记状态值（上线为1，下线为-1）。
-- 上播记录
select
id,
start_time as log_time,
1 as flag
from temp.user_login_info
union all
-- 下播记录
select
id,
end_time as log_time,
-1 as flag
from temp.user_login_info
数据结果如下：
| id | log_time | flag |
|---|---|---|
| 1 | 2024-05-05 08:57:54 | -1 |
| 2 | 2024-05-05 08:51:32 | -1 |
| 3 | 2024-05-05 08:38:28 | -1 |
| 4 | 2024-05-05 08:42:03 | -1 |
| 5 | 2024-05-05 08:52:19 | -1 |
| 6 | 2024-05-05 08:56:07 | -1 |
| 7 | 2024-05-05 08:57:42 | -1 |
| 8 | 2024-05-05 08:21:48 | -1 |
| 9 | 2024-05-05 08:13:42 | -1 |
| 10 | 2024-05-05 08:29:42 | -1 |
| 1 | 2024-05-05 07:59:06 | 1 |
| 2 | 2024-05-05 08:14:02 | 1 |
| 3 | 2024-05-05 08:38:10 | 1 |
| 4 | 2024-05-05 08:41:22 | 1 |
| 5 | 2024-05-05 08:33:39 | 1 |
| 6 | 2024-05-05 08:54:50 | 1 |
| 7 | 2024-05-05 08:56:12 | 1 |
| 8 | 2024-05-05 08:21:43 | 1 |
| 9 | 2024-05-05 07:59:58 | 1 |
| 10 | 2024-05-05 08:20:05 | 1 |
2.开窗函数聚合
对上下线日志流水进行开窗聚合累积计算且查看上下线明细。
select id,log_time,flag,sum(flag) over(order by log_time) as acum_login from (
-- 上播记录
select
id,
start_time as log_time,
1 as flag
from temp.user_login_info where id <= 10
union all
-- 下播记录
select
id,
end_time as log_time,
-1 as flag
from temp.user_login_info where id <= 10
) a
order by log_time
数据结果
| id | log_time | flag | acum_login |
|---|---|---|---|
| 1 | 2024-05-05 07:59:06 | 1 | 1 |
| 9 | 2024-05-05 07:59:58 | 1 | 2 |
| 9 | 2024-05-05 08:13:42 | -1 | 1 |
| 2 | 2024-05-05 08:14:02 | 1 | 2 |
| 10 | 2024-05-05 08:20:05 | 1 | 3 |
| 8 | 2024-05-05 08:21:43 | 1 | 4 |
| 8 | 2024-05-05 08:21:48 | -1 | 3 |
| 10 | 2024-05-05 08:29:42 | -1 | 2 |
| 5 | 2024-05-05 08:33:39 | 1 | 3 |
| 3 | 2024-05-05 08:38:10 | 1 | 4 |
| 3 | 2024-05-05 08:38:28 | -1 | 3 |
| 4 | 2024-05-05 08:41:22 | 1 | 4 |
| 4 | 2024-05-05 08:42:03 | -1 | 3 |
| 2 | 2024-05-05 08:51:32 | -1 | 2 |
| 5 | 2024-05-05 08:52:19 | -1 | 1 |
| 6 | 2024-05-05 08:54:50 | 1 | 2 |
| 6 | 2024-05-05 08:56:07 | -1 | 1 |
| 7 | 2024-05-05 08:56:12 | 1 | 2 |
| 7 | 2024-05-05 08:57:42 | -1 | 1 |
| 1 | 2024-05-05 08:57:54 | -1 | 0 |
3.计算最大在线人数
最后计算最大同时在线人数
select max(acum_login) as max_acum_login from (
select id,log_time,flag,sum(flag) over(order by log_time) as acum_login from (
select
id,
start_time as log_time,
1 as flag
from temp.user_login_info where id <= 10
union all
--下播记录
select
id,
end_time as log_time,
-1 as flag
from temp.user_login_info where id <= 10
) a
) b
数据结果
| max_acum_login |
|---|
| 4 |
最大在线人数为4。
5.衍生问题解答
如果是最上面的问题2,每个房间同时在线最大人数呢？
那它的写法应该是这样的。
select room_id,max(acum_login) as max_acum_login from (
select id,room_id
,log_time,flag
,sum(flag) over(partition by room_id order by log_time) as acum_login
from (
-- 上线记录
select
id,room_id,
start_time as log_time,
1 as flag
from temp.user_login_info where id <= 10
union all
-- 下线记录
select
id,room_id,
end_time as log_time,
-1 as flag
from temp.user_login_info where id <= 10
) a
) b
group by room_id
就不补充具体数据演示了。
思路：以第一个问题为基础，这里只是多增加了一个房间维度，按房间分组进行开窗聚合累积计算以及最后的分组求最大值。如有问题，欢迎联系我点击此处加群一起学习讨论。
以上，本期全部内容。
感谢阅读。
按例，欢迎点击此处关注我的个人公众号，交流更多知识。
