题解 | 统计骑手信息

WITH A as (
    select zone_id
    ,zone_name
    ,sum( if( date_format( delivered_time,'%Y-%m') = '2023-02' and (HOUR( order_time) in (11,12,13,18,19,20)), 1, 0)) as peak_2023_02_delivered
    ,sum( if( date_format( delivered_time,'%Y-%m') = '2024-02' and (HOUR( order_time) in (11,12,13,18,19,20)), 1, 0)) as peak_2024_02_delivered
    ,sum( if( date_format( delivered_time,'%Y-%m') = '2024-01' and (HOUR( order_time) in (11,12,13,18,19,20)), 1, 0)) as peak_2024_01_delivered
    ,sum( if( date_format( delivered_time,'%Y-%m') = '2024-02' and (HOUR( order_time) in (11,12,13,18,19,20)), 1, 0)) - sum( if( date_format(   delivered_time,'%Y-%m') = '2023-02' and (HOUR(order_time) in (11,12,13,18,19,20)), 1, 0)) as yoy_delta
    ,sum( if( date_format(delivered_time,'%Y-%m') = '2024-02' and (HOUR(order_time) in (11,12,13,18,19,20)), 1, 0)) - sum( if( date_format(delivered_time,  '%Y-%m') = '2024-01' and (HOUR( order_time) in (11,12,13,18,19,20)), 1, 0)) as mom_delta
    ,round( avg( if( date_format( delivered_time,'%Y-%m') = '2024-02' and (HOUR( order_time) in (11,12,13,18,19,20)), timestampdiff(minute,order_time,  delivered_time), null)), 2) as avg_peak_minutes_2024_02
    from Zones z left join Orders o using(zone_id)
    where status = 'delivered'
    group by zone_id, zone_name
),
B as (
    select zone_id
    ,courier_id
    ,courier_name
    ,sum( if( date_format( delivered_time,'%Y-%m') = '2024-02' and (HOUR( order_time) in (11,12,13,18,19,20)), 1, 0)) as 24_2_d
    ,row_number()over(partition by zone_id order by sum( if( date_format( delivered_time,'%Y-%m') = '2024-02' and (HOUR( order_time) in (11,12,13,18,19,20)), 1, 0)) DESC, courier_id) as rk
    from Orders left join Couriers using(courier_id)
    where status = 'delivered'
    group by zone_id, courier_id, courier_name
)
select zone_id
,zone_name
,peak_2023_02_delivered
,peak_2024_02_delivered
,peak_2024_01_delivered
,yoy_delta
,mom_delta
,avg_peak_minutes_2024_02
,courier_name as top_courier_2024_02
from A left join B using(zone_id)
where rk = 1
order by zone_id, zone_name;