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

with t1 as(select artical_id,dt,sum(uv)over(partition by artical_id order by dt,uv desc) as uv_cnt
from
(select uid,artical_id,in_time as dt,1 as uv from tb_user_log
union all
select uid,artical_id,out_time as dt,-1 as uv from tb_user_log) as a
where artical_id <> 0)

select artical_id,max(uv_cnt) as max_uv from t1
group by artical_id
order by max_uv desc

计算实时流量，首先用union all连接得到在某一时刻观看和离开人数的表，然后按照文章排序并且按照时间排序，由于题目要求是同一时刻先进后出，所以加了一个uv desc，同一时刻按照倒序排列，最后按照文章id聚合计算即可。