📊 案例一：游戏/直播场景下的“每分钟最大在线人数”（PCU）
背景：
这是腾讯游戏或字节直播（抖音直播）非常经典的面试题和实战场景。
数据表：live_stream_log（直播日志表），包含 user_id（主播ID）、start_time（开播时间）、end_time（下播时间）。
挑战：传统的 GROUP BY 无法直接计算“某一时刻”有多少人在线，因为直播是持续的时间段，而我们需要的是时间点上的并发量。
核心思路：
事件流展开法（差分法）
将“时间段”拆解为“时间点事件”：开播记为 +1，下播记为 -1。通过对时间排序后的累加和，得到实时在线人数。
1. 创建直播日志表
-- 1. 创建直播日志表
CREATE TABLE IF NOT EXISTS live_stream_log (
user_id BIGINT, -- 主播ID
start_time STRING, -- 开播时间 (格式: yyyy-MM-dd HH:mm:ss)
end_time STRING -- 下播时间 (格式: yyyy-MM-dd HH:mm:ss)
)
PARTITIONED BY (ds STRING) -- 分区字段，例如 '2026-03-27'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
2.加载数据
-- 2. 加载数据 (假设分区是 2026-03-27)
-- 注意：这里模拟的是原始日志，通常包含跨天的数据
INSERT INTO TABLE live_stream_log PARTITION (ds='2026-03-27')
VALUES
(101, '2026-03-27 23:50:00', '2026-03-28 00:10:00'), -- 跨天直播
(102, '2026-03-27 23:55:00', '2026-03-28 00:05:00'), -- 跨天直播
(103, '2026-03-28 00:01:00', '2026-03-28 00:06:00'); -- 仅在后一天
3.运行计算 SQL
WITH event_stream AS (
-- 1. 展开事件流
-- 核心逻辑：开播是+1，下播是-1
SELECT user_id, start_time as event_time, 1 as change_cnt
FROM live_stream_log
WHERE ds = '2026-03-27' -- 读取当天分区
UNION ALL
SELECT user_id, end_time as event_time, -1 as change_cnt
FROM live_stream_log
WHERE ds = '2026-03-27'
),
time_grouped AS (
-- 2. 按分钟聚合
-- 注意：这里会自动处理跨天的时间戳，因为时间戳是绝对的
SELECT
date_format(event_time, 'yyyy-MM-dd HH:mm') as time_minute,
SUM(change_cnt) as net_change
FROM event_stream
GROUP BY date_format(event_time, 'yyyy-MM-dd HH:mm')
),
online_stats AS (
-- 3. 计算实时在线人数 (窗口函数累加)
SELECT
time_minute,
SUM(net_change) OVER (ORDER BY time_minute) as current_online_cnt
FROM time_grouped
)
-- 4. 查询结果
SELECT
time_minute,
current_online_cnt
FROM online_stats
ORDER BY time_minute;
易出现问题
上述问题 出现数据倾斜：如果某个时间点（如整点）事件极其密集，会导致单个Reducer压力过大。在实际生产中，通常会先对时间进行“加盐”或预聚合。 举个案例 在字节或腾讯，遇到整点活动（比如 20:00 开播抢红包），数据量可能是平时的 100 倍，如果不处理，任务 100% 会卡在 99% 进度条不动。
###解决这个问题的核心思路是：“两阶段聚合”。
也就是：先加盐打散（局部聚合），再去盐汇总（全局聚合）
局部聚合:
concat(cast(floor(rand() * 10) as string), '_', event_time)或者concat(cast(rand() * 10 as int), '_', event_time) as salted_time_key
全局聚合:
split(salted_time_key, '_')[1]
数据倾斜加盐处理
WITH event_stream AS (
-- 1. 模拟原始数据（100条 20:00 的数据）
SELECT '20:00' as event_time, 1 as change_cnt FROM (SELECT 1) t1
-- 这里省略了 UNION ALL 的其他数据，假设这里有100行同样的
),
-- ==========================================
-- 第一阶段：加盐 + 局部聚合
-- ==========================================
stage1_local_agg AS (
SELECT
-- 【加盐】：给时间戳加上 0-9 的随机前缀
-- 比如：'20:00' 变成了 '0_20:00', '1_20:00' ... '9_20:00'
-- 这样原本相同的 Key 就变成了 10 个不同的 Key，被分发到不同的 Reducer
concat(cast(floor(rand() * 10) as string), '_', event_time) as salted_time_key,
SUM(change_cnt) as local_sum
FROM event_stream
GROUP BY concat(cast(floor(rand() * 10) as string), '_', event_time)
),
-- ==========================================
-- 第二阶段：去盐 + 全局聚合
-- ==========================================
stage2_global_agg AS (
SELECT
-- 【去盐】：把前缀切掉，还原成 '20:00'
split(salted_time_key, '_')[1] as time_minute,
SUM(local_sum) as net_change
FROM stage1_local_agg
GROUP BY split(salted_time_key, '_')[1]
)
-- 最终结果
SELECT * FROM stage2_global_agg;
扩展
上面是针对 所有主播的在线人数 ，要是改成 每个主播 里最大的游客在线人数
| live_id (直播间ID) |
user_id (用户ID) |
event_time (事件时间) |
event_type (事件类型) |
| 101 |
A |
10:00:00 |
enter |
| 101 |
B |
10:00:05 |
enter |
| 102 |
C |
10:00:01 |
enter |
| 101 |
A |
10:00:10 |
exit |
| 102 |
D |
10:00:15 |
enter |
核心点：
你只需要在原来的思路上，增加一个 PARTITION BY 字段，就能轻松实现从“全局”到“个体”的转换。这个思路非常通用，无论是计算每个游戏的PCU、每篇文章的实时阅读人数，都是同样的逻辑。
| 需求 |
核心SQL思路 |
关键区别 |
| 所有主播总PCU |
SUM(...) OVER (ORDER BY event_time) |
所有数据混在一起算一个总数。 |
| 每个主播PCU |
SUM(...) OVER (PARTITION BY live_id ORDER BY event_time) |
先按 live_id 分组，再在每个组内独立计算。 |
🛒 案例二：电商/广告场景下的“留存率与转化漏斗”
背景：
抖音电商或广告投放中，需要计算“新用户7日留存”或“点击->下单”转化率。
数据表：user_action_log（用户行为表），包含 user_id, action_type（点击/下单）, event_time。
挑战：涉及大表自关联或多次关联，数据量呈指数级膨胀，极易导致任务OOM。
核心思路：
对于留存分析，通常使用 ROW_NUMBER() 去重后，再通过自关联或条件聚合计算。
新用户次日留存率
WITH new_users AS (
-- 1. 找出每天的新增用户（去重）
SELECT
user_id,
TO_DATE(MIN(event_time)) as register_date
FROM user_action_log
WHERE action_type = 'login'
GROUP BY user_id
),
active_users AS (
-- 2. 关联行为表，找出次日活跃的用户
SELECT
nu.register_date,
COUNT(DISTINCT CASE
WHEN DATEDIFF(TO_DATE(ua.event_time), nu.register_date) = 1
THEN ua.user_id
END) as next_day_active,
COUNT(DISTINCT nu.user_id) as total_new
FROM new_users nu
-- 注意：这里通常使用 LEFT JOIN，且要注意数据倾斜
LEFT JOIN user_action_log ua ON nu.user_id = ua.user_id
WHERE ua.event_time >= nu.register_date
AND ua.event_time < DATE_ADD(nu.register_date, 2) -- 限制扫描范围，减少数据量
GROUP BY nu.register_date
)
SELECT
register_date,
total_new,
next_day_active,
next_day_active * 1.0 / total_new as retention_rate
FROM active_users;
需要注意的点：
1. 使用 left join 左表为新用户表 右变为日志全量表
因为我们要统计留存率。留存率的分母是“所有新用户”，inner join的话会丢失部分数据
2. WHERE ua.event_time >= nu.register_date AND ua.event_time < DATE_ADD(nu.register_date, 2)
如果不加这个条件会把所有的日志都关联进来这完全没必要，且极度消耗资源，我们只需要次日留新
3. DISTINCT CASE WHEN DATEDIFF(TO_DATE(ua.event_time), nu.register_date) = 1 THEN ua.user_id
这个条件必须放在select 中，不能放到where 条件中，否则 会把分母（新用户数量）过滤一些
留存几天
核心修改点：把 = 1 改成 BETWEEN 1 AND 7。
-- 修改前（次日）：
WHEN DATEDIFF(TO_DATE(ua.event_time), nu.register_date) = 1 THEN ua.user_id
-- 修改后（7日）：
WHEN DATEDIFF(TO_DATE(ua.event_time), nu.register_date) BETWEEN 1 AND 7 THEN ua.user_id
7日点击->下单转化率漏斗
WITH click_users AS (
-- 1. 找出点击过的人（作为基数）
SELECT DISTINCT user_id, event_time as click_time
FROM user_action_log
WHERE action_type = 'click'
AND TO_DATE(event_time) = '2026-03-27' -- 我们统计 3月27日 的点击
),
conversion_stats AS (
SELECT
COUNT(DISTINCT c.user_id) as click_users, -- 分母：点击人数
COUNT(DISTINCT CASE
WHEN o.user_id IS NOT NULL THEN c.user_id
END) as order_users -- 分子：7天内下单的人数
FROM click_users c
LEFT JOIN user_action_log o
ON c.user_id = o.user_id
AND o.action_type = 'order'
-- 【关键点】这里不再是同一天，而是点击后的 0 到 7 天
AND o.event_time >= c.click_time
AND o.event_time <= DATE_ADD(c.click_time, 7)
)
SELECT
click_users,
order_users,
order_users * 1.0 / click_users as conversion_rate_7d
FROM conversion_stats;
注意：
锁定具体某一天的点击，然后查后七天的下单转化
action_type = 'click' AND TO_DATE(event_time) = '2026-03-27'
📌 总结：大厂代码的“套路”
| 需求 |
核心逻辑 (CASE WHEN 里的条件) |
含义 |
| 次日留存 |
DATEDIFF(..., ...) = 1 |
找第2天的数据 |
| 7日留存 |
BETWEEN 1 AND 7 |
找第2~8天的数据 |
| 30日留存 |
BETWEEN 1 AND 30 |
找第2~31天的数据 |
| 点击->下单 |
action_type = 'order' |
找关联行为的数据 |
🎯 案例三：复杂指标排名与去重（窗口函数应用）
同一个时间维度下（2026-03-26），对全平台所有的游戏进行大比拼，排出一个“热门游戏榜单”。
📊 预期输出结果示例
| play_name |
dau |
dau_rank |
duration_rank |
total_score |
| 王者荣耀 |
10000 |
1 |
2 |
3 |
| 原神 |
8000 |
2 |
1 |
3 |
| 开心消消乐 |
5000 |
3 |
3 |
6 |
| ... |
... |
... |
... |
... |
按照日活和时长指标排名
WITH daily_stats AS (
-- 1. 基础指标聚合
SELECT
play_name,
COUNT(DISTINCT user_id) as dau, -- 活跃人数
SUM(play_duration) as total_duration
FROM game_play_log
WHERE ds = '2026-03-26'
GROUP BY play_name
),
ranked_stats AS (
-- 2. 计算排名
SELECT
play_name,
dau,
total_duration,
-- 参与率排名
DENSE_RANK() OVER (ORDER BY dau DESC) as dau_rank,
-- 时长排名
DENSE_RANK() OVER (ORDER BY total_duration DESC) as duration_rank
FROM daily_stats
)
-- 3. 最终输出
SELECT
play_name,
dau,
dau_rank,
duration_rank,
(dau_rank + duration_rank) as total_score -- 综合评分
FROM ranked_stats
ORDER BY total_score ASC
LIMIT 100;
DAU (日活跃用户)与新增用户数
核心点
DAU (日活跃用户数) ：COUNT(DISTINCT user_id) as dau
新增用户数：COUNT(DISTINCT CASE WHEN is_new_user = 1 THEN user_id END) as new_users
DAU (日活跃用户)与新增用户
SELECT
-- 1. 统计日期
TO_DATE(start_time) as stat_date,
-- 2. DAU (日活跃用户数)
-- 核心：COUNT(DISTINCT ...) 去重
COUNT(DISTINCT user_id) as dau,
-- 3. 新增用户数
-- 核心：在去重的基础上，只统计标记为新的用户
COUNT(DISTINCT CASE WHEN is_new_user = 1 THEN user_id END) as new_users,
-- 4. 人均启动次数 (粘性指标)
-- 核心：总启动次数 / DAU
COUNT(*) * 1.0 / COUNT(DISTINCT user_id) as avg_sessions_per_user
FROM game_start_log
WHERE ds = '2026-03-27' -- 分区过滤，只查这一天
GROUP BY TO_DATE(start_time);
📊 核心区别对比表
| 维度 |
DAU (日活跃用户) |
新增用户数 |
| 关注点 |
活跃度 (Activity) |
增长 (Growth) |
| 包含关系 |
大集合 (包含新用户 + 老用户) |
子集 (仅包含新用户) |
| 统计逻辑 |
只要今天登录/使用了，就算 1 |
必须是人生第一次登录/注册，才算 1 |
| 业务含义 |
产品现在的人气旺不旺 |
推广渠道拉人能力强不强 |
| 数据波动 |
相对稳定 (老用户基数大) |
波动极大 (取决于广告投放力度) |
浙公网安备 33010602011771号