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

#思路：
# 1.先得确定区间，从每个人的最小和最大区间算起作为目标区间，然后细分到秒
# 2.依次判定这个时刻该文章下是否在in_time和out_time时间内是的话赋值为1，走出去赋值为-1
# 3.进行累加，提取最大值
#问题降维，我直接统计这些时间点下的累加最大值，其他的时间都在这个区间内平滑移动
#诡异点是先+后-要排序进行累加
with t1 as
(
	select artical_id,in_time as time_tb,1 as tb from tb_user_log  where artical_id !=0
	union all
	select artical_id,out_time as time_tb,-1 as tb from tb_user_log where artical_id !=0
), t2 as
(
	select artical_id,time_tb,tb from t1 order by time_tb
), t3 as 
(
	select artical_id,
	sum(tb) over (partition by artical_id order by time_tb,tb desc) as uv
	from t2
)
select 
	artical_id,
	max(uv) as max_uv
from t3 
group by artical_id
order by max_uv desc