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

select
    *
from(
    select
        p.courier_id, c.courier_name, c.city,
        count(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(sum(if(timestampdiff(minute, p.shipped_ts, p.delivered_ts) <= p.promised_minutes, 1, 0)) / nullif(count(parcel_id), 0), 2) as on_time_rate,
        round(avg(timestampdiff(minute, p.shipped_ts, p.delivered_ts)), 2) as avg_minutes_7d,
        rank() over(partition by c.city order by (sum(if(timestampdiff(minute, p.shipped_ts, p.delivered_ts) <= p.promised_minutes, 1, 0)) / nullif(count(parcel_id), 0)) desc, avg(timestampdiff(minute, p.shipped_ts, p.delivered_ts))) as rank_in_city
    from
        courier c join parcel p on c.courier_id = p.courier_id
    where
        p.delivered_ts >=
            (select date_sub(max(delivered_ts), interval 7 day)
            from parcel)
    group by
        p.courier_id, c.courier_name, c.city) t
-- 排序和窗口函数排序冲突，不拿出来会出错
order by
    city, rank_in_city, courier_id;