MySQL求最大同时在线人数的一种解法

目录

• 题目地址
• 思路
• 代码

题目地址

https://www.nowcoder.com/practice/d69677e41f9a4bf3b3ed7a42573e9490

思路

将所有区间的开始时刻和结束时刻作为tick。
之后按照区间开始等于这个tick还是区间结束等于这个tick，来决定是+1还是-1。
之后sum() over()做前缀和。
得到在这些tick附近的同时在线人数。
对每一门课程求最大同时在线人数。

代码

with total_info as(
    select ct.course_id,ct.course_name,
    at.in_datetime as interval_start,
    at.out_datetime as interval_end
    from course_tb ct
    left join attend_tb at
    using(course_id)
)
,
# 准备前缀和
t2 as(
    select *,
    (case when tick=interval_start then +1 when tick=interval_end then -1 else 0 end) as cnt_change,
    (case when interval_start=tick then 1 when interval_end=tick then 2 else 0 end) as sort_util_flag  # 如果某个时刻，有n人进入，有m人出去，同时在线人数的变动为：n个+1，之后m个-1
    # (case when interval_start=tick then 2 when interval_end=tick then 1 else 0 end) as sort_util_flag  # 如果某个时刻，有n人进入，有m人出去，同时在线人数的变动为：m个-1，之后n个+1
    from
    (
        select interval_start as tick from total_info
        union 
        select interval_end as tick from total_info
    )tmp1 
    left join total_info
    on interval_start=tick or interval_end=tick
) 

select course_id,course_name,max(online_uv) as max_num from 
(
    select course_id,course_name,
    sum(cnt_change) over(partition by course_id order by tick,sort_util_flag) as online_uv
    from t2
) t3
group by course_id,course_name
order by course_id asc