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

select artical_id,max(person) max_uv
from(
select artical_id,
sum(diff) over (partition by artical_id order by dt,diff desc) as person#这个地方的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
order by 1,2) t1)t2
group by artical_id
order by max_uv desc