例题:
每篇文章同一时刻最大在看人数
问题来源牛客
首先要将进入与离开分离,分别计数,再按时间排序即可
这里便于理解用with函数分别写各个表
with tmp1 as (
select artical_id,in_time dt, 1 num from tb_user_log where artical_id!=0
union
select artical_id,out_time dt, -1 num from tb_user_log where artical_id!=0
)
select artical_id,max(cnt) max_uv from
(select artical_id, sum(num) over(partition by order by dt asc, num desc) as cnt from tmp1) as a
group by artical_id order by max_uv desc