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

with a as (select p1.courier_id,
    count(distinct parcel_id) orders_7d1,
    count(if(timestampdiff(second,shipped_ts,delivered_ts)/60<=promised_minutes,1,null)) on_time_7d1,
    ROUND(AVG(TIMESTAMPDIFF(MINUTE, shipped_ts, delivered_ts)), 2) avg_minutes_7d1
from parcel p1
inner join (select max(delivered_ts) mdt
from parcel
where delivered_ts is not null
) p2
on datediff(mdt,p1.delivered_ts)<=6
where delivered_ts is not null
group by p1.courier_id)

select c.courier_id,
    courier_name,
    city,
    ifnull(orders_7d1,0) orders_7d,
    ifnull(on_time_7d1,0) on_time_7d,
    ifnull(ROUND(on_time_7d1 / orders_7d1, 2),0) on_time_rate,
    ifnull(avg_minutes_7d1,0) avg_minutes_7d,
    rank() over(partition by city order by ifnull(ROUND(on_time_7d1 / orders_7d1, 2),0) desc,ifnull(avg_minutes_7d1,0) asc) rank_in_city
from a 
inner join courier c 
on a.courier_id=c.courier_id
where orders_7d1>0
order by city,rank_in_city,courier_id