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

select artical_id,
max(instant_viewer_cnt) max_uv
from(
    select 
    artical_id,
    dt,
    sum(diff) over(partition by artical_id order by dt,diff desc) instant_viewer_cnt
    from (
        select 
            artical_id, in_time dt, 1 diff
        from tb_user_log
        where artical_id!=0
        union all #all:允许重复值
        select 
            artical_id,out_time dt, -1 diff
        from tb_user_log
        where artical_id!=0
        order by 1,2
    ) t1
)t2
group by 1
order by 2 desc

学习大佬的思路：

① 2表联合，分别有in_time和out_time并且升序，多设置一个diff字段，如果是in_time就是+1，out_time就是-1，代表人的进入和退出。 记住非文章页面artical_id≠0哦~

② 开窗函数，sum(diff)也就是统计人数，按照aritical_id分区，并且按照时间排序，人数(+1,-1)倒序，这里人数还要排序而且是倒序是因为优先计算进来的人，不然就会算错最大观看数。这样就能够统计随着时间变化，每个artical的观看人数变化啦

③ max(观看人数) + group by artical_id + order by max_uv desc：按照题目要求，取最大观看人数，以及按要求排序