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

with t1 as (
    select artical_id,in_time as event_time ,1 as user_type from tb_user_log where artical_id !=0
    union all
    select artical_id,out_time as event_time ,-1 as user_type from tb_user_log where artical_id !=0
)
select t2.artical_id,max(status)max_uv
from(
    select t1.artical_id,
       sum(user_type)over(partition by artical_id order by event_time,user_type desc)status
from t1
)t2
group by artical_id
order by max_uv desc

1.计算同一时刻最大在看人数，重点在于对用户进入直播间和退出直播间的行为打标签，进入直播间记作1，退出直播间记作-1.

2.首先将in_time和out_time查出来，并根据是in_time还是out_time分别记为1和-1，用union all联立得到表t1。

3.接下来对user_type用sum()over()函数开窗求和，所求得就是在看人数，然后再对sum得到的结果求max，所得即为最大的在看人数，记得根据artical_id分组、根据max_uv降序排序。