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

SELECT
  artical_id,
  MAX(instant_viewer_cnt) max_uv
FROM (
  SELECT
    artical_id,
    SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC) instant_viewer_cnt
  FROM (
    SELECT 
      artical_id, in_time dt, 1 diff
    FROM tb_user_log
    WHERE artical_id != 0
    UNION ALL
    SELECT 
      artical_id, out_time dt, -1 diff
    FROM tb_user_log
    WHERE artical_id != 0) t1 
) t2
GROUP BY 1
ORDER BY 2 DESC;