题解 | 近7天骑手履约时效看板

with a1 as (
    select courier_id,
    count(*) as orders_7d,
    sum(if(TIMESTAMPDIFF(MINUTE, shipped_ts, delivered_ts)<=promised_minutes,1,0)) as on_time_7d,
    round(sum(if(TIMESTAMPDIFF(MINUTE, shipped_ts, delivered_ts)<=promised_minutes,1,0))/count(*),2) as on_time_rate,
    ROUND(AVG(TIMESTAMPDIFF(MINUTE, shipped_ts, delivered_ts)), 2) as avg_minutes_7d
    from parcel
    where datediff((select max(delivered_ts) from parcel),delivered_ts)<=6
    group by courier_id
)
select a.courier_id,c.courier_name,c.city,a.orders_7d, a.on_time_7d ,a.on_time_rate,a.avg_minutes_7d,
rank() over(partition by c.city order by a.on_time_rate desc,a.avg_minutes_7d asc) as rank_in_city
from a1 a join courier c on a.courier_id=c.courier_id
order by city,rank_in_city,courier_id;