题解 | 统计骑手信息

select t.zone_id,zone_name
,sum(case when status = 'delivered' and date_format(delivered_time,'%Y-%m') = '2023-02' then 1 else 0 end) as peak_2023_02_delivered
,sum(case when status = 'delivered' and date_format(delivered_time,'%Y-%m') = '2024-02' then 1 else 0 end) as peak_2024_02_delivered
,sum(case when status = 'delivered' and date_format(delivered_time,'%Y-%m') = '2024-01' then 1 else 0 end) as peak_2024_01_delivered
,sum(case when status = 'delivered' and date_format(delivered_time,'%Y-%m') = '2024-02' then 1 else 0 end)-sum(case when status = 'delivered' and date_format(delivered_time,'%Y-%m') = '2023-02' then 1 else 0 end) as yoy_delta
,sum(case when status = 'delivered' and date_format(delivered_time,'%Y-%m') = '2024-02' then 1 else 0 end)-sum(case when status = 'delivered' and date_format(delivered_time,'%Y-%m') = '2024-01' then 1 else 0 end) as mom_delta
,max(avg1) as avg_peak_minutes_2024_02
,max(t3.courier_name) as top_courier_2024_02
from Zones t
left join Orders t1 on t.zone_id=t1.zone_id
left join (select t.zone_id
          ,round(avg(TIMESTAMPDIFF(minute,order_time,delivered_time)),2) as avg1
           from Zones t
           left join Orders t1 on t.zone_id=t1.zone_id
           where date_format(delivered_time,'%Y-%m') = '2024-02'
           group by t.zone_id) t2
           on t.zone_id=t2.zone_id
left join (select t.zone_id,courier_name,t1.courier_id 
                 ,row_number()over(partition by t.zone_id order by count(*) desc,t1.courier_id) as rn 
           from Zones t
           left join Orders t1 on t.zone_id=t1.zone_id
           left join Couriers t2 on t1.courier_id=t2.courier_id 
           where date_format(delivered_time,'%Y-%m') = '2024-02'
           group by t.zone_id,courier_name,t1.courier_id) t3
           on t.zone_id=t3.zone_id and rn=1
group by t.zone_id,zone_name