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

with temp0 as (
    select max(delivered_ts) as benchmark_day
    from parcel
), temp1 as (
    select 
        p.courier_id,
        count(*) as orders_7d,
        count(if(timestampdiff(minute,shipped_ts,delivered_ts)<=promised_minutes,1,null)) as on_time_7d,
        round(ifnull(count(if(timestampdiff(minute,shipped_ts,delivered_ts)<=promised_minutes,1,null))/count(*),0),2) as on_time_rate,
        round(avg(timestampdiff(minute, shipped_ts, delivered_ts)), 2) as avg_minutes_7d
    from parcel p inner join temp0 t0
    where timestampdiff(day,p.delivered_ts,t0.benchmark_day) <= 7
    group by p.courier_id
), temp2 as (
    select t1.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 asc) as rank_in_city
    from temp1 t1 inner join courier c on t1.courier_id = c.courier_id
    order by city,rank_in_city,t1.courier_id
)

select *
from temp2;