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

select artical_id, max(num) as max_uv from (
select artical_id, dt, sum(num) over(partition by artical_id order by dt, num desc) as num from(
(    select artical_id, in_time as dt, 1 as num from tb_user_log
    where artical_id != 0)
    union all
(    select artical_id, out_time as dt, -1 as num from tb_user_log
    where artical_id != 0)
)t
)t1
group by artical_id
order by max_uv desc

🌟🌟🌟同一时刻在看的最大人数

🌟将一条记录按照进入时间和退出时间拆分为2条，用UNION ALL连接，不能用UNION❗️

注意union的两个部分都需要用( )包含起来

进入记录标记为num=1，表示该时刻观看人数+1

退出记录标记为num=-1，表示该时刻观看人数-1

则同一时刻在看的最大人数为 sum(num) over(partition by article_id order by time, num desc)

根据题目要求，统一时间的进入和退出记录需要先+1再-1，所以必须限制order by num

两个排序字段缺一不可❗️