网易大数据面试SQL-跨服对战匹配计算
⚠️ 待修正
一、题目背景
这道题来自网易的数据分析岗面试。网易是游戏和音乐，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：游戏运营相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一个跨服对战匹配系统，包含两张表：
t1_player_rank：各服务器玩家的排名分t1_match_pool：匹配池，记录正在等待匹配的玩家
请为匹配池中每位玩家找出排名分最接近（分差最小）且不在同一服务器的对手。如果存在多个分差相同的对手，选择排名分较高的那个。
玩家排名分表 t1_player_rank：
+-----------+-------------+--------+
| player_id | server_id | score |
+-----------+-------------+--------+
| P001 | S1 | 1500 |
| P002 | S1 | 1600 |
| P003 | S2 | 1520 |
| P004 | S2 | 1580 |
| P005 | S3 | 1480 |
| P006 | S3 | 1620 |
| P007 | S1 | 1550 |
| P008 | S2 | 1490 |
| P009 | S3 | 1610 |
| P010 | S1 | 1530 |
+-----------+-------------+--------+
匹配池表 t1_match_pool：
+-----------+
| player_id |
+-----------+
| P001 |
| P003 |
| P005 |
+-----------+
三、思路分析
本题难度较高，考察多表 JOIN、子查询和排名窗口函数的综合运用。关键在于找出每个匹配玩家的最优对手。
解题步骤：
- 将匹配池表与排名分表 JOIN 获取等待匹配玩家的信息；
- 将等待玩家与所有其他玩家（排除同服）做 CROSS JOIN 计算分差；
- 使用
ROW_NUMBER()按分差升序、对手分数降序排序，取排名第一的即为最优对手。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 计算等待玩家与所有候选对手的分差
执行SQL
select m.player_id as waiting_player,
m.score as waiting_score,
m.server_id as waiting_server,
r.player_id as opponent,
r.score as opponent_score,
r.server_id as opponent_server,
abs(m.score - r.score) as score_diff
from (
select p.player_id, p.server_id, p.score
from t1_match_pool mp
join t1_player_rank p on mp.player_id = p.player_id
) m
join t1_player_rank r
on m.player_id != r.player_id
and m.server_id != r.server_id
执行结果
+----------------+---------------+----------------+----------+----------------+------------------+------------+
| waiting_player | waiting_score | waiting_server | opponent | opponent_score | opponent_server | score_diff |
+----------------+---------------+----------------+----------+----------------+------------------+------------+
| P001 | 1500 | S1 | P003 | 1520 | S2 | 20 |
| P001 | 1500 | S1 | P004 | 1580 | S2 | 80 |
| P001 | 1500 | S1 | P005 | 1480 | S3 | 20 |
| P001 | 1500 | S1 | P006 | 1620 | S3 | 120 |
| P001 | 1500 | S1 | P008 | 1490 | S2 | 10 |
| P001 | 1500 | S1 | P009 | 1610 | S3 | 110 |
| ... | ... | ... | ... | ... | ... | ... |
+----------------+---------------+----------------+----------+----------------+------------------+------------+
2. 使用 ROW_NUMBER 选出最优对手
执行SQL
select waiting_player,
waiting_score,
waiting_server,
opponent,
opponent_score,
opponent_server,
score_diff
from (
select m.player_id as waiting_player,
m.score as waiting_score,
m.server_id as waiting_server,
r.player_id as opponent,
r.score as opponent_score,
r.server_id as opponent_server,
abs(m.score - r.score) as score_diff,
row_number() over (
partition by m.player_id
order by abs(m.score - r.score) asc, r.score desc
) as rn
from (
select p.player_id, p.server_id, p.score
from t1_match_pool mp
join t1_player_rank p on mp.player_id = p.player_id
) m
join t1_player_rank r
on m.player_id != r.player_id
and m.server_id != r.server_id
) t
where rn = 1
执行结果
+----------------+---------------+---------------+----------+----------------+------------------+------------+
| waiting_player | waiting_score | waiting_server| opponent | opponent_score | opponent_server | score_diff |
+----------------+---------------+---------------+----------+----------------+------------------+------------+
| P001 | 1500 | S1 | P008 | 1490 | S2 | 10 |
| P003 | 1520 | S2 | P010 | 1530 | S1 | 10 |
| P005 | 1480 | S3 | P008 | 1490 | S2 | 10 |
+----------------+---------------+---------------+----------+----------------+------------------+------------+
五、常见坑点
坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。
坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。
六、举一反三
-
增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
-
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
-
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据
七、知识点总结
| 考点 | 说明 |
|---|---|
| ROW_NUMBER + 差值法 | 连续问题经典解法：日期-行号=分组标识，相同差值同一连续段 |
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_player_rank (
player_id string COMMENT '玩家ID',
server_id string COMMENT '服务器ID',
score int COMMENT '排名分'
) COMMENT '玩家排名分表';
CREATE TABLE t1_match_pool (
player_id string COMMENT '等待匹配的玩家ID'
) COMMENT '跨服对战匹配池表';
-- 数据插入
INSERT INTO t1_player_rank VALUES
('P001', 'S1', 1500),
('P002', 'S1', 1600),
('P003', 'S2', 1520),
('P004', 'S2', 1580),
('P005', 'S3', 1480),
('P006', 'S3', 1620),
('P007', 'S1', 1550),
('P008', 'S2', 1490),
('P009', 'S3', 1610),
('P010', 'S1', 1530);
INSERT INTO t1_match_pool VALUES
('P001'),
('P003'),
('P005');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
