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

SELECT l2.artical_id,
       MAX(l2.uv_current_cnt) AS max_uv
FROM
    (SELECT l1.artical_id, 
            SUM(l1.uv) OVER (PARTITION by l1.artical_id 
            ORDER BY l1.current,l1.uv DESC) AS uv_current_cnt
    FROM(
            SELECT artical_id, in_time AS current, 1 AS uv
            FROM tb_user_log
            WHERE artical_id !=0
            UNION ALL
            SELECT artical_id, out_time AS current, -1 AS uv
            FROM tb_user_log
            WHERE artical_id !=0
        ) AS l1
    ) AS l2
GROUP BY l2.artical_id
ORDER BY max_uv DESC;