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

-- “同一时刻有进入有离开时，先记录增加后记录减少” ，进入+1，退出-1，可以使用sum窗口函数，对文章进行分组，然后先日期升序，再对人数进行降序排列
select 
artical_id 
,max(uv) as max_uv 
from (
-- 使用sum窗口函数，根据文章id进行分组，对dt进行升序排列，计算对应的diff值 
select 
artical_id 
,sum(diff) over(partition by artical_id order by dt,diff desc) as uv 
from (
-- 先计算uid对应的进入时间和离开时间，并且进入+1.离开-1 
select 
artical_id
,in_time as dt 
,1 as diff 
from 
tb_user_log 
where 
artical_id <> 0 
union all
select 
artical_id
,out_time as dt 
,-1 as diff 
from 
tb_user_log 
where 
artical_id <> 0 ) a ) b 
group by artical_id 
order by 2 desc