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

select 
    artical_id,max(uvcnt) as max_uv
from (select 
    artical_id,in_time,uv,
    sum(uv) over(partition by artical_id order by in_time asc,uv desc) uvcnt
from (select uid,artical_id,in_time,1 as uv from tb_user_log 
union all 
select uid,artical_id,out_time,-1 as uv from tb_user_log ) t where artical_id != 0) t group by artical_id order by max_uv desc

1.首先把进入设为1 退出设为-1 根据union all 进行两个表的组合

2.窗口函数 计算artical_id的uv和 按照不同的时间进行排序

3.根据artical_id分组 找最大的uv和