这题和SQL 8思路一致
- 将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
- 和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