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

SELECT artical_id, MAX(total_num) max_uv FROM
(SELECT artical_id, SUM(num) over (partition by artical_id order by dt, num DESC) total_num
 FROM (SELECT artical_id, in_time AS dt, 1 AS num FROM tb_user_log WHERE artical_id!=0
      UNION ALL
      SELECT artical_id, out_time AS dt, -1 AS num FROM tb_user_log WHERE artical_id!=0) t1
 ) t2
GROUP BY artical_id
ORDER BY max_uv DESC

参考了题解区高赞的思路。

第一步，in_time观看人数增加，用+1记录；out_time观看人数减少，用-1记录。

第二步，按照时间顺序排序用窗口函数sum对人数进行累计。在这里一个要注意的点是，如果同一时刻有进入也有离开，先记录用户数增加，再记录减少。这意味着，要遵循先+1，再-1的原则，所以在窗口函数的ORDER BY中，要写为order by dt, num DESC。 “先+1再-1”和“先-1再+1”这两种不同计算方法会导致求最大在看人数的结果不同。“先+1再-1”的计算方法算出来的最大在看人数比“先-1再+1”算出来的大。