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

# 字段：artical_id ， max_uv   
# 想法：构建每一秒的观看行为记录

# “如果同一时刻有进入也有离开时，先记录用户数增加再记录减少”？


# tb1：构建 一篇文章多行时间  表
# 定义in_time时刻为用户进入，而out_time时刻为用户出去
with tb1 as(
    select artical_id,in_time as time ,1 as uv
    from tb_user_log
    where artical_id<>0
    union all 
    select artical_id,out_time as time,-1 as uv
    from tb_user_log
    where artical_id<>0
),

# 之后用sum()over()进行累加计算。。。
tb2 as(
    select artical_id, sum(uv) over(partition by artical_id order by time,uv desc) as sum_uv
    from tb1
)
# 注意点：uv的排序先要进入的1 然后才是出去的-1

# 筛选出最大的在线人数
# select * from tb2
select artical_id,max(sum_uv) as max_uv
from tb2
group by artical_id
order by max(sum_uv) desc