题解 | 找出补位班次

有点长，但是也没有十分复杂

WITH
-- 基础数据清洗
filtered_shifts AS (
    SELECT
        shift_id,
        store_id,
        shift_date,
        shift_period,
        required_rider_count,
        published_at
    FROM relief_shifts
    WHERE
        shift_date BETWEEN '2025-08-15' AND '2025-08-17'
        AND shift_status = 'filled'
),
first_invite_responses AS (
    SELECT
        response_id,
        shift_id,
        rider_id,
        invited_at,
        responded_at,
        response_status,
        ROW_NUMBER() OVER (
            PARTITION BY shift_id, rider_id
            ORDER BY invited_at ASC
        ) AS rn
    FROM shift_invite_responses
),

-- 筛选首轮邀约记录
valid_responses AS (
    SELECT
        shift_id,
        rider_id
    FROM first_invite_responses
    WHERE
        rn = 1  -- 只保留首轮邀约
        AND response_status = 'accepted'  -- 确认成功
        AND responded_at IS NOT NULL  -- 有反馈时间
        AND responded_at <= DATE_ADD(invited_at, INTERVAL 2 HOUR)  -- 2小时内
),

-- 计算骑手数 & 计算确认率
shift_acceptance_stats AS (
    SELECT
        fs.store_id,
        fs.shift_date,
        fs.shift_id,
        fs.shift_period,
        fs.required_rider_count,
        COUNT(DISTINCT vr.rider_id) AS accepted_in_2h_rider_count,
        -- 计算确认率，四舍五入保留2位小数
        ROUND(
            COUNT(DISTINCT vr.rider_id) / fs.required_rider_count,
            2
        ) AS acceptance_rate,
        fs.published_at
    FROM filtered_shifts fs
    LEFT JOIN valid_responses vr
        ON fs.shift_id = vr.shift_id
    GROUP BY
        fs.store_id,
        fs.shift_date,
        fs.shift_id,
        fs.shift_period,
        fs.required_rider_count,
        fs.published_at
),

-- 取排名第一的班次
ranked_shifts AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY store_id, shift_date
            ORDER BY
                acceptance_rate DESC,  -- 确认率最高
                accepted_in_2h_rider_count DESC,  -- 确认人数最多
                published_at ASC,  -- 首次发布时间最早
                shift_id ASC  -- 班次ID最小
        ) AS rank_num
    FROM shift_acceptance_stats
)

-- 最终结果：取每个门店每日排名第一的班次，并按要求排序
SELECT
    store_id,
    shift_date,
    shift_id,
    shift_period,
    required_rider_count,
    accepted_in_2h_rider_count,
    acceptance_rate
FROM ranked_shifts
WHERE rank_num = 1
ORDER BY
    shift_date ASC,
    store_id ASC,
    shift_id ASC;