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

with log
as(
    select
    uid
    ,artical_id
    ,in_time dt
    ,1 is_in
    from tb_user_log
    where artical_id != 0 and artical_id != '0'

    union all

    select
    uid
    ,artical_id
    ,out_time dt
    ,-1 is_in
    from tb_user_log
    where artical_id != 0 and artical_id != '0'
)
select
artical_id
,max(uv) max_uv
from
(
    select
    artical_id
    ,dt
    ,sum(is_in)over(partition by artical_id order by dt, is_in desc) uv
    from log
) a
group by artical_id
order by max_uv desc