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

--同时阅读文章的最大在线人数，刚开始真的挺烧脑子的，但是呢从另一个方面想，对进出者两个进行编码，随后进行窗口函数的聚合，不就可以求出最大的在线人数来嘛，但是又存在一个问题那就是同时进行的人有一个卡出去了呢，这个就得在窗口函数的排序中对编码进行降序排序，先统计同一时间内进入的再统计退出的
WITH base AS (  
    SELECT 
        artical_id,
        in_time AS time,
        1 AS uv
    FROM tb_user_log 
    WHERE artical_id <> 0

    UNION ALL

    SELECT 
        artical_id,
        out_time AS time,
        -1 AS uv
    FROM tb_user_log
    WHERE artical_id <> 0
),

tongji AS (
    SELECT 
        artical_id,
        time,
        uv,
        SUM(uv) OVER (PARTITION BY artical_id ORDER BY time, uv DESC) AS juhe 
    FROM base
)

SELECT 
    artical_id,
    MAX(juhe) AS max_uv
FROM tongji 
GROUP BY artical_id
ORDER BY max_uv DESC;