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

# 每篇文章同一时刻最大在看人数，想象固定一个门，观察发生浏览人数变化的时刻点统计其进入和离开的人数
# （1）将各记录分成两个，一个记录进入时间，一个记录离开时间，然后联合两表
with t1 as
(select artical_id, in_time tag_time, 1 tag
from tb_user_log
where artical_id <> 0
union all
select artical_id, out_time tag_time, -1 tag
from tb_user_log
where artical_id <> 0)

# （2）使用窗口函数计算对每篇文章而言各个浏览人数发生变化的时刻点的在看人数，然后计算每篇文章同一时刻最大在看人数
select artical_id, max(current_num) max_uv
from (
    select artical_id, sum(tag)over(partition by artical_id order by tag_time, tag desc) current_num
    from t1
) t2
group by artical_id
order by max_uv desc;