题解 | 统计骑手信息

with 
t1 as(
    select
        o.zone_id,
        z.zone_name,
        sum(if(left(o.order_time,7)='2023-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)) peak_2023_02_delivered,
        sum(if(left(o.order_time,7)='2024-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)) peak_2024_02_delivered,
        sum(if(left(o.order_time,7)='2024-01' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)) peak_2024_01_delivered,
        sum(if(left(o.order_time,7)='2024-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0))-sum(if(left(o.order_time,7)='2023-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)) yoy_delta,
        sum(if(left(o.order_time,7)='2024-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0))-sum(if(left(o.order_time,7)='2024-01' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)) mom_delta,
        round(
            sum(if(left(o.order_time,7)='2024-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',timestampdiff(minute,o.order_time,o.delivered_time),0))/
            sum(if(left(o.order_time,7)='2024-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)),
            2) avg_peak_minutes_2024_02
    from Orders o join Zones z on o.zone_id=z.zone_id
    group by o.zone_id,z.zone_name),
t2 as(
    select
        o.zone_id,
        c.courier_id,
        c.courier_name top_courier_2024_02,
        sum(if(left(o.order_time,7)='2024-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)) delivered_nums,
        row_number() over(partition by o.zone_id order by 
            sum(if(left(o.order_time,7)='2024-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)) desc,c.courier_id asc) as zone_rank
    from Orders o join Couriers c on o.courier_id=c.courier_id
    group by o.zone_id,c.courier_id,c.courier_name)

select 
    t1.*,
    t2.top_courier_2024_02
from t1 join t2 on t1.zone_id=t2.zone_id and t2.zone_rank=1