题解 | 找出补位班次

with tab as(
# 筛选invited_at最早的shit_id+rider_id对,剩下的全为首轮邀约记录,同时统计2小时内确认成功条件
select *,case 
when response_status = 'accepted' and responded_at is not null and timestampdiff(SECOND,invited_at,responded_at) <= 7200 then 1 else 0 end as confirm_succeeded
from(
# shift_id、rider_id分组的invited_at升序id
select *,row_number() over(partition by shift_id,rider_id order by invited_at asc) as earliest
from(
# 整合连接表并筛选日期范围和开班成功的班次
select sir.response_id,sir.shift_id,rs.store_id,sir.rider_id,rs.shift_date,rs.shift_period,rs.required_rider_count,rs.published_at,rs.shift_status,sir.invited_at,sir.responded_at,sir.response_status
from relief_shifts as rs
right join shift_invite_responses as sir on rs.shift_id = sir.shift_id
where rs.shift_date between '2025-08-15' and '2025-08-17' and rs.shift_status = 'filled'
) as temp
) as temp2
where earliest <= 1 
)


# 筛选id为1的行并调整最终顺序
select store_id,shift_date,shift_id,shift_period,required_rider_count,accepted_in_2h_rider_count,acceptance_rate
from(
# 对每个store_id + shift_date，只保留acceptance_rate最高的那一个班次。
# 如果同一门店同一天有多个班次acceptance_rate相同，则依次取：
# accepted_in_2h_rider_count更大的
# published_at更早的
# shift_id更小的
# 以此规则建立排名id
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) as ranking
from(
# 去重
select distinct *
from(
# 筛选最终汇报所需字段并计算接受率（窗口函数实现）
select store_id,shift_date,shift_id,shift_period,required_rider_count,accepted_in_2h_rider_count,cast(accepted_in_2h_rider_count/required_rider_count as decimal(10,2)) as acceptance_rate,published_at
from(
# 统计每个班次2h内成功应邀的不同骑手个数
select *,sum(confirm_succeeded) over(partition by shift_id) as accepted_in_2h_rider_count
from tab
) as temp
) as temp2
) as temp3
) as temp4
where ranking <= 1
order by shift_date asc,store_id asc,shift_id asc