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

with sumList as (
    select uid,artical_id,in_time as time, 1 as uv from tb_user_log
    union all 
    select uid,artical_id,out_time as time, -1 as uv from tb_user_log
)
select artical_id,max(uv) as max_uv
 from (
select artical_id,time,sum(uv)over(partition by artical_id order by time asc, uv desc) as uv from sumList where artical_id like '900_' 
) t1 
group by artical_id
order by max_uv desc

本题有个难点在于，计算最大人数需要先+后减，也就是在某一个时间段中需要先+1 后-1，所以在over（）窗口函数中分区的顺序需先time asc,uv desc