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

#时间戳法解题，分别标记进和出的时间，按时间排序逐个相加
select artical_id,max(view_cnt)  max_uv
from 
(
select artical_id,
sum(cnt) 
over(partition by artical_id order by artical_id,check_time,cnt desc) 
# 整道题的坑体现在这里，要求先加后减 故对时间升序，时间相同时对结果降序 cnt desc
view_cnt
from
(
    select artical_id,in_time as check_time,1 as cnt
    from tb_user_log
    union all
    select artical_id,out_time,-1
    from tb_user_log
)t1
where artical_id<>0
)t2
group by artical_id
order by max_uv desc