题解 | 找出补位班次

-- 1.1 将表进行清洗，只取出规定时间内结束的班次信息
WITH temp AS (
    SELECT * FROM relief_shifts
    WHERE shift_status = 'filled'
      AND shift_date >= '2025-08-15' AND shift_date <= '2025-08-17'
),
-- 1.2 只取出首轮的邀约
temp1 AS (
    SELECT * FROM (
        SELECT *,
               ROW_NUMBER() OVER(PARTITION BY shift_id, rider_id ORDER BY invited_at ASC) AS rnk
        FROM shift_invite_responses
    ) t
    WHERE rnk = 1
),
-- 1.3 关联计算核心指标：2小时内接单人数
temp2 AS (

    SELECT 
        tp.store_id,
        tp.shift_date,
        tp.shift_id,
        tp.shift_period,
        tp.required_rider_count, 
        tp.published_at,        
        SUM(
            CASE 
                WHEN tp1.response_status = 'accepted' 
                 AND tp1.responded_at IS NOT NULL 
                 AND tp1.responded_at <= DATE_ADD(tp1.invited_at, INTERVAL 2 HOUR) 
                THEN 1 
                ELSE 0 
            END
        ) AS accepted_in_2h_rider_count
        
    FROM temp tp
    LEFT JOIN temp1 tp1 ON tp1.shift_id = tp.shift_id
    GROUP BY 
        tp.store_id, tp.shift_date, tp.shift_id, tp.shift_period, 
        tp.required_rider_count, tp.published_at
),

temp3 AS (
    -- 4. 计算接单率，并使用窗口函数进行终极排名
    SELECT 
        *,
        ROUND(accepted_in_2h_rider_count / required_rider_count, 2) AS acceptance_rate,
        
        ROW_NUMBER() OVER(
            PARTITION BY store_id, shift_date 
            ORDER BY 
                ROUND(accepted_in_2h_rider_count / required_rider_count, 2) DESC, -- 率降序
                accepted_in_2h_rider_count DESC,                                  -- 人数降序
                published_at ASC,                                                 -- 时间升序
                shift_id ASC                                                      -- ID升序
        ) AS daily_rank
    FROM temp2
)

-- 5. 最终查询：只取各组第一名，并按要求排序
SELECT 
    store_id,
    shift_date,
    shift_id,
    shift_period,
    required_rider_count,
    accepted_in_2h_rider_count,
    acceptance_rate
FROM temp3
WHERE daily_rank = 1
ORDER BY 
    shift_date ASC,
    store_id ASC,
    shift_id ASC;