大資料面試SQL每日一題系列：最高峰同時線上主播人數。位元組，快手等大廠高頻面試題
之後會不定期更新每日一題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
就不補充具體資料演示了。
思路：以第一個問題為基礎，這裡只是多增加了一個房間維度，按房間分組進行開窗聚合累積計算以及最後的分組求最大值。如有問題，歡迎聯絡我點選此處加群一起學習討論。
以上，本期全部內容。
感謝閱讀。
按例，歡迎點選此處關注我的個人公眾號，交流更多知識。