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

# t1 文章每时刻，用户进出记录，若cnt=1，用户进入，cnt=-1，用户离开
# t2 partition by artical_id 按时间累加用户进入数
# group by artical_id, 算同一时刻最大在看用户数
with t1 as ((select uid, artical_id, in_time my_time, 1 as cnt
from tb_user_log
where artical_id != 0 )
union all
(select uid, artical_id, out_time my_time, -1 as cnt
from tb_user_log
where artical_id != 0 )), t2 as (
select artical_id, sum(cnt) over(partition by artical_id order by my_time, cnt desc) as cnt_all
from t1)

select artical_id, max(cnt_all) max_uv
from t2
group by artical_id
order by max_uv desc