大數據面試SQL每日一題系列：最高峰同時線上主播人數。位元組，快手等大廠高頻面試題

大數據面試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 '用戶上下線時間測試'

數據預覽

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

數據結果如下：

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

數據結果

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 

數據結果

最大線上人數為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

就不補充具體數據演示了。

思路：以第一個問題為基礎，這裡只是多增加了一個房間維度，按房間分組進行開窗聚合累積計算以及最後的分組求最大值。如有問題，歡迎聯繫我點擊此處加群一起學習討論。

以上，本期全部內容。

感謝閱讀。

按例，歡迎點擊此處關註我的個人公眾號，交流更多知識。