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

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