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

#同一时间在线人数需要使用sum窗口函数统计每个视频按照时间累积的观看人数。需要先计算出每个时间段的在线人数和离开人数并合并
select
artical_id
,max(uv) max_uv
from
(
    select
    artical_id
    ,dt
    ,sum(diff)over(partition by artical_id order by dt,diff desc) uv#这里如果出现同一时间先记录增加再记录减少所以order by dt，diff desc
    from
    (
        select
        artical_id
        ,in_time dt
        ,1 diff
        from tb_user_log
        where artical_id != 0

        union all

        select
        artical_id
        ,out_time dt
        ,-1 diff
        from tb_user_log
        where artical_id != 0
    )table1
)table2
group by 1
order by 2 desc;