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

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

1. 通过连接查询 列举用户的登入时间和登出时间 登入记为1 登出记为0 记作uv字段
2. 通过sum窗口函数 按文章分组，登入时间和uv 降序排序 进行累计
1. 按登入时间先排是确保按照时间
2. 按uv排序 是让为1的 先计算
3. 最后按照文章分组 筛选文章id大于0的