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

SELECT artical_id, max(cnt) as max_uv
FROM
(
    SELECT artical_id, uid, tms, status,
    SUM(status) over(partition BY artical_id ORDER BY tms ASC,status desc) as cnt
    FROM(
         SELECT artical_id, uid, in_time as tms,1 as status
         FROM tb_user_log
         UNION ALL
         SELECT artical_id, uid, out_time as tms,-1 as status
         FROM tb_user_log
    ) as tmp
)as tmp
WHERE artical_id !=0
GROUP BY artical_id
ORDER BY max_uv desc