【京东】生成国家赛段表现层完整矩阵
JOIN 连接聚合函数GROUP BYCASE WHENUNION面试真题
题目描述
来源：京东 SQL 面试真题
2025 年东亚区山地车锦标赛由中国（CN）、日本（JP）、韩国（KR）三国车手参加，共 3 个赛段。
组委会需要生成一张“国家 × 赛段 × 表现层”三维矩阵报表。每个组合都必须输出一行，即使该组合没有符合条件的车手，也要使用 0 填充。
数据表
t_rider 车手档案表
| 字段 | 类型 | 说明 |
|---|---|---|
| rider_id | BIGINT | 车手编号 |
| rider_full_name | VARCHAR(64) | 车手姓名，格式为“姓 名”，中间一个空格 |
| team_name | VARCHAR(32) | 所属车队 |
| birth_date | DATE | 出生日期 |
| country_code | VARCHAR(8) | 国家代码，仅取 CN / JP / KR |
t_stage 赛段成绩表
| 字段 | 类型 | 说明 |
|---|---|---|
| stage_id | BIGINT | 记录编号 |
| rider_id | BIGINT | 车手编号，关联 t_rider.rider_id |
| stage_no | TINYINT | 赛段编号，仅取 1 / 2 / 3 |
| race_date | DATE | 比赛日期 |
| finish_time_sec | INT | 完成时间（秒），DNF 时为 NULL |
| dnf_flag | TINYINT | 是否中途退出，0 为完赛，1 为 DNF |
| time_penalty_sec | SMALLINT | 罚时（秒），无罚时为 0 |
表现分层
按车手在单个赛段的总用时判定：
总用时 = finish_time_sec + time_penalty_sec
仅完赛车手参与分层：
| performance_tier | 总用时范围 |
|---|---|
| gold | 小于等于 3500 秒 |
| silver | 3501～3700 秒 |
| bronze | 3701～4000 秒 |
- 总用时超过 4000 秒，视为“完赛但成绩太差不评级”，不计入任何分层。
- dnf_flag = 1 的车手不进入分层。
输出要求
输出 country_code × stage_no × performance_tier 的完整笛卡尔积，共 3 × 3 × 3 = 27 行。
| 字段 | 说明 |
|---|---|
| country_code | 国家代码 |
| stage_no | 赛段编号 |
| performance_tier | 表现分层 |
| rider_cnt | 该组合下的车手人数 |
| avg_finish_time_sec | 该组合总用时的平均值，含罚时，四舍五入保留 2 位；空组合为 0.00 |
| max_penalty_sec | 该组合最大罚时；空组合为 0 |
排序规则
- country_code 升序。
- stage_no 升序。
- performance_tier 按 gold、silver、bronze 的固定顺序。
- 前三项相同时，按 rider_cnt 降序。
数据样例
| rider_idPKBIGINT | rider_full_nameVARCHAR(64) | team_nameVARCHAR(32) | birth_dateDATE | country_codeVARCHAR(8) |
|---|---|---|---|---|
| 101 | Li Wei | Dragon Trail | 1994-03-12 | CN |
| 102 | Zhang Min | Dragon Trail | 1996-07-21 | CN |
| 103 | Chen Hao | Peak Riders | 1992-11-08 | CN |
| 201 | Sato Ken | Fuji Wheels | 1993-04-18 | JP |
| 202 | Tanaka Mei | Fuji Wheels | 1997-09-05 | JP |
| 203 | Ito Ren | Sakura Racing | 1995-01-30 | JP |
| 301 | Kim Min | Seoul Climb | 1994-06-14 | KR |
| 302 | Park Jun | Han River | 1991-12-02 | KR |
输入数据显示 8 / 8 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里