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

SELECT DISTINCT artical_id, 
MAX(num_per) OVER (PARTITION BY artical_id) AS max_uv
FROM(
    SELECT *,
    SUM(count_num) OVER (PARTITION BY artical_id ORDER BY timed, count_num DESC) AS num_per
    FROM(
        SELECT uid,artical_id, in_time AS timed, 1 AS count_num
        FROM tb_user_log
        WHERE artical_id != 0
        UNION
        SELECT uid,artical_id,out_time AS timed, -1 AS count_num
        FROM tb_user_log
        WHERE artical_id != 0
        ) AS t
    ) as t2
ORDER BY max_uv DESC

存档 又是一道优雅例题

1、in_time的时刻在看人数+1，out_time时刻在看人数-1

2、每一组artical_id内 对时间排序后 顺序的累加就可以算出每个时刻在看的人数

3、最后找出每组artical_id内最大的在看人数就是题目要求的同一时刻最大在看人数了

注意，要把uid一起筛选，不然union会被去除重复项，导致结果出错。