sql笔记:直播间最大人数

DROP TABLE if exists SQL_13;
CREATE TABLE SQL_13(
   user_id INT,
   live_id INT,
   in_time DATETIME NOT NULL,
   out_time DATETIME NOT NULL
);
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0001,1,'2023-01-01 10:00:00','2023-01-01 11:00:00');
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0002,2,'2023-01-01 15:00:00','2023-01-01 16:30:00');
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0003,1,'2023-01-01 10:20:00','2023-01-01 10:30:00');
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0004,1,'2023-01-01 09:20:00','2023-01-01 11:30:00');
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0005,2,'2023-01-01 11:20:00','2023-01-01 14:30:00');
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0006,2,'2023-01-01 12:20:00','2023-01-01 15:30:00');
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0007,1,'2023-01-01 08:20:00','2023-01-01 09:30:00');
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0008,2,'2023-01-01 08:20:00','2023-01-01 12:30:00');
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0009,1,'2023-01-01 15:20:00','2023-01-01 19:30:00');
insert into SQL_13(user_id,live_id,in_time,out_time)
values (0010,2,'2023-01-01 09:20:00','2023-01-01 13:30:00');
select * from SQL_13;

思路:

首先转换成如下的表格:

代码是

select user_id,live_id,in_time as dt ,1 as cnt from sql_13 
UNION
select user_id,live_id,out_time as dt,-1 as cnt from sql_13
ORDER BY live_id,dt;

然后使用窗口函数，用live_id固定窗口，order by dt为浮动窗口对cnt进行累加。

with t1 as(
select user_id,live_id,in_time as dt ,1 as cnt from sql_13 
UNION
select user_id,live_id,out_time as dt,-1 as cnt from sql_13
ORDER BY live_id,dt)
select *,
sum(cnt) over(PARTITION BY live_id order by dt) as 人数
from t1;

最后只需要对live_id分组，求max(人数)即可。

with t1 as(
select user_id,live_id,in_time as dt ,1 as cnt from sql_13 
UNION
select user_id,live_id,out_time as dt,-1 as cnt from sql_13
ORDER BY live_id,dt),
t2 as(
select *,
sum(cnt) over(PARTITION BY live_id order by dt) as 人数
from t1)
select live_id,max(人数) from t2 GROUP BY live_id ;

参考b站讲解:
link