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

感觉再次写复杂了

思路如下：

1.打标签，在用户进来的时间点和离开的时间点分别打上1和-1的标签，代表1人进入，1人离开。得到table1

select user_id,course_id,in_datetime time2, 1 diff
from attend_tb
union all 
select user_id,course_id,out_datetime time2, -1 diff
from attend_tb

如下结果：

2.累加每个课程不同时间点的人口变动情况。用sum()over()，得到table2

select course_id,sum(diff)over(partition by course_id order by time2) cumulate_num
from()table1

3.联结course_tb取出course_name，然后group by course_id和course_name，max()出峰值人数

select a.course_id,b.course_name,max(a.cumulate_num)
from
()table2
join course_tb b on a.course_id=b.course_id
group by a.course_id,b.course_name
order by a.course_id

完整代码

select a.course_id,b.course_name,max(a.cumulate_num)
from
(select course_id,sum(diff)over(partition by course_id order by time2) cumulate_num
from(
select user_id,course_id,in_datetime time2, 1 diff
from attend_tb
union all 
select user_id,course_id,out_datetime time2, -1 diff
from attend_tb
)a
)a
join course_tb b on a.course_id=b.course_id
group by a.course_id,b.course_name
order by a.course_id