题解 | #每篇文章同一时刻最大在看人数#
每篇文章同一时刻最大在看人数
https://www.nowcoder.com/practice/fe24c93008b84e9592b35faa15755e48
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)求累计和。