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

# 指标：每篇文章同一时刻最大在看人数
# 1.某一时刻在看人数
# 1.1 编码+联立（注意别用union联立，会自动去重）
# SELECT artical_id,in_time,1 diff
# from tb_user_log
# where artical_id!=0
# union all
# SELECT artical_id,out_time,-1 diff
# from tb_user_log
# where artical_id!=0

# 1.2 窗口函数累加 
# select artical_id,in_time
# ,sum(diff)over(PARTITION by artical_id order by in_time)
# from (
#   SELECT artical_id,in_time,1 diff
#   from tb_user_log
#   where artical_id!=0
#   union all
#   SELECT artical_id,out_time,-1 diff
#   from tb_user_log
#   where artical_id!=0) as t1
# 1.3 瞬时统计时遵循【先进后出】：如果同一时刻有进入也有离开时，先记录用户数增加，再记录减少
# 因此在ORDER BY层面，在遵循dt升序的同时，还要遵循先+1，再-1的原则，即diff DESC

# 2.每篇文章最大在看人数
select artical_id,max(instant_viewer_cnt) max_uv
from (
  select artical_id,dt
  ,sum(diff)over(PARTITION by artical_id order by dt,diff desc) instant_viewer_cnt
  from (
    SELECT artical_id,in_time dt,1 diff
    from tb_user_log
    where artical_id!=0
    union all
    SELECT artical_id,out_time dt,-1 diff
    from tb_user_log
    where artical_id!=0) as t1
) as t2
group by artical_id
order by max_uv desc

这里参考@BLAcKSQAoO 的代码，再次表示感谢