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

【场景】：同时在线人数

【分类】：窗口函数、分组查询、多表连接

分析思路

难点： 1.窗口函数和分组查询要分开求解

（1）统计进出记录表

注：进入是增加一个在线人数，出去是减少一个在线人数

• [条件]：timestampdiff(minute,in_datetime,out_datetime) >= 10

• [使用]：union；使用1和-1标记进入、出去

（2）统计每个时刻的同时在线人数

• [使用]：窗口函数 sum() over

（3）统计每个科目最大同时在线人数（按course_id排序）

• [使用]：max() ; group by course_id,course_name; order by course_id

最终结果

select 查询结果 [课程号;课程名称;最大同时在线人数]
from 从哪张表中查询数据[多表]
group bu 分组条件 [课程号；课程名称]
order by 对查询结果排序 [按课程号升序排序];

扩展

前往查看： MySQL 在线人数 场景分析

求解代码

方法一

with子句

with
    main as(
        #进入是增加一个在线人数，出去是减少一个在线人数
        select
            user_id,
            course_id,
            in_datetime as datetime,
            1 as tag
        from attend_tb
        union
        select
            user_id,
            course_id,
            out_datetime as datetime,
            -1 as tag
        from attend_tb
    )
    ,main1 as(
        #统计每个时刻的同时在线人数
        select distinct
            course_id,
            course_name,
            sum(tag) over(partition by course_id order by datetime) as sum_num
        from course_tb
        left join main using(course_id)
    )
#统计每个科目最大同时在线人数（按course_id排序）
select
    course_id,
    course_name,
    max(sum_num) as max_num
from main1
group by course_id,course_name
order by course_id

方法二

多表连接

#统计每个科目最大同时在线人数（按course_id排序）
select
    course_id,
    course_name,
    max(sum_num) as max_num
from(
    select distinct
        course_id,
        course_name,
        sum(tag) over(partition by course_id order by datetime) as sum_num
    from course_tb
    left join(
        #进入是增加一个在线人数，出去是减少一个在线人数
        select
            user_id,
            course_id,
            in_datetime as datetime,
            1 as tag
        from attend_tb
        union
        select
            user_id,
            course_id,
            out_datetime as datetime,
            -1 as tag
        from attend_tb
    ) main using(course_id)
) main1
group by course_id,course_name
order by course_id