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

【场景】：同一时刻在线人数

【分类】：窗口函数、tag标记、嵌套子查询

分析思路

难点：

1.同一时刻的在线人数 区别于 某个时刻之前的在线人数

（1）用tag标记增加还是减少

进入增加1，出去减少1

• [使用]：union；1 as tag；-1 as tag

（2）统计每个时刻的在看人数

如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，所以在窗口函数中使用sum()需要order by dt,tag desc

• [使用]：sum(tag) over (partition by artical_id order by dt,tag desc)

（3）求在看人数的最大值

• [使用]：group by exam_id；order by uv desc,avg_score

扩展

前往查看： MySQL 在线人数 场景分析

求解代码

方法一：

with 子句

with
    main as(
        #用tag标记增加还是减少
        (select
            artical_id,
            uid,
            in_time as dt,
            1 as tag
        from tb_user_log
        where artical_id != 0)
        union
        (select
            artical_id,
            uid,
            out_time as dt,
            -1 as tag
        from tb_user_log
        where artical_id != 0)
    ),
    main1 as(
        #如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，所以在窗口函数中使用sum()需要order by dt,tag desc
        select
            artical_id,
            sum(tag) over (partition by artical_id order by dt,tag desc) as uv
        from main
    )
#统计每篇文章同一时刻最大在看人数，结果按最大人数降序 
select
    artical_id,
    max(uv) as max_uv
from main1
group by artical_id
order by max_uv desc

方法二：

嵌套子查询 + from子查询

#统计每篇文章同一时刻最大在看人数，结果按最大人数降序 
select
    artical_id,
    max(uv) as max_uv
from(
    #如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，所以在窗口函数中使用sum()需要order by dt,tag desc
    select
        artical_id,
        sum(tag) over (partition by artical_id order by dt,tag desc) as uv
    from(
        #用tag标记增加还是减少
        (select
            artical_id,
            uid,
            in_time as dt,
            1 as tag
        from tb_user_log
        where artical_id != 0)
        union
        (select
            artical_id,
            uid,
            out_time as dt,
            -1 as tag
        from tb_user_log
        where artical_id != 0)
    ) main
) main1
group by artical_id
order by max_uv desc