25年携程-海岛潜水基地「连续进步」潜水员评选_牛客题霸

1. 题目描述背景某海岛潜水基地以技术潜（深潜、洞潜、沉船）为特色，每年 3-9 月是旺季。基地总教头想从 2025 年旺季中找出真正在持续成长的潜水员——不是潜次最多的、也不是潜得最深的，而是「每个月都比上个月更勤、更深」的潜水员。要把这些"持续进步选手"全部挖出来，给他们颁"年度成长之星"。 3 月连续不降窗口定义：对某潜水员，存在三个连续月份 M0、M1、M2（M1 是 M0 的下一月，M2 是 M1 的下一月），同时满足以下条件，则构成一个"成长窗口"：三个月都有潜次记录（每月 ≥ 1 次）该潜水员当月潜次数：m0_dives ≤ m1_dives ≤ m2_dives（潜次数月度不降，允许相等）该潜水员当月平均潜深：m0_avg_depth ≤ m1_avg_depth ≤ m2_avg_depth（平均潜深月度不降，允许相等） m0_dives ≥ 2（窗口起点的基数门槛，避免 1 次→2 次→3 次也算成长）同一潜水员可能在多个时间段构成多个成长窗口，每个窗口都要单独输出一行。表 1：t_diver（潜水员档案表）——每位潜水员一行 diver_id：BIGINT，潜水员编号 diver_name：VARCHAR(64),潜水员姓名（小写英文 + 空格，如 'alex chen'） cert_level：VARCHAR(16)，证书等级，取值'ow''aow''rescue''dm''instructor'（统一小写） nationality：VARCHAR(32)，国籍 reg_date：DATE，注册日期表 2：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，潜次日期（2025 年内） depth_m：DECIMAL(5,2)，最大潜深（米） duration_min：SMALLINT，水下停留时长（分钟） air_used_liter：SMALLINT，用气量（升） 2. 问题只考虑dive_date落在 2025-03-01 至 2025-09-30 之间的潜次。按diver_id× 月份聚合后，找出每位潜水员的所有 3 月连续不降窗口（按上一节定义），每个窗口输出一行。输出字段（英文名）： diver_id：潜水员编号 diver_name：潜水员姓名 cert_level：证书等级 window_m0：窗口起始月，格式'YYYY-MM'，例如'2025-03'（始终用 2 位月份，前补 0） window_m1：窗口中间月，格式同上 window_m2：窗口结束月，格式同上 m0_dives：M0 月潜次数（整数） m1_dives：M1 月潜次数（整数） m2_dives：M2 月潜次数（整数） m0_avg_depth：M0 月平均潜深，四舍五入保留 2 位 m1_avg_depth：M1 月平均潜深，同上 m2_avg_depth：M2 月平均潜深，同上 total_window_dives：窗口内三月潜次总和（=m0_dives + m1_dives + m2_dives）排序规则（严格按此顺序）： total_window_dives降序 window_m0升序（字符串字典序，等价于按月份升序） diver_id升序

示例1

输入

CREATE TABLE t_diver (
    diver_id    BIGINT PRIMARY KEY,
    diver_name  VARCHAR(64) NOT NULL,
    cert_level  VARCHAR(16) NOT NULL,
    nationality VARCHAR(32) NOT NULL,
    reg_date    DATE NOT NULL
);

CREATE TABLE t_dive (
    dive_id        BIGINT PRIMARY KEY,
    diver_id       BIGINT NOT NULL,
    dive_site      VARCHAR(64) NOT NULL,
    site_type      VARCHAR(16) NOT NULL,
    dive_date      DATE NOT NULL,
    depth_m        DECIMAL(5,2) NOT NULL,
    duration_min   SMALLINT NOT NULL,
    air_used_liter SMALLINT NOT NULL
);

INSERT INTO t_diver VALUES
(1, 'alex chen',  'aow',        'Singapore', '2025-01-10'),
(2, 'mei lin',    'rescue',     'China',     '2025-01-22'),
(3, 'sara kim',   'dm',         'Korea',     '2025-02-20'),
(4, 'kenji sato', 'instructor', 'Japan',     '2024-09-05'),
(5, 'liu wei',    'ow',         'China',     '2025-04-15');

INSERT INTO t_dive VALUES
-- alex chen (id=1)：3 月 3 次 → 4 月 5 次 → 5 月 7 次 → 6 月 8 次；潜深 20→22→25→26；构成 3-5 和 4-6 两个窗口
(101, 1, 'Crystal Bay',   'reef',  '2025-03-08', 20.0, 42, 1500),
(102, 1, 'Seraya Reef',   'reef',  '2025-03-15', 21.0, 45, 1550),
(103, 1, 'Coral Garden',  'reef',  '2025-03-22', 19.0, 50, 1450),
(104, 1, 'Crystal Bay',   'reef',  '2025-04-05', 22.0, 45, 1500),
(105, 1, 'USAT Liberty',  'wreck', '2025-04-12', 23.0, 42, 1550),
(106, 1, 'Coral Garden',  'reef',  '2025-04-19', 21.0, 48, 1500),
(107, 1, 'Tulamben Wall', 'wall',  '2025-04-23', 23.0, 40, 1600),
(108, 1, 'Seraya Reef',   'reef',  '2025-04-28', 21.0, 45, 1500),
(109, 1, 'Manta Point',   'wall',  '2025-05-03', 25.0, 38, 1700),
(110, 1, 'USAT Liberty',  'wreck', '2025-05-10', 24.0, 40, 1650),
(111, 1, 'Tulamben Wall', 'wall',  '2025-05-12', 26.0, 38, 1750),
(112, 1, 'Manta Point',   'wall',  '2025-05-17', 25.0, 40, 1700),
(113, 1, 'Coral Garden',  'reef',  '2025-05-22', 25.0, 42, 1650),
(114, 1, 'Manta Point',   'wall',  '2025-05-25', 25.0, 38, 1700),
(115, 1, 'USAT Liberty',  'wreck', '2025-05-30', 25.0, 40, 1700),
(116, 1, 'Manta Point',   'wall',  '2025-06-04', 26.0, 36, 1750),
(117, 1, 'Tulamben Wall', 'wall',  '2025-06-07', 27.0, 35, 1800),
(118, 1, 'USAT Liberty',  'wreck', '2025-06-11', 25.0, 40, 1700),
(119, 1, 'Manta Point',   'wall',  '2025-06-15', 26.0, 36, 1750),
(120, 1, 'Tulamben Wall', 'wall',  '2025-06-18', 26.0, 38, 1750),
(121, 1, 'Manta Point',   'wall',  '2025-06-22', 26.0, 36, 1750),
(122, 1, 'USAT Liberty',  'wreck', '2025-06-26', 27.0, 35, 1800),
(123, 1, 'Tulamben Wall', 'wall',  '2025-06-30', 25.0, 40, 1700),
-- mei lin (id=2)：3 月 1 次 / 4 月 1 次（基数不足）→ 5 月 2 次 → 6 月 3 次 → 7 月 5 次；构成 5-7 月窗口
(201, 2, 'Crystal Bay',   'reef',  '2025-03-20', 15.0, 50, 1400),
(202, 2, 'Crystal Bay',   'reef',  '2025-04-15', 16.0, 50, 1400),
(203, 2, 'Coral Garden',  'reef',  '2025-05-10', 18.0, 48, 1500),
(204, 2, 'Coral Garden',  'reef',  '2025-05-25', 18.0, 50, 1500),
(205, 2, 'Seraya Reef',   'reef',  '2025-06-05', 22.0, 45, 1600),
(206, 2, 'USAT Liberty',  'wreck', '2025-06-15', 21.0, 42, 1600),
(207, 2, 'Tulamben Wall', 'wall',  '2025-06-25', 23.0, 40, 1700),
(208, 2, 'Manta Point',   'wall',  '2025-07-05', 25.0, 38, 1700),
(209, 2, 'Tulamben Wall', 'wall',  '2025-07-12', 24.0, 40, 1700),
(210, 2, 'USAT Liberty',  'wreck', '2025-07-18', 26.0, 35, 1800),
(211, 2, 'Manta Point',   'wall',  '2025-07-22', 25.0, 38, 1700),
(212, 2, 'Manta Point',   'wall',  '2025-07-28', 25.0, 38, 1700),
-- sara kim (id=3)：6 月 2 次 → 7 月 3 次 → 8 月 4 次；构成 6-8 月窗口
(301, 3, 'Crystal Bay',   'reef',  '2025-06-08', 20.0, 42, 1500),
(302, 3, 'Coral Garden',  'reef',  '2025-06-22', 20.0, 45, 1500),
(303, 3, 'Seraya Reef',   'reef',  '2025-07-10', 22.0, 42, 1600),
(304, 3, 'Tulamben Wall', 'wall',  '2025-07-20', 22.0, 40, 1700),
(305, 3, 'USAT Liberty',  'wreck', '2025-07-28', 22.0, 38, 1650),
(306, 3, 'Manta Point',   'wall',  '2025-08-05', 25.0, 36, 1700),
(307, 3, 'Tulamben Wall', 'wall',  '2025-08-12', 25.0, 38, 1700),
(308, 3, 'USAT Liberty',  'wreck', '2025-08-20', 25.0, 35, 1750),
(309, 3, 'Manta Point',   'wall',  '2025-08-28', 25.0, 36, 1700),
-- kenji sato (id=4)：3 月 4 次 → 4 月 3 次（潜次减少）→ 5 月 5 次；3-5 月不构成窗口
(401, 4, 'Manta Point',   'wall',  '2025-03-05', 28.0, 35, 1900),
(402, 4, 'Tulamben Wall', 'wall',  '2025-03-12', 28.0, 35, 1900),
(403, 4, 'Manta Point',   'wall',  '2025-03-19', 29.0, 32, 2000),
(404, 4, 'USAT Liberty',  'wreck', '2025-03-26', 27.0, 38, 1850),
(405, 4, 'Manta Point',   'wall',  '2025-04-08', 30.0, 30, 2000),
(406, 4, 'Tulamben Wall', 'wall',  '2025-04-15', 30.0, 30, 2000),
(407, 4, 'Manta Point',   'wall',  '2025-04-22', 30.0, 32, 2000),
(408, 4, 'Manta Point',   'wall',  '2025-05-05', 32.0, 28, 2100),
(409, 4, 'Tulamben Wall', 'wall',  '2025-05-12', 31.0, 30, 2050),
(410, 4, 'Manta Point',   'wall',  '2025-05-19', 32.0, 28, 2100),
(411, 4, 'Manta Point',   'wall',  '2025-05-26', 33.0, 26, 2150),
(412, 4, 'Tulamben Wall', 'wall',  '2025-05-30', 32.0, 28, 2100),
-- liu wei (id=5)：5/6/7 月各 1 次，潜次和深度都递增但 m0_dives=1 < 2，不达基数门槛
(501, 5, 'Crystal Bay',   'reef',  '2025-05-15', 15.0, 50, 1400),
(502, 5, 'Coral Garden',  'reef',  '2025-06-15', 16.0, 50, 1400),
(503, 5, 'Crystal Bay',   'reef',  '2025-07-15', 17.0, 50, 1400);

输出

diver_id|diver_name|cert_level|window_m0|window_m1|window_m2|m0_dives|m1_dives|m2_dives|m0_avg_depth|m1_avg_depth|m2_avg_depth|total_window_dives
1|alex chen|aow|2025-04|2025-05|2025-06|5|7|8|22.00|25.00|26.00|20
1|alex chen|aow|2025-03|2025-04|2025-05|3|5|7|20.00|22.00|25.00|15
2|mei lin|rescue|2025-05|2025-06|2025-07|2|3|5|18.00|22.00|25.00|10
3|sara kim|dm|2025-06|2025-07|2025-08|2|3|4|20.00|22.00|25.00|9

说明

按月聚合后：

diver_id	月份	潜次数	平均潜深
1	2025-03	3	20.00
1	2025-04	5	22.00
1	2025-05	7	25.00
1	2025-06	8	26.00
2	2025-03	1	15.00
2	2025-04	1	16.00
2	2025-05	2	18.00
2	2025-06	3	22.00
2	2025-07	5	25.00
3	2025-06	2	20.00
3	2025-07	3	22.00
3	2025-08	4	25.00
4	2025-03	4	28.00
4	2025-04	3	30.00
4	2025-05	5	32.00
5	2025-05	1	15.00
5	2025-06	1	16.00
5	2025-07	1	17.00

枚举所有 3 月窗口：

alex 3-5 月：3≤5≤7 ✓，20≤22≤25 ✓，m0=3 ≥ 2 ✓ → 入选，total=15
alex 4-6 月：5≤7≤8 ✓，22≤25≤26 ✓，m0=5 ≥ 2 ✓ → 入选，total=20
mei lin 3-5 月：m0=1 < 2 ✗
mei lin 4-6 月：m0=1 < 2 ✗
mei lin 5-7 月：2≤3≤5 ✓，18≤22≤25 ✓，m0=2 ≥ 2 ✓ → 入选，total=10
sara kim 6-8 月：2≤3≤4 ✓，20≤22≤25 ✓，m0=2 ≥ 2 ✓ → 入选，total=9
kenji 3-5 月：m0=4 > m1=3 ✗
liu wei 5-7 月：m0=1 < 2 ✗

CREATE TABLE t_diver (
    diver_id    BIGINT PRIMARY KEY,
    diver_name  VARCHAR(64) NOT NULL,
    cert_level  VARCHAR(16) NOT NULL,
    nationality VARCHAR(32) NOT NULL,
    reg_date    DATE NOT NULL
);

CREATE TABLE t_dive (
    dive_id        BIGINT PRIMARY KEY,
    diver_id       BIGINT NOT NULL,
    dive_site      VARCHAR(64) NOT NULL,
    site_type      VARCHAR(16) NOT NULL,
    dive_date      DATE NOT NULL,
    depth_m        DECIMAL(5,2) NOT NULL,
    duration_min   SMALLINT NOT NULL,
    air_used_liter SMALLINT NOT NULL
);

INSERT INTO t_diver VALUES
(1, 'alex chen',  'aow',        'Singapore', '2025-01-10'),
(2, 'mei lin',    'rescue',     'China',     '2025-01-22'),
(3, 'sara kim',   'dm',         'Korea',     '2025-02-20'),
(4, 'kenji sato', 'instructor', 'Japan',     '2024-09-05'),
(5, 'liu wei',    'ow',         'China',     '2025-04-15');

INSERT INTO t_dive VALUES
-- alex chen (id=1)：3 月 3 次 → 4 月 5 次 → 5 月 7 次 → 6 月 8 次；潜深 20→22→25→26；构成 3-5 和 4-6 两个窗口
(101, 1, 'Crystal Bay',   'reef',  '2025-03-08', 20.0, 42, 1500),
(102, 1, 'Seraya Reef',   'reef',  '2025-03-15', 21.0, 45, 1550),
(103, 1, 'Coral Garden',  'reef',  '2025-03-22', 19.0, 50, 1450),
(104, 1, 'Crystal Bay',   'reef',  '2025-04-05', 22.0, 45, 1500),
(105, 1, 'USAT Liberty',  'wreck', '2025-04-12', 23.0, 42, 1550),
(106, 1, 'Coral Garden',  'reef',  '2025-04-19', 21.0, 48, 1500),
(107, 1, 'Tulamben Wall', 'wall',  '2025-04-23', 23.0, 40, 1600),
(108, 1, 'Seraya Reef',   'reef',  '2025-04-28', 21.0, 45, 1500),
(109, 1, 'Manta Point',   'wall',  '2025-05-03', 25.0, 38, 1700),
(110, 1, 'USAT Liberty',  'wreck', '2025-05-10', 24.0, 40, 1650),
(111, 1, 'Tulamben Wall', 'wall',  '2025-05-12', 26.0, 38, 1750),
(112, 1, 'Manta Point',   'wall',  '2025-05-17', 25.0, 40, 1700),
(113, 1, 'Coral Garden',  'reef',  '2025-05-22', 25.0, 42, 1650),
(114, 1, 'Manta Point',   'wall',  '2025-05-25', 25.0, 38, 1700),
(115, 1, 'USAT Liberty',  'wreck', '2025-05-30', 25.0, 40, 1700),
(116, 1, 'Manta Point',   'wall',  '2025-06-04', 26.0, 36, 1750),
(117, 1, 'Tulamben Wall', 'wall',  '2025-06-07', 27.0, 35, 1800),
(118, 1, 'USAT Liberty',  'wreck', '2025-06-11', 25.0, 40, 1700),
(119, 1, 'Manta Point',   'wall',  '2025-06-15', 26.0, 36, 1750),
(120, 1, 'Tulamben Wall', 'wall',  '2025-06-18', 26.0, 38, 1750),
(121, 1, 'Manta Point',   'wall',  '2025-06-22', 26.0, 36, 1750),
(122, 1, 'USAT Liberty',  'wreck', '2025-06-26', 27.0, 35, 1800),
(123, 1, 'Tulamben Wall', 'wall',  '2025-06-30', 25.0, 40, 1700),
-- mei lin (id=2)：3 月 1 次 / 4 月 1 次（基数不足）→ 5 月 2 次 → 6 月 3 次 → 7 月 5 次；构成 5-7 月窗口
(201, 2, 'Crystal Bay',   'reef',  '2025-03-20', 15.0, 50, 1400),
(202, 2, 'Crystal Bay',   'reef',  '2025-04-15', 16.0, 50, 1400),
(203, 2, 'Coral Garden',  'reef',  '2025-05-10', 18.0, 48, 1500),
(204, 2, 'Coral Garden',  'reef',  '2025-05-25', 18.0, 50, 1500),
(205, 2, 'Seraya Reef',   'reef',  '2025-06-05', 22.0, 45, 1600),
(206, 2, 'USAT Liberty',  'wreck', '2025-06-15', 21.0, 42, 1600),
(207, 2, 'Tulamben Wall', 'wall',  '2025-06-25', 23.0, 40, 1700),
(208, 2, 'Manta Point',   'wall',  '2025-07-05', 25.0, 38, 1700),
(209, 2, 'Tulamben Wall', 'wall',  '2025-07-12', 24.0, 40, 1700),
(210, 2, 'USAT Liberty',  'wreck', '2025-07-18', 26.0, 35, 1800),
(211, 2, 'Manta Point',   'wall',  '2025-07-22', 25.0, 38, 1700),
(212, 2, 'Manta Point',   'wall',  '2025-07-28', 25.0, 38, 1700),
-- sara kim (id=3)：6 月 2 次 → 7 月 3 次 → 8 月 4 次；构成 6-8 月窗口
(301, 3, 'Crystal Bay',   'reef',  '2025-06-08', 20.0, 42, 1500),
(302, 3, 'Coral Garden',  'reef',  '2025-06-22', 20.0, 45, 1500),
(303, 3, 'Seraya Reef',   'reef',  '2025-07-10', 22.0, 42, 1600),
(304, 3, 'Tulamben Wall', 'wall',  '2025-07-20', 22.0, 40, 1700),
(305, 3, 'USAT Liberty',  'wreck', '2025-07-28', 22.0, 38, 1650),
(306, 3, 'Manta Point',   'wall',  '2025-08-05', 25.0, 36, 1700),
(307, 3, 'Tulamben Wall', 'wall',  '2025-08-12', 25.0, 38, 1700),
(308, 3, 'USAT Liberty',  'wreck', '2025-08-20', 25.0, 35, 1750),
(309, 3, 'Manta Point',   'wall',  '2025-08-28', 25.0, 36, 1700),
-- kenji sato (id=4)：3 月 4 次 → 4 月 3 次（潜次减少）→ 5 月 5 次；3-5 月不构成窗口
(401, 4, 'Manta Point',   'wall',  '2025-03-05', 28.0, 35, 1900),
(402, 4, 'Tulamben Wall', 'wall',  '2025-03-12', 28.0, 35, 1900),
(403, 4, 'Manta Point',   'wall',  '2025-03-19', 29.0, 32, 2000),
(404, 4, 'USAT Liberty',  'wreck', '2025-03-26', 27.0, 38, 1850),
(405, 4, 'Manta Point',   'wall',  '2025-04-08', 30.0, 30, 2000),
(406, 4, 'Tulamben Wall', 'wall',  '2025-04-15', 30.0, 30, 2000),
(407, 4, 'Manta Point',   'wall',  '2025-04-22', 30.0, 32, 2000),
(408, 4, 'Manta Point',   'wall',  '2025-05-05', 32.0, 28, 2100),
(409, 4, 'Tulamben Wall', 'wall',  '2025-05-12', 31.0, 30, 2050),
(410, 4, 'Manta Point',   'wall',  '2025-05-19', 32.0, 28, 2100),
(411, 4, 'Manta Point',   'wall',  '2025-05-26', 33.0, 26, 2150),
(412, 4, 'Tulamben Wall', 'wall',  '2025-05-30', 32.0, 28, 2100),
-- liu wei (id=5)：5/6/7 月各 1 次，潜次和深度都递增但 m0_dives=1 < 2，不达基数门槛
(501, 5, 'Crystal Bay',   'reef',  '2025-05-15', 15.0, 50, 1400),
(502, 5, 'Coral Garden',  'reef',  '2025-06-15', 16.0, 50, 1400),
(503, 5, 'Crystal Bay',   'reef',  '2025-07-15', 17.0, 50, 1400);