题解 | 统计骑手信息

# 一个注意点，计算avg的时候，else null 而不是else 0，因为0会使得分母变大。
with t1 as (
select
    o.zone_id
    ,z.zone_name
    ,count(case when date_format(order_time, '%Y-%m') = '2023-02' and (HOUR(order_time) BETWEEN 11 AND 13 OR HOUR(order_time) BETWEEN 18 AND 20)
    then order_id else null end) as peak_2023_02_delivered
    ,count(case when date_format(order_time, '%Y-%m') = '2024-02' and (HOUR(order_time) BETWEEN 11 AND 13 OR HOUR(order_time) BETWEEN 18 AND 20)
    then order_id else null end) as peak_2024_02_delivered
    ,count(case when date_format(order_time, '%Y-%m') = '2024-01' and (HOUR(order_time) BETWEEN 11 AND 13 OR HOUR(order_time) BETWEEN 18 AND 20)
    then order_id else null end) as peak_2024_01_delivered
    ,avg(case when date_format(order_time, '%Y-%m') = '2024-02' and (HOUR(order_time) BETWEEN 11 AND 13 OR HOUR(order_time) BETWEEN 18 AND 20) 
    then TIMESTAMPDIFF(MINUTE, order_time, delivered_time) else null end) as avg_peak_minutes_2024_02
from Orders o
left join Zones z on o.zone_id = z.zone_id
where o.status = 'delivered'
group by 1,2
),

t2 as (
select
    o.zone_id
    ,o.courier_id
    ,c.courier_name 
    ,count(distinct order_id) as order_count_2024_02
    ,row_number() over(partition by o.zone_id order by count(distinct order_id) desc, o.courier_id asc) as rk
from Orders o
left join Couriers c on o.courier_id = c.courier_id
where date_format(order_time, '%Y-%m') = '2024-02' and HOUR(order_time) in (11,12,13,18,19,20)
and o.status = 'delivered'
group by o.zone_id,o.courier_id,c.courier_name 
# order by order_count_2024_02 desc, o.courier_id asc
)


select
    t1.zone_id
    ,zone_name
    ,peak_2023_02_delivered
    ,peak_2024_02_delivered
    ,peak_2024_01_delivered
    ,peak_2024_02_delivered - peak_2023_02_delivered as yoy_delta
    ,peak_2024_02_delivered - peak_2024_01_delivered as mom_delta
    ,round(avg_peak_minutes_2024_02, 2) as avg_peak_minutes_2024_02
    ,t2.courier_name as top_courier_2024_02
from t1
    left join t2 on t1.zone_id = t2.zone_id
where t2.rk = 1
order by t1.zone_id asc, zone_name asc
;