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

with t1 as(select artical_id,in_time,
sum(uv) over(partition by artical_id order by in_time,uv desc) uv_cnt
from
(SELECT uid,artical_id,in_time,1 uv FROM tb_user_log
UNION ALL
SELECT uid,artical_id,out_time,-1 uv FROM tb_user_log) a
where artical_id<>0)
select artical_id, max(uv_cnt) max_uv from t1 group by artical_id order by max_uv desc

• 难点是构建一个列表，进来的人加1，退出的减1
• 利用union all连接 两个表
• 筛选artical_id