大数据面试SQL每日一题系列：最高峰同时在线主播人数。字节，快手等大厂高频面试题

sql · 浏览次数 : 0

小编点评

## 衍生问题解答由于每个房间同时在线最大人数可能不同，因此无法直接从上面问题中直接获取每个房间同时在线最大人数。以下是一些衍生问题的解决方案： 1. 可以使用 `SUM` 函数对房间内所有用户的登录时间和登录次数进行累加，然后选择最大的登录次数的房间作为最大在线人数。 ```sql 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 ) b where room_id is not null group by room_id ``` 2. 可以使用 `GROUP BY` 和 `HAVING` 语句对房间内所有用户的登录时间和登录次数进行聚合，然后根据登录次数降序排列，选择最大的登录次数的房间作为最大在线人数。 ```sql select room_id, max(acum_login) as max_acum_login from ( select id, room_id, log_time, flag, sum(flag) over(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, flag ) b having max(acum_login) is not null ``` 3. 可以使用 `Window Functions` 来计算每个房间的在线人数窗口最大值，然后选择最大的窗口值作为最大在线人数。 ```sql select room_id, max(window_max(flag)) over(order by log_time) as max_acum_login from ( partition by room_id order by log_time window_func() over (order by log_time) as window_max ) b where room_id is not null ```

正文

之后会不定期更新每日一题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

就不补充具体数据演示了。

思路：以第一个问题为基础，这里只是多增加了一个房间维度，按房间分组进行开窗聚合累积计算以及最后的分组求最大值。如有问题，欢迎联系我点击此处加群一起学习讨论。

以上，本期全部内容。

感谢阅读。

按例，欢迎点击此处关注我的个人公众号，交流更多知识。