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

WITH tb1 AS (
    SELECT parcel_id, courier_id, 
           TIMESTAMPDIFF(MINUTE, shipped_ts,delivered_ts ) AS actual_minutes,
           promised_minutes,delivered_ts,
           MAX(DATE(delivered_ts)) OVER(PARTITION BY courier_id) AS latest_date
    FROM parcel
),
tb2 AS (
    SELECT parcel_id, tb1.courier_id, courier_name, courier.city,
           actual_minutes, promised_minutes,
           CASE WHEN actual_minutes <= promised_minutes 
           THEN 1 ELSE 0 END AS if_punct
    FROM tb1 
    LEFT JOIN courier USING(courier_id)
    WHERE delivered_ts >= DATE_SUB(latest_date, INTERVAL 7 DAY)
), 
tb3 AS (
SELECT courier_id, courier_name, city, 
       COUNT(*) AS orders_7d,
       SUM(if_punct) AS on_time_7d,
       ROUND(SUM(if_punct)/COUNT(*),2) AS on_time_rate,
       ROUND(AVG(actual_minutes),2) AS avg_minutes_7d,
       RANK() OVER(PARTITION BY city ORDER BY ROUND(SUM(if_punct)/COUNT(*),2) DESC, ROUND(AVG(actual_minutes),2) ASC) AS rank_in_city
FROM tb2
GROUP BY courier_id
)
SELECT *
FROM tb3
WHERE orders_7d > 0 AND on_time_7d > 0 
ORDER BY city, rank_in_city, courier_id