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

明确题意：

统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少

问题分解：

计算人数变动情况：进入（select artical_id ,in_time dt, 1 uv）union 退出（select artical_id ,out_time dt, -1 uv）

计算瞬时在看人数：sum(uv) over(partition by artical_id order by dt, uv desc)

按文章分组：group by artical_id

计算最大在看人数：max(uv_cnt)

细节问题：

按最大人数降序：order by max_uv desc

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