1
原始数据（一条记录有两个时间点）
2
        ↓
3
   UNION ALL 拆成两类事件
4
        ↓
5
  进入事件(+1, flag=1)  +  离开事件(-1, flag=2)
6
        ↓
7
  SUM(diff) OVER(PARTITION BY artical_id ORDER BY time, flag)
8
  滚动累加 → 每一行 = 那一时刻的实时在线数
9
        ↓
10
  MAX(uv) → 每篇文章峰值

1
WITH
2
-- 第一步：把每条记录拆成进入和离开两个事件
3
T1 AS (
4
    SELECT artical_id, in_time AS curr_time, 1 AS diff, 1 AS flag
5
    FROM tb_user_log
6
    WHERE artical_id != 0
7

8
    UNION ALL
9

10
    SELECT artical_id, out_time AS curr_time, -1 AS diff, 2 AS flag
11
    FROM tb_user_log
12
    WHERE artical_id != 0
13
),
14

15
-- 第二步：窗口函数滚动累加，得到每一时刻的实时在线人数
16
T2 AS (
17
    SELECT
18
        artical_id,
19
        SUM(diff) OVER (
20
            PARTITION BY artical_id
21
            ORDER BY curr_time, flag
22
        ) AS uv
23
    FROM T1
24
)
25

26
-- 第三步：取每篇文章的峰值
27
SELECT artical_id, MAX(uv) AS max_uv
28
FROM T2
29
GROUP BY artical_id
30
ORDER BY max_uv DESC;

1
-- ❌ 错误：两个 SELECT 都用了 in_time
2
SELECT artical_id, in_time AS curr_time, -1 AS diff, 2 AS flag
3

4
-- ✅ 正确：离开事件必须用 out_time
5
SELECT artical_id, out_time AS curr_time, -1 AS diff, 2 AS flag

1
原始数据 tb_user_log
2
        ↓
3
  ┌─────────────┐        ┌──────────────┐
4
  │  new_users  │        │  active_days │
5
  │  每个用户   │        │  每个用户    │
6
  │  首次登录日 │        │  每天活跃日  │
7
  └──────┬──────┘        └──────┬───────┘
8
         │                      │
9
         └─────── LEFT JOIN ─────┘
10
                 条件：同一用户
11
                 且活跃日 = 注册日+1
12
                      ↓
13
              GROUP BY 注册日
14
                      ↓
15
         次日活跃人数 / 当天新用户总数

1
WITH
2
-- 第一步：找每个用户的首次登录日期（新用户定义）
3
new_users AS (
4
    SELECT uid, MIN(DATE(in_time)) AS reg_date
5
    FROM tb_user_log
6
    GROUP BY uid
7
),
8

9
-- 第二步：找每个用户每天的活跃日期（含跨天处理）
10
active_days AS (
11
    SELECT uid, DATE(in_time) AS active_date
12
    FROM tb_user_log
13
    UNION
14
    SELECT uid, DATE(out_time) AS active_date
15
    FROM tb_user_log
16
)
17

18
-- 第三步：LEFT JOIN关联，计算次日留存率
19
SELECT
20
    n.reg_date AS dt,
21
    ROUND(COUNT(DISTINCT a.uid) / COUNT(DISTINCT n.uid), 2) AS uv_left_rate
22
FROM new_users n
23
LEFT JOIN active_days a
24
    ON n.uid = a.uid
25
    AND a.active_date = DATE_ADD(n.reg_date, INTERVAL 1 DAY)
26
WHERE DATE_FORMAT(n.reg_date, '%Y-%m') = '2021-11'
27
GROUP BY n.reg_date
28
ORDER BY n.reg_date;