题解 | 统计骑手信息

WITH t0 AS (
	SELECT
		z.zone_id,
		zone_name,
		sum(
		IF
			(
				YEAR ( order_time )= 2023 
				AND MONTH ( order_time )= 2 
				AND ((
						HOUR ( order_time ) BETWEEN 11 
						AND 13 
						) 
				OR ( HOUR ( order_time ) BETWEEN 18 AND 20 )),
				1,
				0 
			)) AS peak_2023_02_delivered,
		sum(
		IF
			(
				YEAR ( order_time )= 2024 
				AND MONTH ( order_time )= 2 
				AND ((
						HOUR ( order_time ) BETWEEN 11 
						AND 13 
						) 
				OR ( HOUR ( order_time ) BETWEEN 18 AND 20 )),
				1,
				0 
			)) AS peak_2024_02_delivered,
		sum(
		IF
			(
				YEAR ( order_time )= 2024 
				AND MONTH ( order_time )= 1 
				AND ((
						HOUR ( order_time ) BETWEEN 11 
						AND 13 
						) 
				OR ( HOUR ( order_time ) BETWEEN 18 AND 20 )),
				1,
				0 
			)) AS peak_2024_01_delivered,
		sum(
		IF
			(
				YEAR ( order_time )= 2024 
				AND MONTH ( order_time )= 2 
				AND ((
						HOUR ( order_time ) BETWEEN 11 
						AND 13 
						) 
				OR ( HOUR ( order_time ) BETWEEN 18 AND 20 )),
				1,
				0 
			))- sum(
		IF
			(
				YEAR ( order_time )= 2023 
				AND MONTH ( order_time )= 2 
				AND ((
						HOUR ( order_time ) BETWEEN 11 
						AND 13 
						) 
				OR ( HOUR ( order_time ) BETWEEN 18 AND 20 )),
				1,
				0 
			)) AS yoy_delta,
		sum(
		IF
			(
				YEAR ( order_time )= 2024 
				AND MONTH ( order_time )= 2 
				AND ((
						HOUR ( order_time ) BETWEEN 11 
						AND 13 
						) 
				OR ( HOUR ( order_time ) BETWEEN 18 AND 20 )),
				1,
				0 
			))- sum(
		IF
			(
				YEAR ( order_time )= 2024 
				AND MONTH ( order_time )= 1 
				AND ((
						HOUR ( order_time ) BETWEEN 11 
						AND 13 
						) 
				OR ( HOUR ( order_time ) BETWEEN 18 AND 20 )),
				1,
				0 
			)) AS mom_delta 
	FROM
		Orders o
		LEFT JOIN Zones z ON z.zone_id = o.zone_id 
	WHERE
		STATUS = 'delivered' 
	GROUP BY
		1 
	),
	t1 AS (
	SELECT
		zone_id,
		round( avg( TIMESTAMPDIFF( MINUTE, order_time, delivered_time )), 2 ) AS avg_peak_minutes_2024_02 
	FROM
		Orders 
	WHERE
		YEAR ( order_time )= 2024 
		AND MONTH ( order_time )= 2 
		AND ((
				HOUR ( order_time ) BETWEEN 11 
				AND 13 
				) 
		OR ( HOUR ( order_time ) BETWEEN 18 AND 20 )) 
	GROUP BY
		1 
	),
	t2 AS (
	SELECT
		zone_id,
		o.courier_id,
		courier_name AS top_courier_2024_02,
		count(*) AS dt,
		row_number() over ( PARTITION BY zone_id ORDER BY count(*) DESC, o.courier_id ) AS rk 
	FROM
		Orders o
		LEFT JOIN Couriers c ON o.courier_id = c.courier_id 
	WHERE
		YEAR ( order_time )= 2024 
		AND MONTH ( order_time )= 2 
		AND ((
				HOUR ( order_time ) BETWEEN 11 
				AND 13 
				) 
		OR ( HOUR ( order_time ) BETWEEN 18 AND 20 )) 
	GROUP BY
		1,2 
	) SELECT
	t0.zone_id,
	t0.zone_name,
	peak_2023_02_delivered,
	peak_2024_02_delivered,
	peak_2024_01_delivered,
	yoy_delta,
	mom_delta,
	avg_peak_minutes_2024_02,
	top_courier_2024_02 
FROM
	t0
	JOIN t1 ON t0.zone_id = t1.zone_id
	JOIN t2 ON t0.zone_id = t2.zone_id 
WHERE
	rk =1