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

/*
思路：
需要输出：文章ID、最大同时浏览数量
有表：行为表
需要用到字段：文章ID、进入时间、退出时间
步骤：
    ①生成2张表：进入时间记作+1表，退出时间记作-1表，两个表都要生成一个行为发生时间，然后自连接得到得到完整的行为表。
    ②在行为表基础上，使用窗口函数，对文章ID分组，对行为发生时间升序，访问数倒序，对访问人数求和，得出每个行为变动时间的人数，先计算进入再计算退出，确保每次有人进入都会统计一次同时访问人数。
    ③对文章分组后，用MAX或者窗口函数取出最大同时访问数。
*/

WITH                        -- 使用CET格式
xw AS (                     -- 生成完整的用户行为表
    SELECT
        artical_id,
        in_time AS sj,      -- 行为发生时间（进入）
        1 AS uv             -- 每有次有进入记作访问人数+1
    FROM tb_user_log
    UNION ALL               -- 全量自连接进入表和退出表
    SELECT
        artical_id,
        out_time AS sj,     -- 行为发生时间（退出）
        -1 AS uv            -- 每有次有退出记作访问人数-1
    FROM tb_user_log
),

ts AS (                     -- 生成同时同时浏览人数看板，显示每次访问人数发生变化时的同时浏览数
    SELECT
        artical_id,
        SUM(uv) OVER (PARTITION BY artical_id ORDER BY sj ASC,uv DESC) AS ll
        # 使用窗口函数，对文章ID分组，对行为发生时间升序（访问数随时间变化），访问数倒序（确保如果在同一时间有进入也有退出，则先计算进入），对访问人数求和（得出正在访问的人数，即同时访问人数）
    FROM xw
    WHERE artical_id != 0   -- 筛除非文章内容页面的访问数据
)

SELECT
    artical_id,
    MAX(ll)                 -- 提取每篇文章的最大同时访问人数
FROM ts
GROUP BY artical_id         -- 按照文章ID分组
ORDER BY MAX(ll) DESC;      -- 按照最大同时访问数降序