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

# 需要考虑到进入时间和离开时间，如果用户10s进入，11s离开，应该会被统计两次。需要进行union
select artical_id ,max(sum_uv) as max_uv 
from (
select artical_id,sum(diff) over(partition by artical_id order by dt ,diff desc) as sum_uv 
from (
select uid,artical_id,in_time as dt ,1 diff
from tb_user_log  
where artical_id != 0
union all
select uid,artical_id,out_time as dt ,-1 diff 
from tb_user_log  
where artical_id != 0  ) a 
) b 
group by artical_id 
order by max_uv desc

需要注意，先统计进入，再统计减少