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

select
    artical_id,max(cnt) as max_uv
from
(
        select
            artical_id,
            sum(num) over (partition by artical_id order by dt,num desc ) as cnt
        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
        )t1
    )t2
group by artical_id
order by max_uv desc;

将用户的进入时间单独拎出来，同时记为1；离开时间单独拎出来，同时记为-1。

这样就聚合这两个表，按照时间排序，意思就是：进去一个加1，离开一个减1。

然后利用窗口函数对计数（1或者-1）求累计和。