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

这题和SQL 8思路一致

1. 将in_time 和 out_time用UNION ALL并集，利用窗口函数计算每个时间段的瞬时uv

select course_id,
	max(uv) as max_uv 
    from (
      select course_id,
        	sum(inc) over (partition by course_id order by dt ,inc desc) as uv 
        	from (
            	select course_id,in_datetime dt, 1 inc 
            	from attend_tb 
            	union all
            	select course_id,out_datetime dt, -1 inc
            	from attend_tb
        	) temp
    	) temp2
    	GROUP BY course_id

2. 和course_tb关联获取course_name，将结果按照course_id升序排列

select course_id, 
    course_name, 
    max_uv from (
        select course_id,max(uv) as max_uv from (select course_id,
        sum(inc) over (partition by course_id order by dt ,inc desc) as uv 
        from (
            select course_id,in_datetime dt, 1 inc 
            from attend_tb 
            union all
            select course_id,out_datetime dt, -1 inc
            from attend_tb
        ) temp
    ) temp2
    GROUP BY course_id
    ) temp3 
    inner join course_tb 
    using(course_id) 
    order by course_id