题解 | 统计骑手信息

with a1 as (
    select 
    zone_id,
    courier_id,
    courier_name,
    count(*) as courier_count,
    rank() over(partition by zone_id order by count(*) desc ,courier_id) as rk # partition by 和 count 的口径不同
    from Orders
    join Zones
    using(zone_id)
    join Couriers
    using(courier_id) 
    where HOUR(order_time) BETWEEN 11 AND 13 OR HOUR(order_time) BETWEEN 18 AND 20 
    and status='delivered'
    and order_time between '2024-02-01' and '2024-02-29'
    group by zone_id,courier_id,courier_name
)

select 
zone_id,
zone_name,
sum(case when order_time between '2023-02-01' and '2023-02-28' then 1 else 0 end) as peak_2023_02_delivered,
sum(case when order_time between '2024-02-01' and '2024-02-29' then 1 else 0 end) as peak_2024_02_delivered,
sum(case when order_time between '2024-01-01' and '2024-01-31' then 1 else 0 end) as peak_2024_01_delivered,
sum(case when order_time between '2024-02-01' and '2024-02-29' then 1 else 0 end)-sum(case when order_time between '2023-02-01' and '2023-02-28' then 1 else 0 end) as yoy_delta,
sum(case when order_time between '2024-02-01' and '2024-02-29' then 1 else 0 end)-sum(case when order_time between '2024-01-01' and '2024-01-31' then 1 else 0 end) as mom_delta,
round(avg(case when order_time between '2024-02-01' and '2024-02-29' then TIMESTAMPDIFF(MINUTE, order_time, delivered_time) end),2) as avg_peak_minutes_2024_02,
max(a1.courier_name) as top_courier_2024_02
from Orders
join Zones
using(zone_id)
join Couriers
using(courier_id)
join a1 
using(zone_id)
where (HOUR(order_time) BETWEEN 11 AND 13 OR HOUR(order_time) BETWEEN 18 AND 20)  # 带OR的请带括号，不然易和后面的AND混淆
and status='delivered'
and rk = 1 
group by zone_id,zone_name
order by zone_id,zone_name