题解 | #牛客直播各科目同时在线人数#

with zb as (select ct.course_id,ct.course_name,in_datetime,out_datetime,1 as in_num,-1 as leave_num /*将进入时间标记为1,离开记-1*/
from course_tb ct
left join attend_tb at on ct.course_id=at.course_id
where date(course_datetime) = date(in_datetime)) /*建立一个要分割的表*/
select course_id,course_name,max(z_num) max_num
from
(select *,suM(in_num) over(partition by course_id order by in_datetime) z_num /*每科各时间段在线人数*/
from
(select course_id,course_name,in_datetime,in_num
from zb
union all /*上下连接,将时间连续,统计时间段在线人数*/
select course_id,course_name,out_datetime,leave_num
from zb) zb1) zb2
group by course_id,course_name
order by course_id