Mysql 解法, 执行用时: 36ms, 内存消耗: 6384KB, 提交时间: 2022-01-01

select ct.course_id,ct.course_name,bt.max_num
from course_tb ct
join(
select course_id,max(at_num) as max_num
from(
select*, sum(num)over(partition by course_id order by course_id,at_time) as at_num
from(
select user_id,course_id,time(in_datetime) as at_time, 1 num 
from attend_tb 
union all
select user_id,course_id,time(out_datetime) as at_time, -1 num 
from attend_tb 
order by course_id,at_time
) a
) b
group by course_id
) bt
on ct.course_id=bt.course_id

Mysql 解法, 执行用时: 36ms, 内存消耗: 6476KB, 提交时间: 2022-01-22

select
e.course_id,
e.course_name,
max(cnt) max_num
from
(SELECT
d.course_id,
d.course_name,
sum(zaixian) over (partition by course_id order by dt,zaixian desc) cnt
from
(select
a.course_id,
a.course_name,
in_datetime dt,
1 zaixian
from
course_tb a left join attend_tb b on a.course_id=b.course_id
 
union all
select
a.course_id,
a.course_name ,  
out_datetime dt,
-1 zaixian
from
course_tb a left join attend_tb b on a.course_id=b.course_id
)  d) e
group by 1,2
order by 1

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2022-01-24

with t1 as (
    select
        user_id,
        course_id,
        in_datetime as on_time,
        1 as diff -- 进入直播间
    from attend_tb
    union
    select
        user_id,
        course_id,
        out_datetime as on_time,
        -1 as diff -- 离开直播间
    from attend_tb
),
t2 as (
    select
        course_id,
        sum(diff) over (partition by course_id order by on_time, diff desc) as num
    from t1
),
t3 as (
    select distinct
        course_id, num,
        max(num) over (partition by course_id) as max_num
    from t2
)
select
    t3.course_id, c.course_name, t3.max_num
from t3
left join course_tb c on c.course_id = t3.course_id
where num = max_num
order by course_id asc;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2022-01-25

select course_id, course_name,
max(ranks) as max_num
from
(
    select 
    a.course_id, course_name, 
    sum(flag) over(partition by a.course_id, course_name order by times, flag desc) as ranks
    from
    (
        select course_id, in_datetime as times, 1 as flag
        from attend_tb
        union all 
        select course_id, out_datetime as times, -1 as flag
        from attend_tb
    ) as a
    left join course_tb as b
    on a.course_id = b.course_id
) as c
group by course_id, course_name

Mysql 解法, 执行用时: 37ms, 内存消耗: 6416KB, 提交时间: 2021-12-19

-- 2021年12月18日

with attend_detail as  (
    select 
    course_id, active_time, sum(cnt) as cnt
    from (
        select course_id, in_datetime as active_time, 1 as cnt from attend_tb 
        union all
        select course_id, out_datetime as active_time, -1 as cnt from attend_tb 
    ) t
    group by course_id, active_time
)

select 
  t2.course_id as course_id
, c.course_name as course_name 
, t2.max_num as max_num
from (
    select 
    course_id, max(online_num) as max_num
    from (
        select 
          a1.course_id as course_id 
        , a1.active_time as active_time
        , ifnull(sum(a2.cnt),0) as online_num
        from attend_detail a1 
        left outer join attend_detail a2 
            on a1.course_id = a2.course_id 
            and a1.active_time >= a2.active_time 
        -- where a1.cnt = 1 -- 最大人数必然最早发生在进入的时间
        group by a1.course_id, a1.active_time
    ) t1
    group by course_id
) t2
inner join course_tb c 
    on t2.course_id = c.course_id 
order by course_id 
;