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

WITH t1 AS (
    SELECT *
    FROM tb_user_log
    WHERE artical_id != 0
),
t2 AS (
    SELECT uid,
           artical_id,
           in_time,
           1 AS uv
           FROM t1
    UNION ALL
    SELECT uid,
           artical_id,
           out_time,
           -1 AS uv
    FROM t1
),
t3 AS (
    SELECT
    artical_id,
    in_time,
    uv,
    sum(uv) OVER (PARTITION BY artical_id ORDER BY in_time ASC,uv DESC ) AS sum_uv
    FROM t2
),
t4 AS (
    SELECT artical_id,
           max(sum_uv) AS max_uv
    FROM t3
    GROUP BY artical_id
    ORDER BY max_uv DESC
)
SELECT * FROM t4;

# 核心在于同时进入和同时出去时，需要先加后减！