题解 | #每篇文章同一时刻最大在看人数#

考查编码和窗口函数的巧妙使用

具体思路: 根据观看进入时间, 和退出时间进行编码,

select ul.artical_id,
    ul.in_time as view_time,
    1 as `code`
from tb_user_log ul
where ul.artical_id != 0
# 这里需要使用union all, 可能会存在同一个视频观看时间相同的情况
select ul.artical_id,
    ul.out_time as view_time,
    -1 as `code`
from tb_user_log ul
where ul.artical_id != 0  

可得: 每个进入时刻的编码为1, 退出时的编码为-1, 那么此时就可以使用窗口函数, 对artical_id进行partition by, 然后根据时间列进行升序排序, 进行累加, 有人进入则+1, 有人退出则-1, 需要注意的是, 当同一时刻有人退出又有人进入时, 先计算进入的. 则还需要根据code 进行降序

select t2.artical_id,
    max(view_num) as max_uv
from (
    select t1.artical_id,
        t1.view_time,
        t1.code,
        sum(t1.code) over(partition by t1.artical_id order by t1.view_time, t1.`code` desc) as view_num
    from (
        select ul.artical_id,
            ul.in_time as view_time,
            1 as `code`
        from tb_user_log ul
        where ul.artical_id != 0
        # 这里需要使用union all
        union all
        select ul.artical_id,
            ul.out_time as view_time,
            -1 as `code`
        from tb_user_log ul
        where ul.artical_id != 0
    ) t1
) t2 
group by t2.artical_id
order by max_uv desc