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

• 难点：瞬时计算
• 思路：
  • 找出所有用户观看的时刻（包含进入与离开，注意去重）作为时间轴
  • 计算每个时间点在观看的人数，即进入时刻不大于该时刻且离开时刻不小于该时刻
  • 对时间点和文章id分组汇总，计算出每个时刻点每篇文章的观看人数
  • 进一步对文章id分组汇总，计算出每篇文章在所有时刻中最大的观看人数

with tmp as
         ((select in_time as dt
           from tb_user_log
           group by in_time
           order by in_time
          )
          union
          (select out_time as dt
           from tb_user_log
           group by out_time
           order by out_time))

select t.artical_id,
       max(t.uv) as max_uv
from (
         select t1.dt, t2.artical_id, count(uid) as uv
         from tmp as t1
                  left join tb_user_log as t2 on t2.in_time <= t1.dt and t2.out_time >= t1.dt
         where t2.artical_id != 0
         group by t1.dt, t2.artical_id
     ) as t
group by t.artical_id
order by max_uv desc;