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

问题：

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

问题分解：

1. 如何判断同一时刻在看人数是难点

将in_time,out_time联立到一张表中，然后排序，in_time +1, out_time -1

select aritical_id,in_time as tt,1 as number
from tb_user_log
union all
select aritical_id,out_time as tt,-1 as number
from tb_user_log

2. 用聚类窗口函数求出同时浏览人数

sum(number)over(partition by artical_id order by scan_time,number desc)

where artical_id !=0 剔除非文章内容页浏览

select artical_id,sum(number)over(partition by artical_id order by scan_time,number desc) as comm_num
from (
	select artical_id,in_time as scan_time,1 number
	from tb_user_log
	union all
	select artical_id,out_time as scan_time,-1 number
	from tb_user_log
	) t1
where artical_id !=0

3. 汇总并排序

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