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

# 思路：
# 1、将进入的人和出的人分别用1和-1表示，进入则在线人数+1，退出则在线人数-1
# 2、用union 将进入和退出的记录合并，生成一张总表，表示每个时间点进入和退出的情况
# 3、针对每条记录，计算截至当前的人数总和，取最高。
# 备注：注意同一时间段进入退出都存在，则先算进入，后算退出，因此计算sum时要将进入排在退出前。
select t2.artical_id,max(sum_num) FROM (
select t1.artical_id
    ,sum(t1.diff) over(partition by t1.artical_id  order by t1.dt asc,t1.diff desc) as sum_num 
from (
select artical_id,in_time as dt,1 as diff 
from tb_user_log where artical_id != 0 
union ALL
select artical_id,out_time as dt,-1 as diff 
from tb_user_log where artical_id != 0 ) t1 
) t2 group by t2.artical_id order by max(t2.sum_num) desc