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

# 作答时间：20240417 14:52 ~ 
# 字段： course_id、course_name、max_num
# 问题类型：最大在线人数
# 方法：①union all  in_time和out_time;②sum(label)over()按照课程分组、time(升)和label(降)排序;③max筛选最大在线人数
# tb1：union all
with tb1 as (
    select user_id,course_id,course_name,in_datetime as inout_time,1 as label
    from attend_tb left join course_tb using(course_id)
    union all 
    select user_id,course_id,course_name,out_datetime as inout_time,-1 as label
    from attend_tb left join course_tb using(course_id)
),
# tb2：累计在线人数统计
tb2 as(
    select course_id,course_name,sum(label) over(partition by course_id order by inout_time,label desc) as sum_uv
    from tb1
)
# 最大在线人数max统计
select course_id,course_name,max(sum_uv)
from tb2
group by course_id,course_name
order by course_id