SELECT artical_id,max(cnt) AS max_uv
FROM(
    SELECT artical_id,SUM(num) OVER (PARTITION BY artical_id ORDER BY dt ASC,num DESC) AS cnt
    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
    ) AS a
) AS b
GROUP BY artical_id
ORDER BY max_uv DESC;

1）数据预处理

子查询 a 通过 UNION ALL 将用户的进入和退出行为合并：

  • 进入记录：in_time 作为时间点，num=1（表示在线人数 + 1）。
  • 退出记录：out_time 作为时间点，num=-1（表示在线人数 - 1）。

示例数据（某文章的用户行为）：

1001

2021-11-01 09:00

1

-- 用户 A 进入

1001

2021-11-01 09:30

1

-- 用户 B 进入

1001

2021-11-01 10:00

-1

-- 用户 A 退出

1001

2021-11-01 10:30

1

-- 用户 C 进入

（2）窗口函数计算实时在线人数

SUM(num) OVER (PARTITION BY artical_id ORDER BY dt ASC, num DESC) AS cnt
  • PARTITION BY artical_id：按文章分组，确保每个文章的计算独立进行。
  • ORDER BY dt ASC, num DESC：按时间升序排序，同一时间点下，退出记录（num=-1）优先处理（避免虚增在线人数）。
  • SUM(num)：累计当前行及之前所有行的 num 值，得到当前时间点的在线人数。

计算过程示例：

1001

2021-11-01 09:00

1

1

-- A 进入 → 1 人

1001

2021-11-01 09:30

1

2

-- B 进入 → 2 人

1001

2021-11-01 10:00

-1

1

-- A 退出 → 1 人

1001

2021-11-01 10:30

1

2

-- C 进入 → 2 人

（3）外层查询取最大值

SELECT artical_id, max(cnt) AS max_uv
FROM (...) AS b
GROUP BY artical_id
  • 对每个文章的所有实时在线人数（cnt）取最大值，得到该文章的历史在线峰值。

3. 关键细节

（1）排序逻辑

  • ORDER BY dt ASC, num DESC：确保同一时间点下，退出操作（num=-1）先被计算，避免在线人数虚增。例如，若用户同时进出，先处理退出再处理进入，人数保持不变。

窗口函数 SUM(num) OVER (...) 的核心作用是：通过累计用户进入（+1）和退出（-1）操作，动态计算每个时间点的在线人数，并最终取最大值作为文章的热度指标。