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

1.对表中in_time和out_time进行编码，in观看人数+1，out观看人数-1，进行两次select联立;

2.如果同一时刻有进入也有离开时，先记录用户数增加再记录减少,

故在order by层面，在遵循dt升序的同时，还要遵循先+1，再-1的原则，即diff desc；

select artical_id,max(uv) max_uv
from
(select artical_id, 
#使用sum窗口函数，按文章id维度，统计按时间戳升序的观看人数的变化
#在order by层面，在遵循dt升序的同时，还要遵循先+1，再-1的原则，即diff desc
sum(diff) over(partition by artical_id order by dt,diff desc) uv
from 
(#对表中in_time和out_time进行编码，in观看人数+1，out观看人数-1，进行两次select联立
select artical_id,in_time dt,1 diff
from tb_user_log
union all
select artical_id,out_time dt,-1 diff
from tb_user_log)t1
where artical_id!=0)t2
group by artical_id
order by max_uv desc
;