SQL 副本通关时间TopN队伍：GROUP BY + ORDER BY + LIMIT（网易面试题）
一、题目背景
这道题来自网易游戏事业部的数据分析岗面试。MMORPG 游戏的副本系统是核心 PVE 玩法，运营团队每周需要为每个副本生成"竞速排行榜"——通关最快的队伍获得全服公示和额外奖励。背后的 SQL 逻辑就是按副本分组、按通关时间排序、取每组 Top N。
业务场景：每周副本重置后，运营后台自动生成"上周各副本 Top 3 竞速榜单"，用于发放排名奖励和全服公告。这道题的 ROW_NUMBER 分组排序就是该榜单的 SQL 实现。
二、题目
现有一张副本通关记录表 t2_dungeon_clear，记录了各队伍在不同副本中的通关耗时。请找出每个副本通关时间最短的 Top 3 队伍。
副本通关记录表 t2_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 t2_dungeon_clear
执行结果
+----------+---------------+-------------+-----+
| team_id | dungeon_name | clear_time | rn |
+----------+---------------+-------------+-----+
| T02 | 暗影城堡 | 280 | 1 |
| T04 | 暗影城堡 | 290 | 2 |
| T01 | 暗影城堡 | 300 | 3 |
| T05 | 暗影城堡 | 310 | 4 |
| T03 | 暗影城堡 | 320 | 5 |
| T03 | 深渊龙巢 | 165 | 1 |
| T01 | 深渊龙巢 | 180 | 2 |
| T05 | 深渊龙巢 | 195 | 3 |
| T02 | 深渊龙巢 | 210 | 4 |
| T04 | 深渊龙巢 | 230 | 5 |
+----------+---------------+-------------+-----+
10 rows selected (0.993 seconds)(https://www.dwsql.com)
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 t2_dungeon_clear
) t
where rn <= 3
order by dungeon_name, rn
执行结果
+---------------+----------+-------------+-------+
| dungeon_name | team_id | clear_time | rank |
+---------------+----------+-------------+-------+
| 暗影城堡 | T02 | 280 | 1 |
| 暗影城堡 | T04 | 290 | 2 |
| 暗影城堡 | T01 | 300 | 3 |
| 深渊龙巢 | T03 | 165 | 1 |
| 深渊龙巢 | T01 | 180 | 2 |
| 深渊龙巢 | T05 | 195 | 3 |
+---------------+----------+-------------+-------+
6 rows selected (9.401 seconds)(https://www.dwsql.com)
五、常见坑点
坑1：RANK vs DENSE_RANK vs ROW_NUMBER — 并列值处理不同：ROW_NUMBER硬区分，RANK跳跃，DENSE_RANK连续。统计"前N名"需明确业务倾向。
坑2：NULL值排序位置 — ORDER BY 默认 NULLS LAST，如果排序列有 NULL，Top N可能被挤掉。
六、举一反三
-
按队伍人数分组竞速：
PARTITION BY dungeon_name, CASE WHEN player_num <= 5 THEN '小队' ELSE '大团' END，让 5 人队和 10 人团分别排名，更公平 -
每个副本的最慢 Top N（菜鸡榜）：
ORDER BY clear_time DESC取 Bottom N，用于发现"通关策略有严重问题"的队伍，定向推送攻略 -
NTILE 速度档位：
NTILE(3) OVER (PARTITION BY dungeon_name ORDER BY clear_time)将每个副本的队伍按通关时间均分为快/中/慢三档，统计各档位的平均通关时间变化趋势 -
个人历史最佳：如果记录了队员ID，先用
MIN(clear_time) OVER (PARTITION BY player_id, dungeon_name)取每个玩家的历史最佳成绩，再排序——实现"个人纪录榜"而非"队伍单次榜"
七、知识点总结
| 考点 | 说明 |
|---|---|
| ROW_NUMBER OVER | PARTITION BY 副本 ORDER BY 耗时 ASC 按副本分组、按时间升序排名 |
子查询 + WHERE · rn <= N | 窗口函数不能在 WHERE 直接使用，需嵌套子查询后再筛选 |
| ASC vs DESC | 耗时越短越好 → ASC；金额/分数越高越好 → DESC，方向写反结果完全相反 |
| RANK / DENSE_RANK / ROW_NUMBER | 并列值处理不同：ROW_NUMBER 硬区分，RANK 跳号，DENSE_RANK 连续 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t2_dungeon_clear (
team_id string COMMENT '队伍ID',
dungeon_name string COMMENT '副本名称',
clear_time int COMMENT '通关耗时(秒)',
player_num int COMMENT '队伍人数'
) COMMENT '副本通关记录表';
-- 数据插入
INSERT INTO t2_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真题
