hivesql练习_同时在线人数问题

现有各直播间的用户访问记录表（live_events）如下，表中每行数据表达的信息为，一个用户何时进入了一个直播间，又在何时离开了该直播间。

user_id (用户id)	live_id (直播间id)	in_datetime (进入直播间的时间)	out_datetime (离开直播间的时间)
100	1	2021-12-1 19:30:00	2021-12-1 19:53:00
100	2	2021-12-1 21:01:00	2021-12-1 22:00:00
101	1	2021-12-1 19:05:00	2021-12-1 20:55:00

现要求统计各直播间最大同时在线人数，期望结果如下：

live_id <int> (直播id)	max_user_count <int> (最大人数)
1	4
2	3
3	2

 

解答：

with tt as (
  SELECT
  live_id, in_datetime dt, 1 flag --定义登入标识
  FROM
  live_events
  union all 
  SELECT
  live_id, out_datetime dt, -1 flag --定义登出标识
  FROM
  live_events
),
tt1 as(
  select live_id, dt, sum(flag) over(partition by live_id order by dt) user_count --当前时间的在线人数
  from tt
)
select live_id, max(user_count) max_user_count
from 
tt1
group by live_id