网易大数据面试SQL-副本通关时间Top N队伍
⚠️ 待修正
一、题目背景
这道题来自网易的数据分析岗面试。网易是游戏和音乐，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：游戏运营相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张副本通关记录表 t1_dungeon_clear，记录了各队伍在不同副本中的通关耗时。请找出每个副本通关时间最短的 Top 3 队伍。
副本通关记录表 t1_dungeon_clear：
+----------+--------------+------------+-------------+
| team_id | dungeon_name | clear_time | player_num |
+----------+--------------+------------+-------------+
| T01 | 深渊龙巢 | 180 | 5 |
| T02 | 深渊龙巢 | 210 | 5 |
| T03 | 深渊龙巢 | 165 | 5 |
| T04 | 深渊龙巢 | 230 | 5 |
| T05 | 深渊龙巢 | 195 | 5 |
| T01 | 暗影城堡 | 300 | 10 |
| T02 | 暗影城堡 | 280 | 10 |
| T03 | 暗影城堡 | 320 | 10 |
| T04 | 暗影城堡 | 290 | 10 |
| T05 | 暗影城堡 | 310 | 10 |
+----------+--------------+------------+-------------+
三、思路分析
本题考察窗口函数 ROW_NUMBER() 的分组排序应用。需要对每个副本内的队伍按通关时间升序排名，取前 3 名。
解题步骤：
- 使用
ROW_NUMBER() OVER (PARTITION BY dungeon_name ORDER BY clear_time ASC)对每个副本内队伍排名； - 筛选排名
rn <= 3的记录即为每个副本的 Top 3。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 对每个副本内的队伍排名
执行SQL
select team_id,
dungeon_name,
clear_time,
row_number() over (partition by dungeon_name order by clear_time asc) as rn
from t1_dungeon_clear
执行结果
+----------+--------------+------------+-----+
| team_id | dungeon_name | clear_time | rn |
+----------+--------------+------------+-----+
| T03 | 深渊龙巢 | 165 | 1 |
| T01 | 深渊龙巢 | 180 | 2 |
| T05 | 深渊龙巢 | 195 | 3 |
| T02 | 深渊龙巢 | 210 | 4 |
| T04 | 深渊龙巢 | 230 | 5 |
| T02 | 暗影城堡 | 280 | 1 |
| T04 | 暗影城堡 | 290 | 2 |
| T01 | 暗影城堡 | 300 | 3 |
| T05 | 暗影城堡 | 310 | 4 |
| T03 | 暗影城堡 | 320 | 5 |
+----------+--------------+------------+-----+
2. 筛选 Top 3
执行SQL
select dungeon_name,
team_id,
clear_time,
rn as rank
from (
select team_id,
dungeon_name,
clear_time,
row_number() over (partition by dungeon_name order by clear_time asc) as rn
from t1_dungeon_clear
) t
where rn <= 3
order by dungeon_name, rn
执行结果
+--------------+----------+------------+------+
| dungeon_name | team_id | clear_time | rank |
+--------------+----------+------------+------+
| 深渊龙巢 | T03 | 165 | 1 |
| 深渊龙巢 | T01 | 180 | 2 |
| 深渊龙巢 | T05 | 195 | 3 |
| 暗影城堡 | T02 | 280 | 1 |
| 暗影城堡 | T04 | 290 | 2 |
| 暗影城堡 | T01 | 300 | 3 |
+--------------+----------+------------+------+
五、常见坑点
坑1：RANK vs DENSE_RANK vs ROW_NUMBER — 并列值处理不同：ROW_NUMBER硬区分，RANK跳跃，DENSE_RANK连续。统计"前N名"需明确业务倾向。
坑2：NULL值排序位置 — ORDER BY 默认 NULLS LAST，如果排序列有 NULL，Top N可能被挤掉。
六、举一反三
-
分组Top N：
PARTITION BY category实现每个品类/区域的前N名 -
含占比的排名：
SUM() OVER()算总量，每个条目除总量得贡献度 -
滚动时间窗口Top N：限定过去30天数据，更适合分析趋势变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| ROW_NUMBER + 差值法 | 连续问题经典解法：日期-行号=分组标识，相同差值同一连续段 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
| PARTITION BY | 窗口函数按列分组，实现组内独立计算 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_dungeon_clear (
team_id string COMMENT '队伍ID',
dungeon_name string COMMENT '副本名称',
clear_time int COMMENT '通关耗时(秒)',
player_num int COMMENT '队伍人数'
) COMMENT '副本通关记录表';
-- 数据插入
INSERT INTO t1_dungeon_clear VALUES
('T01', '深渊龙巢', 180, 5),
('T02', '深渊龙巢', 210, 5),
('T03', '深渊龙巢', 165, 5),
('T04', '深渊龙巢', 230, 5),
('T05', '深渊龙巢', 195, 5),
('T01', '暗影城堡', 300, 10),
('T02', '暗影城堡', 280, 10),
('T03', '暗影城堡', 320, 10),
('T04', '暗影城堡', 290, 10),
('T05', '暗影城堡', 310, 10);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
