解题思路: 反向推导, 正向执行
反向推导
- 获取各科目最大同时在线人数, 需要计算各科目每一个瞬间的同时在线人数, 再进行筛选
- 计算各科目每个瞬间的同在在线人数, 只需要计算截止每个瞬间的上线人数减去下线人数即可
- 计算截止每个瞬间的上线和下线人数, 只需要生成一个用户上线和下线的总表, 再根据科目分组, 按照时间顺序叠加即可
正向执行
- 生成用户瞬间的上线和下线总表
- 根据科目分组, 按照时间顺序, 上线加1, 下线-1, 计算每个瞬间的在线人数
- 最后筛选出每个科目最大的同时在线人数
select course_id,
course_name,
max(num) as max_num
from (
select course_id,
sum(diff) over (partition by course_id order by dt, diff desc) as num
from (
select user_id,
course_id,
in_datetime as dt,
1 as diff
from attend_tb
union all
select user_id,
course_id,
out_datetime as dt,
-1 as diff
from attend_tb
) as a
) as b
join course_tb using(course_id)
group by course_id,
course_name
order by course_id