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

with 
t1 as(
    select max(delivered_ts) as latest_dt
    from parcel),
t2 as(
    select
        p.courier_id,
        c.courier_name,
        c.city,
        count(p.parcel_id) as orders_7d,
        sum(if(timestampdiff(minute,p.shipped_ts,p.delivered_ts)<=p.promised_minutes,1,0)) as on_time_7d,
        round(avg(timestampdiff(minute,p.shipped_ts,p.delivered_ts)),2) as avg_minutes_7d,
        rank() over(partition by city order by ifnull(round(sum(if(timestampdiff(minute,p.shipped_ts,p.delivered_ts)<=p.promised_minutes,1,0))/count(p.parcel_id),2),0) desc,round(avg(timestampdiff(minute,p.shipped_ts,p.delivered_ts)),2)) as rank_in_city
    from parcel p
    left join courier c using(courier_id) join t1 on 1=1
    where datediff(latest_dt,p.delivered_ts)<=7
    group by p.courier_id,c.courier_name
    having orders_7d>0)

select
    courier_id,courier_name,city,
    orders_7d,
    on_time_7d,
    round(on_time_7d/orders_7d,2) as on_time_rate,
    avg_minutes_7d,
    rank_in_city
from t2
order by city,rank_in_city,courier_id