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

WITH 
-- 获取最新送达时间作为基准日
latest_delivery AS (
    SELECT MAX(delivered_ts) AS base_date FROM parcel
),

-- 计算每位骑手近7天的履约数据
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, p.shipped_ts, p.delivered_ts)), 2) AS avg_minutes_7d
    FROM 
        courier c -- 信息表
    right JOIN
        parcel p ON c.courier_id = p.courier_id -- 订单表
    left JOIN
        latest_delivery ld ON 1=1 -- 将latest_delivery中的列添加到主查询的每一行中 -- 最新日期
    WHERE 
        p.delivered_ts >= DATE_SUB(ld.base_date, INTERVAL 6 DAY) 
        AND p.delivered_ts <= ld.base_date --筛选最近7天
    GROUP BY 
        c.courier_id, c.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 
    courier_id,
    courier_name,
    city,
    orders_7d,
    on_time_7d,
    on_time_rate,
    avg_minutes_7d,
    rank_in_city
FROM 
    final_stats
WHERE 
    orders_7d > 0
ORDER BY 
    city,
    rank_in_city,
    courier_id;