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

【问题分析】

对于不同的时间截点，有人登入有人登出，这个时候就存在一个同时在线人数高峰，我们要做的就是分作品找到这个时间高峰。

人数高峰时间不确定，其随着人的登入与登出在变动，登入人数增加，登出人数减少，一个自然的想法是对所有登入与登出时间编码，然后对时间截点分作品累加，然后再统计最大值出现的点即可。

【问题求解】

第一步：对登入与登出时间进行编码。

select 
	artical_id
	,in_time as time
	,1 as person_diff
from tb_user_log
where artical_id <> 0
union
select
	artical_id
	,out_time as time
	,-1 person_diff
from tb_user_log
where artical_id <> 0
	

第二步：对不同时间截点按照artical_id分组求和。

select
	artical_id
	,sum(person_diff) over(partition by artical_id order by time,person_diff desc) as max_uv
from (
select 
	artical_id
	,in_time as time
	,1 as person_diff
from tb_user_log
where artical_id <> 0
union
select
	artical_id
	,out_time as time
	,-1 person_diff
from tb_user_log
where artical_id <> 0
) t1

	

第三步：返回结果

select
	artical_id
	,max(max_uv) as max_uv
from (
select
	artical_id
	,sum(person_diff) over(partition by artical_id order by time,person_diff desc) as max_uv
from (
select 
	artical_id
	,in_time as time
	,1 as person_diff
from tb_user_log
where artical_id != 0
union all
select
	artical_id
	,out_time as time
	,-1 person_diff
from tb_user_log
where artical_id != 0
) t1
) t2
group by artical_id
order by max_uv desc
;