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

select courier_id,courier_name,city,
        orders_7d,on_time_7d,on_time_rate,avg_minutes_7d
        ,row_number() over(partition by city order by on_time_rate desc,avg_minutes_7d) as rank_in_city
from (select
    a.courier_id,
    a.courier_name,
    a.city,
    count(distinct b.parcel_id) as orders_7d,
    count(case when b.acc_time <= promised_minutes then 1 else null end ) as on_time_7d,
    round(count(case when b.acc_time <= promised_minutes then 1 else null end ) /  count(distinct b.parcel_id), 2) as on_time_rate,
    round(avg(acc_time),2) as avg_minutes_7d
from
    courier a
    join (
        select
            parcel_id,
            courier_id,
            timestampdiff(minute, shipped_ts, delivered_ts) as acc_time,
            promised_minutes
        from parcel
        where datediff((select max(delivered_ts) from parcel), delivered_ts) between 0 and 6
    ) b on a.courier_id = b.courier_id
    group by 1, 2, 3) c
order by city,rank_in_city,courier_id