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

with t 
as(select uid
    ,artical_id
    ,in_time date_time
    ,1 as num
    from tb_user_log
    where artical_id <> 0 

    union all

    select uid
    ,artical_id
    ,out_time date_time
    ,-1 as num
    from tb_user_log
    where artical_id <> 0)

select artical_id
,max(uv) max_uv
from(select uid
    ,artical_id
    ,sum(num)over(partition by artical_id order by date_time,num desc) uv
    from t) t2
group by artical_id
order by max_uv desc
# 窗口函数中要加上num desc，确保同一时刻（有进入有退出时）先计算进入的人