题解 | 统计骑手信息

with rkrk as (
    select zone_id,courier_name from
    (
        select zone_id,c.courier_id,courier_name,rank()over(partition by zone_id order by count(*) desc,c.courier_id) rk
        from Orders o join Couriers c on o.courier_id = c.courier_id
        group by zone_id,courier_name,c.courier_id
    ) rk1
    where rk1.rk = 1   
),
time as (
    select zone_id,round(avg(timestampdiff(minute,order_time,delivered_time)),2) avg_peak_minutes_2024_02
    from Orders
    where delivered_time like '2024-02%' and ((hour(order_time) between 11 and 13) or (hour(order_time) between 18 and 20))
    group by zone_id
)
select z.zone_id,zone_name,
sum(if(delivered_time like '2023-02%' and ((hour(order_time) between 11 and 13) or (hour(order_time) between 18 and 20)),1,0)) peak_2023_02_delivered,
sum(if(delivered_time like '2024-02%' and ((hour(order_time) between 11 and 13) or (hour(order_time) between 18 and 20)),1,0)) peak_2024_02_delivered,
sum(if(delivered_time like '2024-01%' and ((hour(order_time) between 11 and 13) or (hour(order_time) between 18 and 20)),1,0)) peak_2024_01_delivered,
sum(if(delivered_time like '2024-02%',1,0)) - sum(if(delivered_time like '2023-02%',1,0)) yoy_delta,
sum(if(delivered_time like '2024-02%',1,0)) - sum(if(delivered_time like '2024-01%',1,0)) mom_delta,
avg_peak_minutes_2024_02,
rkrk.courier_name top_courier_2024_02
from Orders o join Zones z on o.zone_id = z.zone_id
join Couriers c on o.courier_id = c.courier_id
join time t on z.zone_id = t.zone_id
join rkrk on z.zone_id = rkrk.zone_id
group by z.zone_id,rkrk.courier_name
order by zone_id