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

又是一道瞬时最大UV的题目，这种题目有固定的做法，我整理了一下：

1、首先要理解，最大瞬时UV一定发生在某个用户进入直播间的瞬间

2、当用户进入直播间，记录进入时间，且UV+1；当用户退出直播间，记录退出时间，且UV-1；然后union all连接，目的是构建出这样一张表：每一个进出时间点的 用户进出明细记录 。

with a as (
        select 
        df1.course_id,
        df2.course_name,
        df1.in_datetime as "状态变动时间",
        1 as "uv"
        from attend_tb as df1
        left join course_tb as df2
        on df1.course_id = df2.course_id
        UNION ALL 
        select 
        df1.course_id,
        df2.course_name,
        df1.out_datetime as "状态变动时间",
        -1 as "uv"
        from attend_tb as df1
        left join course_tb as df2
        on df1.course_id = df2.course_id
)

3、第2步结束后，利用窗口，按课程id分组，按时间线升序，计算每个时间点的瞬时UV

SELECT 
course_id,
course_name,
sum(uv) OVER(PARTITION BY course_id ORDER BY 状态变动时间 asc rows between unbounded PRECEDING and current row) as "num"
from a

4、再套一层查询，取瞬时UV最大值

SELECT course_id,course_name,max(num) as "max_num"  from (
        SELECT 
        course_id,
        course_name,
        sum(uv) OVER(PARTITION BY course_id ORDER BY 状态变动时间 asc rows between unbounded PRECEDING and current row) as "num"
        from a
) as t
group by course_id,course_name
order by course_id asc

完整代码

with a as (
        select 
        df1.course_id,
        df2.course_name,
        df1.in_datetime as "状态变动时间",
        1 as "uv"
        from attend_tb as df1
        left join course_tb as df2
        on df1.course_id = df2.course_id
        UNION ALL 
        select 
        df1.course_id,
        df2.course_name,
        df1.out_datetime as "状态变动时间",
        -1 as "uv"
        from attend_tb as df1
        left join course_tb as df2
        on df1.course_id = df2.course_id
)

SELECT course_id,course_name,max(num) as "max_num"  from (
        SELECT 
        course_id,
        course_name,
        sum(uv) OVER(PARTITION BY course_id ORDER BY 状态变动时间 asc rows between unbounded PRECEDING and current row) as "num"
        from a
) as t
group by course_id,course_name
order by course_id asc