同时在限人数(同时在线最大人数)

同时在线最大人数

问题:
现有直播间时间表, 记录用户id和直播间id,以及何时进入直播间,何时离开直播间
求个直播间最大同时在线人数是多少

1. 数据准备

-- 数据准备
-- user_id(用户编号), live_id(直播间编号), in_time(进入直播间时间), out_time(离开直播间时间)
WITH live_events AS (
SELECT * FROM (
  VALUES    (100, 1, '2023-11-21 19:00:00', '2023-11-21 19:28:00'),
            (100, 1, '2023-11-21 19:30:00', '2023-11-21 19:53:00'),
            (100, 2, '2023-11-21 21:01:00', '2023-11-21 22:00:00'),
            (101, 1, '2023-11-21 19:05:00', '2023-11-21 20:55:00'),
            (101, 2, '2023-11-21 21:05:00', '2023-11-21 21:58:00'),
            (102, 1, '2023-11-21 19:10:00', '2023-11-21 19:25:00'),
            (102, 2, '2023-11-21 19:55:00', '2023-11-21 21:00:00'),
            (102, 3, '2023-11-21 21:05:00', '2023-11-21 22:05:00'),
            (104, 1, '2023-11-21 19:00:00', '2023-11-21 20:59:00'),
            (104, 2, '2023-11-21 21:57:00', '2023-11-21 22:56:00'),
            (105, 2, '2023-11-21 19:10:00', '2023-11-21 19:18:00'),
            (106, 3, '2023-11-21 19:01:00', '2023-11-21 21:10:00')
) AS table_name(user_id, live_id, in_time, out_time)
)

2. 代码实现

-- 1. 将进入和离开直播间时间合成一列, 并做标记
SELECT 
      user_id, live_id, in_time AS in_out_time, 1 AS tab -- 1 代表有1人在,-1表示离开一人
FROM live_events
UNION ALL 
SELECT 
      user_id, live_id, out_time AS in_out_time, -1 AS tab -- 1 代表有1人在,-1表示离开一人
FROM live_events
;

user_id	live_id	in_out_time	tab
100	1	2023-11-21 19:00:00	1
104	1	2023-11-21 19:00:00	1
106	3	2023-11-21 19:01:00	1
101	1	2023-11-21 19:05:00	1
102	1	2023-11-21 19:10:00	1
105	2	2023-11-21 19:10:00	1
105	2	2023-11-21 19:18:00	-1
102	1	2023-11-21 19:25:00	-1
100	1	2023-11-21 19:28:00	-1
100	1	2023-11-21 19:30:00	1
100	1	2023-11-21 19:53:00	-1
102	2	2023-11-21 19:55:00	1
…	…	…	…

-- 2. 统计每个时间段, 同时在线人数
SELECT 
      user_id, live_id, in_out_time
    , SUM(tab) OVER(PARTITION BY live_id ORDER BY in_out_time ) AS user_count 
FROM (
    SELECT 
          user_id, live_id, in_time AS in_out_time, 1 AS tab -- 1 代表有1人在,-1表示离开一人
    FROM live_events
    UNION ALL 
    SELECT 
          user_id, live_id, out_time AS in_out_time, -1 AS tab -- 1 代表有1人在,-1表示离开一人
    FROM live_events
) a
;

user_id	live_id	in_out_time	user_count
100	1	2023-11-21 19:00:00	1
104	1	2023-11-21 19:00:00	2
101	1	2023-11-21 19:05:00	3
102	1	2023-11-21 19:10:00	4
102	1	2023-11-21 19:25:00	3
100	1	2023-11-21 19:28:00	2
100	1	2023-11-21 19:30:00	3
…	…	…	…

-- 3. 取出每个直播间, 同时在线最大人数
SELECT 
      live_id, MAX(user_count) AS max_user_count 
FROM (
    SELECT 
          user_id, live_id, in_out_time
        , SUM(tab) OVER(PARTITION BY live_id ORDER BY in_out_time ) AS user_count 
    FROM (
        SELECT 
              user_id, live_id, in_time AS in_out_time, 1 AS tab -- 1 代表有1人在,-1表示离开一人
        FROM live_events
        UNION ALL 
        SELECT 
              user_id, live_id, out_time AS in_out_time, -1 AS tab -- 1 代表有1人在,-1表示离开一人
        FROM live_events
    ) a
) b
GROUP BY live_id
;

live_id	max_user_count
1	4
2	3
3	2

end