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

# 1.将开始时间和结束时间视作相同时间列，分别给予标签1和-1表示在看人数的增加和减少。
# 2.SUM窗口函数，按文章id维度，统计按时间戳升序的观看人数变化情况
# 3.题目要求在瞬时统计时遵循【先进后出】,因此在ORDER BY层面，在遵循时间戳dt升序的同时，还要遵循先+1再-1的原则，即diff DESC

with t1 as
(select 
    artical_id,
    uid,
    in_time as dt ,
    1 as diff 
from tb_user_log 
where artical_id >0
union all
select artical_id,
    uid,
    out_time as dt ,
    -1 as diff 
from tb_user_log 
where artical_id >0),

t2 as
(select 
    artical_id,
    SUM(diff) over(Partition by artical_id order by dt,diff desc) as cnt 
from t1)

select 
    artical_id,
    max(cnt) as max_uv
from t2
group by artical_id
order by max_uv desc