题解 | 统计骑手信息

with o as (
    select order_id,courier_id,zone_id,date_format(delivered_time,'%Y%m') d_f ,timestampdiff(minute,order_time,delivered_time) t_d
    from Orders 
    where ((hour(order_time) between 11 and 13) 
    or (hour(order_time) between 18 and 20))
    and status='delivered'
    ),
a as (
    select o.zone_id,c.courier_id,courier_name,count(1) c_c,rank() over(partition by o.zone_id order by count(1) desc,c.courier_id asc ) r
    from Couriers c 
    inner join o on c.courier_id = o.courier_id
    inner join Zones on Zones.zone_id = o.zone_id
    where d_f=202402
    group by o.zone_id,c.courier_id
)

select z.zone_id,
    zone_name,
    sum(if(d_f=202302,1,0)) peak_2023_02_delivered,
    sum(if(d_f=202402,1,0)) peak_2024_02_delivered,
    sum(if(d_f=202401,1,0)) peak_2024_01_delivered,
    sum(if(d_f=202402,1,0))-sum(if(d_f=202302,1,0)) yoy_delta,
    sum(if(d_f=202402,1,0))-sum(if(d_f=202401,1,0)) mom_delta,
    round(sum(if(d_f=202402,t_d,0))/sum(if(d_f=202402,1,0)),2) avg_peak_minutes_2024_02,
    max(courier_name) top_courier_2024_02
from  o
inner join Zones z
on o.zone_id = z.zone_id
inner join a 
on a.zone_id = z.zone_id and r=1
group by z.zone_id,zone_name
order by z.zone_id