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

# 选出最大人数
select 
    artical_id,
    max(sum_p) max_ux
from(
#获取每个文章的用户开窗，根据时间排序，sum加flag字段的值（这个操作就可以算出当前时刻，在线人数）
select artical_id,
       # time,
        sum(flag) over(partition by artical_id order by time rows between unbounded preceding and current row) `sum_p`
from(
##把登录和登出先分离（union形成一个新表）
#登录操作，并且打标记为1
select 
    uid,
    artical_id,
    in_time `time`,
    1 `flag`
from tb_user_log
where artical_id!=0 and artical_id is not null
union
#登出操作，并且打标记为-1
select 
    uid,
    artical_id,
    out_time `time`,
    -1 `flag`
from tb_user_log
where artical_id!=0 and artical_id is not null)t1)t2
#分组条件
group by artical_id 
#按照最大人数降序
order by max_ux desc