题解 | 统计骑手信息

with 2024_02_delivered as (
    select 
        z.zone_id,
        z.zone_name,
        ifnull(count(o.order_id),0) as peak_2024_02_delivered,
        round(avg(timestampdiff(minute,order_time,delivered_time)),2) as avg_peak_minutes_2024_02
    from 
        Orders o 
    left join
        Couriers c on o.courier_id = c.courier_id
    left join 
        Zones z on o.zone_id = z.zone_id
    where
        (hour(o.order_time) between 11 and 13 
    or 
        hour(o.order_time) between 18 and 20)
    and 
        o.status = 'delivered' 
    and 
        date_format(o.delivered_time,'%Y-%m') = '2024-02'
    group by 
        z.zone_id,z.zone_name
),
2023_02_delivered as (
    select 
        z.zone_id,
        z.zone_name,
        ifnull(count(o.order_id),0) as peak_2023_02_delivered
    from 
        Orders o 
    left join
        Couriers c on o.courier_id = c.courier_id
    left join 
        Zones z on o.zone_id = z.zone_id
    where
        (hour(o.order_time) between 11 and 13 
    or 
        hour(o.order_time) between 18 and 20)
    and 
        o.status = 'delivered' 
    and 
        date_format(o.delivered_time,'%Y-%m') = '2023-02'
    group by 
        z.zone_id,z.zone_name
),
2024_01_delivered as (
    select 
        z.zone_id,
        z.zone_name,
        ifnull(count(o.order_id),0) as peak_2024_01_delivered
    from 
        Orders o 
    left join
        Couriers c on o.courier_id = c.courier_id
    left join 
        Zones z on o.zone_id = z.zone_id
    where
        (hour(o.order_time) between 11 and 13 
    or 
        hour(o.order_time) between 18 and 20)
    and 
        o.status = 'delivered' 
    and 
        date_format(o.delivered_time,'%Y-%m') = '2024-01'
    group by 
        z.zone_id,z.zone_name
),
top1_courier_name as (
    select 
        z.zone_id,
        c.courier_id,
        c.courier_name,
        count(o.order_id) as peak_2024_02_delivered,
        row_number() over(partition by z.zone_id order by count(o.order_id) desc,c.courier_id asc) as rn
    from 
        Orders o 
    left join
        Couriers c on o.courier_id = c.courier_id
    left join 
        Zones z on o.zone_id = z.zone_id
    where
        (hour(o.order_time) between 11 and 13 
    or 
        hour(o.order_time) between 18 and 20)
    and 
        o.status = 'delivered' 
    and 
        date_format(o.delivered_time,'%Y-%m') = '2024-02'
    group by 
        z.zone_id,c.courier_id,c.courier_name
)
select 
    a.zone_id,
    a.zone_name,
    ifnull(b.peak_2023_02_delivered,0) as peak_2023_02_delivered,
    ifnull(a.peak_2024_02_delivered,0) as peak_2024_02_delivered,
    ifnull(c.peak_2024_01_delivered,0) as peak_2024_01_delivered,
    ifnull(a.peak_2024_02_delivered,0) - ifnull(b.peak_2023_02_delivered,0) as yoy_delta,
    ifnull(a.peak_2024_02_delivered,0) - ifnull(c.peak_2024_01_delivered,0) as mom_delta,
    a.avg_peak_minutes_2024_02,
    d.courier_name as top_courier_2024_02
from 
    2024_02_delivered a 
left join 
    2023_02_delivered b on a.zone_id = b.zone_id and a.zone_name = b.zone_name
left join
    2024_01_delivered c on a.zone_id = c.zone_id and a.zone_name = c.zone_name
left join
    top1_courier_name d on a.zone_id = d.zone_id and d.rn = 1