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

with a as
(select uid,artical_id, in_time as dt,1 as flag
from tb_user_log where artical_id!=0
union 
select uid,artical_id, out_time as dt,-1 as flag
from tb_user_log where artical_id!=0)

select artical_id,max(sum_cnt) as max_uv
from
(select artical_id, sum(flag) over(partition by artical_id order by dt,flag desc) as sum_cnt
from a)b
group by artical_id
order by max_uv desc

三刷 select artical_id, sum(flag) over(partition by artical_id order by dt,flag desc) as sum_cnt

from a 红字易遗漏，sum_cnt求的是累计数，先进入、后退出，若少红字部分，会导致少算一个最大值