题解 | 统计骑手信息

#前n-1个字段
with t as (select zone_id,zone_name,
sum(if(date_format(delivered_time,'%Y-%m') = '2023-02',1,0)) as peak_2023_02_delivered,
sum(if(date_format(delivered_time,'%Y-%m') = '2024-02',1,0)) as peak_2024_02_delivered,
sum(if(date_format(delivered_time,'%Y-%m') = '2024-01',1,0)) as peak_2024_01_delivered,
sum(if(date_format(delivered_time,'%Y-%m') = '2024-02',1,0)) - sum(if(date_format(delivered_time,'%Y-%m') = '2023-02',1,0)) as yoy_delta,
sum(if(date_format(delivered_time,'%Y-%m') = '2024-02',1,0)) - sum(if(date_format(delivered_time,'%Y-%m') = '2024-01',1,0)) as mom_delta,
round(sum(if(date_format(delivered_time,'%Y-%m') = '2024-02',timestampdiff(minute,order_time,delivered_time),0)) / sum(if(date_format(delivered_time,'%Y-%m') = '2024-02',1,0)),2) as avg_peak_minutes_2024_02
 from Zones
join Orders using(zone_id)
join Couriers using(courier_id)
where status='delivered' and (HOUR(order_time) BETWEEN 11 AND 13 OR HOUR(order_time) BETWEEN 18 AND 20)
group by zone_id,zone_name
order by zone_id),
#最后一个字段
t1 as (
select zone_id,courier_name,rank()over(partition by zone_id order by 次数 desc,courier_id asc) as 排名 from (
select zone_id,courier_id,courier_name,count(date_format(delivered_time,'%Y-%m') = '2024-02') as 次数 from Zones
join Orders using(zone_id)
join Couriers using(courier_id)
group by zone_id,courier_id,courier_name) a
)

select zone_id,zone_name,peak_2023_02_delivered,peak_2024_02_delivered,
peak_2024_01_delivered,yoy_delta,mom_delta,avg_peak_minutes_2024_02, courier_name as top_courier_2024_02
from t
join t1 using(zone_id)
where 排名 = 1