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

SELECT artical_id,MAX(UV) AS max_uv FROM 
(SELECT artical_id,uid,dt,
SUM(tag) OVER(PARTITION BY T1.artical_id ORDER BY dt ASC ,tag DESC ) AS UV
FROM 
(
    SELECT artical_id,uid,in_time AS dt,1 AS tag
    FROM tb_user_log
    WHERE artical_id !=0
    UNION
    SELECT artical_id,uid,out_time  AS dt,-1 AS tag
    FROM tb_user_log
    WHERE artical_id !=0
) AS T1) AS T2
GROUP BY artical_id
ORDER BY max_uv DESC;