题解 | 找出补位班次

with t 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_invites as
(
    select shift_id, rider_id, invited_at, responded_at, response_status
    from shift_invite_responses s1
    where invited_at in (
        select min(invited_at) 
        from shift_invite_responses  
        group by shift_id,rider_id
    )
),
t1 as
(
    select s.shift_id, 
           t.required_rider_count,
           count(distinct s.rider_id) as accepted_in_2h_rider_count,
           t.published_at 
    from first_invites s 
    join t on s.shift_id = t.shift_id
    where timestampdiff(second, s.invited_at, s.responded_at) <= 7200
      and s.response_status = 'accepted'
    group by s.shift_id
),
t2 as
(
    select store_id,shift_date,t1.shift_id,shift_period,
           t1.required_rider_count,
           coalesce(accepted_in_2h_rider_count, 0) as accepted_in_2h_rider_count,
           round(coalesce(accepted_in_2h_rider_count, 0) / t1.required_rider_count, 2) as acceptance_rate,
           t1.published_at 
    from t 
    left join t1 on t.shift_id = t1.shift_id
)
select store_id,shift_date,shift_id,shift_period,
       required_rider_count,accepted_in_2h_rider_count,acceptance_rate 
from 
(
    select store_id,shift_date,shift_id,shift_period,
           required_rider_count,accepted_in_2h_rider_count,acceptance_rate,
           rank() over(
               partition by store_id,shift_date 
               order by acceptance_rate desc,
                        accepted_in_2h_rider_count desc,
                        published_at asc,
                        shift_id asc
           ) as rk 
    from t2
) tt 
where rk = 1;