题解 | 25年携程-海岛潜水基地「连续进步」潜水员评选

WITH monthly_stats AS (
    -- 第一步：计算出每个潜水员在每个月的潜水数以及平均潜水深度
    SELECT
        r.diver_id,
        ANY_VALUE(t_d.diver_name) AS diver_name,
        ANY_VALUE(t_d.cert_level) AS cert_level,
        MONTH(r.dive_date) AS dive_month,
        COUNT(*) AS dives,
        ROUND(AVG(r.depth_m), 2) AS avg_depth 
    FROM
        t_dive r 
    JOIN 
        t_diver t_d ON r.diver_id = t_d.diver_id
    WHERE
        r.dive_date BETWEEN '2025-03-01' AND '2025-09-30'
    GROUP BY
        r.diver_id, MONTH(r.dive_date)
),
window_calc AS (
    -- 第二步：利用 lead 函数获取下个月及下下个月的【月份】、【潜水数】和【平均深度】
    SELECT
        diver_id,
        diver_name,
        cert_level,
        -- 获取月份
        dive_month AS m0_month,
        LEAD(dive_month, 1) OVER(PARTITION BY diver_id ORDER BY dive_month ASC) AS m1_month,
        LEAD(dive_month, 2) OVER(PARTITION BY diver_id ORDER BY dive_month ASC) AS m2_month,
        -- 获取潜水次数
        dives AS m0_dives,
        LEAD(dives, 1) OVER(PARTITION BY diver_id ORDER BY dive_month ASC) AS m1_dives,
        LEAD(dives, 2) OVER(PARTITION BY diver_id ORDER BY dive_month ASC) AS m2_dives,
        -- 获取平均深度
        avg_depth AS m0_avg_depth,
        LEAD(avg_depth, 1) OVER(PARTITION BY diver_id ORDER BY dive_month ASC) AS m1_avg_depth,
        LEAD(avg_depth, 2) OVER(PARTITION BY diver_id ORDER BY dive_month ASC) AS m2_avg_depth
    FROM
        monthly_stats
)
-- 第三步：根据条件判断需要保留的行
SELECT
    diver_id,
    diver_name,
    cert_level,
    CONCAT('2025-', LPAD(m0_month, 2, '0')) AS window_m0,
    CONCAT('2025-', LPAD(m1_month, 2, '0')) AS window_m1,
    CONCAT('2025-', LPAD(m2_month, 2, '0')) AS window_m2,
    m0_dives,
    m1_dives,
    m2_dives,
    m0_avg_depth,
    m1_avg_depth,
    m2_avg_depth,
    (m0_dives + m1_dives + m2_dives) AS total_window_dives
FROM
    window_calc
WHERE
    -- 1. 核心校验：确保取到的数据确实是连续的三个月
    m1_month = m0_month + 1 
    AND m2_month = m0_month + 2
    
    -- 2. 原始业务条件：潜水次数月度不降，且起点基数 >= 2
    AND m0_dives >= 2
    AND m0_dives <= m1_dives 
    AND m1_dives <= m2_dives
    
    -- 3. 原始业务条件：平均潜深月度不降
    AND m0_avg_depth <= m1_avg_depth 
    AND m1_avg_depth <= m2_avg_depth
ORDER BY
    total_window_dives DESC, window_m0 ASC, diver_id ASC;