SQL 装备强化失败次数统计：按用户+装备统计（网易面试题）
一、题目背景
这道题来自网易游戏事业部的数值策划与数据分析岗面试。装备强化是游戏中最经典的概率玩法——成功则战力提升，失败则材料打水漂。连续多次失败会导致玩家产生"被系统针对"的负面情绪，甚至愤怒删游。运营需要识别"非酋"玩家（连续失败次数异常多的玩家），及时进行安抚干预。
业务场景：运营后台每日扫描全服玩家的装备强化记录，标记出"最长连续失败 > 3 次"的玩家名单，自动发放"幸运符"补偿道具。这道题的连续状态分组逻辑就是该扫描任务的 SQL 实现。
二、题目
现有一张装备强化记录表 t5_enhance_log，记录了每个玩家每次强化装备的结果。请统计每个玩家的"最长连续强化失败次数"，以及连续失败次数超过 3 次的失败区段总数。
强化记录表 t5_enhance_log：
+------------+---------+----------------------+----------+
| player_id | equip | enhance_time | result |
+------------+---------+----------------------+----------+
| P001 | sword | 2024-01-01 08:00:00 | fail |
| P001 | sword | 2024-01-01 08:05:00 | fail |
| P001 | sword | 2024-01-01 08:10:00 | fail |
| P001 | sword | 2024-01-01 08:15:00 | success |
| P001 | sword | 2024-01-01 08:20:00 | fail |
| P001 | sword | 2024-01-01 08:25:00 | fail |
| P002 | shield | 2024-01-01 09:00:00 | fail |
| P002 | shield | 2024-01-01 09:05:00 | success |
| P002 | shield | 2024-01-01 09:10:00 | fail |
| P002 | shield | 2024-01-01 09:15:00 | fail |
| P002 | shield | 2024-01-01 09:20:00 | fail |
| P002 | shield | 2024-01-01 09:25:00 | fail |
+------------+---------+----------------------+----------+
三、思路分析
本题考察连续状态分组与聚合统计，是典型的"连续值"问题，需使用 ROW_NUMBER() 差值法识别连续失败区块。
解题步骤：
- 使用
ROW_NUMBER()按玩家和装备分区，按时间排序，生成行号； - 使用另一个
ROW_NUMBER()仅对result = 'fail'的记录生成行号； - 两个行号的差值即可唯一标识一个连续失败区块；
- 按区块分组统计连续失败次数，再按玩家聚合求最大值和超阈值区段数。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 识别连续失败区块
执行SQL
select player_id,
equip,
enhance_time,
result,
row_number() over (partition by player_id, equip order by enhance_time) as rn1,
row_number() over (partition by player_id, equip, result order by enhance_time) as rn2
from t5_enhance_log
执行结果
+------------+---------+----------------------+----------+------+------+
| player_id | equip | enhance_time | result | rn1 | rn2 |
+------------+---------+----------------------+----------+------+------+
| P001 | sword | 2024-01-01 08:00:00 | fail | 1 | 1 |
| P001 | sword | 2024-01-01 08:05:00 | fail | 2 | 2 |
| P001 | sword | 2024-01-01 08:10:00 | fail | 3 | 3 |
| P001 | sword | 2024-01-01 08:20:00 | fail | 5 | 4 |
| P001 | sword | 2024-01-01 08:25:00 | fail | 6 | 5 |
| P001 | sword | 2024-01-01 08:15:00 | success | 4 | 1 |
| P002 | shield | 2024-01-01 09:00:00 | fail | 1 | 1 |
| P002 | shield | 2024-01-01 09:10:00 | fail | 3 | 2 |
| P002 | shield | 2024-01-01 09:15:00 | fail | 4 | 3 |
| P002 | shield | 2024-01-01 09:20:00 | fail | 5 | 4 |
| P002 | shield | 2024-01-01 09:25:00 | fail | 6 | 5 |
| P002 | shield | 2024-01-01 09:05:00 | success | 2 | 1 |
+------------+---------+----------------------+----------+------+------+
12 rows selected (1.01 seconds)(https://www.dwsql.com)
2. 统计每个玩家的最长连续失败次数和超3次区段数
执行SQL
select player_id,
max(fail_cnt) as max_consecutive_fail,
sum(case when fail_cnt > 3 then 1 else 0 end) as fail_segment_gt3
from (
select player_id,
(rn1 - rn2) as grp,
count(1) as fail_cnt
from (
select player_id, equip, enhance_time, result,
row_number() over (partition by player_id, equip order by enhance_time) as rn1,
row_number() over (partition by player_id, equip, result order by enhance_time) as rn2
from t5_enhance_log
) t
where result = 'fail'
group by player_id, (rn1 - rn2)
) t2
group by player_id
执行结果
+------------+-----------------------+-------------------+
| player_id | max_consecutive_fail | fail_segment_gt3 |
+------------+-----------------------+-------------------+
| P002 | 4 | 1 |
| P001 | 3 | 0 |
+------------+-----------------------+-------------------+
2 rows selected (1.05 seconds)(https://www.dwsql.com)
五、常见坑点
坑1：连续失败分组标识的计算顺序
ROW_NUMBER 差值法的关键是：对全体行按 PARTITION BY player_id, equip_id ORDER BY enhance_time 整体编号，再对失败行 result = '失败' 单独编号。如果先筛选失败再编号，成功行的 ROW_NUMBER 会被跳过，差值法的分组逻辑失效。
坑2：成功记录中断连续
某次强化成功会断开连续失败。差值法通过 ROW_NUMBER() OVER (ORDER BY time) - ROW_NUMBER() OVER (PARTITION BY result ORDER BY time) 自动识别——成功行的差值会和失败行的差值不同，自然形成分组边界。这比手动判断"上一行是否失败"更简洁可靠。
坑3：连续失败跨越多个强化记录周期
如果同一个玩家对同一个装备分多次强化（如周一失败3次，周三失败2次，中间没有该装备的记录），差值法不会错误地把这两段合并——因为全局 ROW_NUMBER 已经按时间排序，空档会自然拉大差值。
六、举一反三
-
连续登录/签到天数：将"失败"换成"登录成功"，同样用差值法统计最长连续签到天数，用于签到奖励阶梯计算
-
设置告警阈值 + 自动补偿：
WHERE max_consecutive_fail >= 5筛选出"非酋"玩家，运营系统自动发放幸运符补偿道具，减少流失 -
按装备品质分组统计：JOIN 装备品质表，
PARTITION BY equip_quality计算不同品质装备（白/蓝/紫/橙）的连续失败率，判断是否存在"高品质装备暗改概率"的数值问题 -
连续失败时间跨度：在最长连续失败区段中加入
MAX(enhance_time) - MIN(enhance_time)，计算该玩家经历连续失败的时间跨度（如跨了3天还在失败），维度比单纯数次数更丰富
七、知识点总结
| 考点 | 说明 |
|---|---|
| ROW_NUMBER 差值法 | 全局行号 - 失败行号 = 分组标识，差值相同即为同一连续失败段 |
| PARTITION BY 分组维度 | 按玩家+装备分组，确保每个玩家每个装备独立计算 |
| WHERE 过滤 + GROUP BY | 只统计失败结果，分组后 COUNT 出区段长度，MAX 取最长连续 |
| HAVING 区段筛选 | HAVING COUNT(*) > 3 筛选超过 3 次的连续失败区段，只关注严重情况 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t5_enhance_log (
player_id string COMMENT '玩家ID',
equip string COMMENT '装备名称',
enhance_time string COMMENT '强化时间',
result string COMMENT '强化结果(success/fail)'
) COMMENT '装备强化记录表';
-- 数据插入
INSERT INTO t5_enhance_log VALUES
('P001', 'sword', '2024-01-01 08:00:00', 'fail'),
('P001', 'sword', '2024-01-01 08:05:00', 'fail'),
('P001', 'sword', '2024-01-01 08:10:00', 'fail'),
('P001', 'sword', '2024-01-01 08:15:00', 'success'),
('P001', 'sword', '2024-01-01 08:20:00', 'fail'),
('P001', 'sword', '2024-01-01 08:25:00', 'fail'),
('P002', 'shield', '2024-01-01 09:00:00', 'fail'),
('P002', 'shield', '2024-01-01 09:05:00', 'success'),
('P002', 'shield', '2024-01-01 09:10:00', 'fail'),
('P002', 'shield', '2024-01-01 09:15:00', 'fail'),
('P002', 'shield', '2024-01-01 09:20:00', 'fail'),
('P002', 'shield', '2024-01-01 09:25:00', 'fail');
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
