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

WITH temp AS (
    # 记录进入，记为1
    SELECT uid,artical_id,in_time line_time,1 uv FROM tb_user_log WHERE artical_id<>0
    UNION ALL
    # 记录退出，记为-1
    SELECT uid,artical_id,out_time line_time,-1 uv FROM tb_user_log WHERE artical_id<>0
),
temp1 as
(
SELECT artical_id,
    SUM(uv) over(partition by artical_id order by line_time,uv desc) sum_uv
FROM temp 
)
SELECT artical_id,Max(sum_uv) as max_uv
FROM temp1
GROUP BY artical_id
ORDER BY max_uv desc;

注意点：如果同一时刻有进入也有离开时，先记录用户数增加再记录减少 所以这里需要把人数也要进行排序，先加后减。