第一次做到这个类型的题目,一定要收藏一下。 本题的关键点: 1一个时间戳表格,记录每个时间点读者的行为, 2同时根据题目先进后出,对sign进行降序排序 3搭配sum + 窗口函数(order by)是累计和,这也是关键点
select artical_id,max(uv) as max_uv
from (
select artical_id,time,sum(sign) over(partition by artical_id order by time ,sign desc) as uv
FROM
(select artical_id,in_time as time ,1 as sign
from tb_user_log log1 where artical_id!=0
union all
select artical_id,out_time as time ,-1 as sign
from tb_user_log log1 where artical_id!=0) tmp
order by artical_id,time,sign desc
) tmp2
group by artical_id
order by max_uv DESC