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

问题：
统计每个科目最大同时在线人数（按course_id排序）
思路：
1.给用户行为打标。进入则为1，出去为-1。

-- 对用户行为打标，创建uv表
with uv as(
select
   user_id,
   course_id,
   in_datetime as dt,
   1 as flag
from attend_tb
union all
select
   user_id,
   course_id,
   out_datetime as dt,
   -1 as flag
from attend_tb
)

2. 利用窗口函数对人数进行排序累加，统计每个时刻的在线人数。

elect 
    course_id,
    course_name,
    num
from(
    select 
        course_id,
        -- 实时在线人数
        sum(flag) over(partition by course_id order by dt) as num
    from uv
) t1
left join course_tb using(course_id)

3. 选出最大的在线人数，并按科目排序。

-- 对t2分组和排序，筛选最大在线人数，
select 
    course_id,
    course_name,
    max(num)
from t2
group by course_id,course_name
order by course_id

4. 合并带入结果

with uv as(
select
   user_id,
   course_id,
   in_datetime as dt,
   1 as flag
from attend_tb
union all
select
   user_id,
   course_id,
   out_datetime as dt,
   -1 as flag
from attend_tb
)

select 
    course_id,
    course_name,
    max(num)
from(
    select 
    course_id,
    course_name,
    num
    from(
    select 
        course_id,
        -- 实时在线人数
        sum(flag) over(partition by course_id order by dt) as num
    from uv) t1
    left join course_tb using(course_id)
) t2
group by course_id,course_name
order by course_id