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

思路：

1. 先对用户行为打标，进入为1，离开为-1

• 进入表
• 出去表
• union all联结两表

2. 按照时间，顺序，累加用户状态，可得到用户同时在线人数。

• 同时在线人数
  • sum(tag) over(partition by artical_id order by dt,tag desc) -- 先记录增加，后记录减少，所以要tag desc
  • 条件，artical_id!=0
• 取最大，并排序
  • artical_id 分组
  • 排序

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