牛客网 SQL大厂面试真题篇 SQL8 每篇文章同一时刻最大在看人数

描述

用户行为日志表tb_user_log

id	uid	artical_id	in_time	out_time	sign_cin
1	101	9001	2021-11-01 10:00:00	2021-11-01 10:00:11	0
2	102	9001	2021-11-01 10:00:09	2021-11-01 10:00:38	0
3	103	9001	2021-11-01 10:00:28	2021-11-01 10:00:58	0
4	104	9002	2021-11-01 11:00:45	2021-11-01 11:01:11	0
5	105	9001	2021-11-01 10:00:51	2021-11-01 10:00:59	0
6	106	9002	2021-11-01 11:00:55	2021-11-01 11:01:24	0
7	107	9001	2021-11-01 10:00:01	2021-11-01 10:01:50	0

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。

输出示例：

示例数据的输出结果如下

artical_id	max_uv
9001	3
9002	2

解释：10点0分10秒时，有3个用户正在浏览文章9001；11点01分0秒时，有2个用户正在浏览文章9002。

题解：

第一步：查看每篇文章每一时刻用户进入和离开的情况（进入记录1，离开记录-1，方便后面进行加和计算总的用户数）

select artical_id, in_time dt, 1 diff
from tb_user_log 
WHERE artical_id!=0
union all
select artical_id, out_time	 dt, -1 diff
from tb_user_log 
WHERE artical_id!=0

第二步： 计算每篇文章每个时刻的在看人数（先记录用户数增加再记录减少，所以窗口函数里要order by diff）

select artical_id,dt,sum(diff) over(partition by artical_id order by dt,diff desc) instant_view
from(
        select artical_id, in_time dt, 1 diff
        from tb_user_log 
        WHERE artical_id!=0
        union all
        select artical_id, out_time	 dt, -1 diff
        from tb_user_log 
        WHERE artical_id!=0
)t1

 第三步：按照文章ID查看最大在看人数

select artical_id,max(instant_view) max_uv
from(
    select artical_id,dt,sum(diff) over(partition by artical_id order by dt,diff desc) instant_view
    from(
            select artical_id, in_time dt, 1 diff
            from tb_user_log 
            WHERE artical_id!=0
            union all
            select artical_id, out_time	 dt, -1 diff
            from tb_user_log 
            WHERE artical_id!=0
    )t1
)t2                    
group by artical_id
order by max_uv desc