题解 | #牛客直播各科目同时在线人数#
牛客直播各科目同时在线人数
https://www.nowcoder.com/practice/d69677e41f9a4bf3b3ed7a42573e9490
select course_id,course_name,max(curnum) max_num from( select course_id,sum(diff) over(partition by course_id order by dt,diff desc) curnum from( SELECT in_datetime dt,1 diff,course_id from attend_tb union all SELECT out_datetime dt,-1 diff,course_id from attend_tb )t1 )t2 join course_tb using(course_id) group by course_id,course_name
- 查出课程人数变更表
SELECT in_datetime dt,1 diff,course_id
from attend_tb
union all
SELECT out_datetime dt,-1 diff,course_id
from attend_tb
- 窗口查出当前在线人数
sum(diff) over(partition by course_id order by dt,diff desc) curnum