【携程】寻找三个月连续不降的年度成长潜水员
JOIN 连接聚合函数GROUP BY日期函数排行榜面试真题
题目描述
来源：携程 SQL 面试真题
某海岛潜水基地以技术潜为特色，每年 3～9 月是旺季。基地总教头希望从 2025 年旺季中找出真正持续成长的潜水员。
目标不是找潜次最多或潜得最深的人，而是找出每个月都比上个月更勤、更深的潜水员，并为他们颁发“年度成长之星”。
三个月连续不降窗口
对某个潜水员，如果存在连续三个月 M0、M1、M2，并且同时满足以下条件，则构成一个成长窗口：
- 三个月都有潜次记录。
- 月潜次数满足 m0_dives ≤ m1_dives ≤ m2_dives，允许相等。
- 月平均潜深满足 m0_avg_depth ≤ m1_avg_depth ≤ m2_avg_depth，允许相等。
- 窗口起点满足 m0_dives ≥ 2。
同一潜水员可能在多个时间段构成多个成长窗口，每个窗口都要单独输出一行。
数据表
t_diver 潜水员档案表
| 字段 | 类型 | 说明 |
|---|---|---|
| diver_id | BIGINT | 潜水员编号 |
| diver_name | VARCHAR(64) | 潜水员姓名，小写英文加空格 |
| cert_level | VARCHAR(16) | 证书等级：ow / aow / rescue / dm / instructor |
| nationality | VARCHAR(32) | 国籍 |
| reg_date | DATE | 注册日期 |
t_dive 潜次记录表
| 字段 | 类型 | 说明 |
|---|---|---|
| dive_id | BIGINT | 潜次编号 |
| diver_id | BIGINT | 潜水员编号，关联 t_diver.diver_id |
| dive_site | VARCHAR(64) | 潜点名称 |
| site_type | VARCHAR(16) | 潜点类型：reef / wreck / cave / wall / pier |
| dive_date | DATE | 潜次日期 |
| depth_m | DECIMAL(5,2) | 最大潜深（米） |
| duration_min | SMALLINT | 水下停留时长（分钟） |
| air_used_liter | SMALLINT | 用气量（升） |
输出要求
只考虑 dive_date 落在 2025-03-01 至 2025-09-30 之间的潜次。
按 diver_id 和月份聚合，找出每位潜水员的所有三个月连续不降窗口。
| 字段 | 说明 |
|---|---|
| diver_id | 潜水员编号 |
| diver_name | 潜水员姓名 |
| cert_level | 证书等级 |
| window_m0 | 窗口起始月，格式 YYYY-MM |
| window_m1 | 窗口中间月，格式 YYYY-MM |
| window_m2 | 窗口结束月，格式 YYYY-MM |
| m0_dives / m1_dives / m2_dives | 三个月潜次数量 |
| m0_avg_depth / m1_avg_depth / m2_avg_depth | 三个月平均潜深，保留 2 位 |
| total_window_dives | 三个月潜次总和 |
排序规则
- total_window_dives 降序。
- window_m0 升序。
- diver_id 升序。
数据样例
| diver_idPKBIGINT | diver_nameVARCHAR(64) | cert_levelVARCHAR(16) | nationalityVARCHAR(32) | reg_dateDATE |
|---|---|---|---|---|
| 101 | alex chen | dm | china | 2024-01-10 |
| 102 | bella wu | aow | china | 2024-02-15 |
| 103 | chris li | rescue | china | 2024-03-20 |
| 104 | dana xu | ow | china | 2025-01-05 |
| 105 | evan zhou | dm | china | 2023-11-18 |
| 106 | fiona lin | aow | china | 2024-05-22 |
输入数据显示 6 / 6 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里