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

WITH latest_delivery AS (
    SELECT MAX(delivered_ts) AS base_date FROM parcel
),
courier_stats AS (
    SELECT c.courier_id,
           c.courier_name,
           c.city,
           COUNT(p.parcel_id) AS orders_7d,
           SUM(CASE WHEN 
               TIMESTAMPDIFF(minute, p.shipped_ts, p.delivered_ts) <= p.promised_minutes THEN 1 ELSE 0 END) AS on_time_7d,
           ROUND(AVG(TIMESTAMPDIFF(MINUTE, shipped_ts, delivered_ts)),2) AS avg_minutes_7d
    FROM courier c
    RIGHT JOIN parcel p USING(courier_id)
    LEFT JOIN latest_delivery ld ON 1=1
    WHERE p.delivered_ts >= DATE_SUB(ld.base_date, INTERVAL 6 DAY)
          AND p.delivered_ts <= ld.base_date
    GROUP BY c.courier_id, courier_name, c.city
),
final_stats AS (
    SELECT courier_id, courier_name, city, orders_7d, on_time_7d,
          CASE WHEN orders_7d = 0 THEN 0
          ELSE ROUND(on_time_7d / orders_7d, 2)
          END AS on_time_rate,
          avg_minutes_7d,
          RANK() OVER(
            PARTITION BY city ORDER BY CASE WHEN orders_7d = 0 THEN 0
          ELSE ROUND(on_time_7d / orders_7d, 2) END DESC, avg_minutes_7d ASC
          ) AS rank_in_city
    FROM courier_stats
)
SELECT *
FROM final_stats
WHERE orders_7d > 0
ORDER BY city, rank_in_city, courier_id