题解 | 统计骑手信息

WITH t1 AS (
SELECT Z.zone_id,zone_name,
       COUNT(IF(DATE_FORMAT(order_time,"%Y-%m")='2023-02',order_id,NULL)) AS peak_2023_02_delivered,
       COUNT(IF(DATE_FORMAT(order_time,"%Y-%m")='2024-02',order_id,NULL)) AS peak_2024_02_delivered,
       COUNT(IF(DATE_FORMAT(order_time,"%Y-%m")='2024-01',order_id,NULL)) AS peak_2024_01_delivered,
       COUNT(IF(DATE_FORMAT(order_time,"%Y-%m")='2024-02',order_id,NULL))-COUNT(IF(DATE_FORMAT(order_time,"%Y-%m")='2023-02',order_id,NULL))  AS yoy_delta,
       COUNT(IF(DATE_FORMAT(order_time,"%Y-%m")='2024-02',order_id,NULL))-COUNT(IF(DATE_FORMAT(order_time,"%Y-%m")='2024-01',order_id,NULL)) AS mom_delta,
       ROUND(AVG(CASE WHEN DATE_FORMAT(order_time,"%Y-%m")='2024-02' THEN TIMESTAMPDIFF(MINUTE, order_time, delivered_time) END),2) AS avg_peak_minutes_2024_02
FROM Zones Z 
LEFT JOIN Orders O ON Z.zone_id=O.zone_id
LEFT JOIN Couriers C ON O.courier_id=C.courier_id
WHERE (HOUR(order_time) BETWEEN 11 AND 13 OR HOUR(order_time) BETWEEN 18 AND 20) AND status='delivered'
GROUP BY Z.zone_id,zone_name
),
t2 AS (
    SELECT Z.zone_id,zone_name,C.courier_id,courier_name,
           COUNT(IF(DATE_FORMAT(order_time,"%Y-%m")='2024-02',order_id,NULL)) AS cnt
    FROM Zones Z 
    LEFT JOIN Orders O ON Z.zone_id=O.zone_id
    LEFT JOIN Couriers C ON O.courier_id=C.courier_id
    WHERE (HOUR(order_time) BETWEEN 11 AND 13 OR HOUR(order_time) BETWEEN 18 AND 20) AND status='delivered'
    GROUP BY Z.zone_id,zone_name,C.courier_id,courier_name
),
t3 AS (
    SELECT zone_id,zone_name,courier_id,courier_name,
           ROW_NUMBER() OVER(PARTITION BY zone_id ORDER BY cnt DESC,courier_id ASC) AS rnk
    FROM t2
),
t4 AS (
    SELECT zone_id,zone_name,courier_id,courier_name
    FROM t3 
    WHERE rnk=1
)
SELECT t1.*,t4.courier_name AS top_courier_2024_02
FROM t1
LEFT JOIN t4  ON t1.zone_id=t4.zone_id
ORDER BY zone_id