题解 | 统计骑手信息

-- t1:总表
WITH t1 AS (
		SELECT z.zone_id,
			 z.zone_name,
			 z.zone_type,
			 o.order_time,
			 o.delivered_time,
			 o.status,
			 o.fee,
			 c.courier_id,
			 c.courier_name,
			 c.grade
    FROM Zones z 
	LEFT JOIN Orders o ON o.zone_id = z.zone_id
	LEFT JOIN Couriers c ON c.courier_id = o.courier_id
	WHERE (HOUR(o.order_time) BETWEEN 11 AND 13 OR HOUR(o.order_time) BETWEEN 18 AND 20)
		  AND o.status='delivered' 
),
-- 计算该商圈送达订单数最多的骑手姓名
-- step1:计算送达订单数
t2 AS (
     SELECT zone_id,
	 		courier_id,
			courier_name,
			count(*)AS cnt
	 FROM t1 
	 WHERE DATE_FORMAT(order_time, '%Y-%m') = '2024-02'
	 GROUP BY zone_id,courier_id,courier_name
),
-- step2:计算排名情况
t3 AS (
     SELECT zone_id,
	 		courier_name,
			ROW_NUMBER()OVER(
				PARTITION BY zone_id 
				ORDER BY cnt DESC,courier_id ASC)AS rn
	 FROM t2
),
-- step3:筛选送达订单最多的骑手
t4 AS (
		 SELECT zone_id,courier_name
		 FROM t3
		 WHERE rn = 1
)
SELECT t1.zone_id,
	   t1.zone_name,
	   COUNT(CASE 
	   			WHEN DATE_FORMAT(t1.order_time,'%Y-%m') = '2023-02' THEN t1.order_time 
			 END) AS peak_2023_02_delivered,
	   COUNT(CASE 
	   			WHEN DATE_FORMAT(t1.order_time,'%Y-%m') = '2024-02' THEN t1.order_time 
			END) AS peak_2024_02_delivered,
	   COUNT(CASE 
	   			WHEN DATE_FORMAT(t1.order_time,'%Y-%m') = '2024-01' THEN t1.order_time 
			END) AS peak_2024_01_delivered,
	   COUNT(CASE 
	   			WHEN DATE_FORMAT(t1.order_time,'%Y-%m') = '2024-02' THEN t1.order_time 
			END) - COUNT(CASE 
							WHEN DATE_FORMAT(t1.order_time,'%Y-%m') = '2023-02' 
								THEN t1.order_time 
						 END) AS yoy_delta,
	   COUNT(CASE 
	   			WHEN DATE_FORMAT(t1.order_time,'%Y-%m') = '2024-02' THEN t1.order_time
		     END) - COUNT(CASE 
			 				WHEN DATE_FORMAT(t1.order_time,'%Y-%m') = '2024-01' 
								THEN t1.order_time 
						  END) AS mom_delta,
	   ROUND(avg(CASE 
					WHEN DATE_FORMAT(t1.order_time,'%Y-%m') = '2024-02' 
						THEN TIMESTAMPDIFF(MINUTE,t1.order_time,t1.delivered_time)
				 END),2) AS avg_peak_minutes_2024_02,
	   t4.courier_name AS top_courier_2024_02
FROM t1
LEFT JOIN t4 ON t1.zone_id = t4.zone_id
GROUP BY t1.zone_id,t1.zone_name,t4.courier_name
ORDER BY t1.zone_id