题解 | 统计骑手信息

select t1.zone_id, t1.zone_name, t1.peak_2023_02_delivered,
t1.peak_2024_02_delivered, t1.peak_2024_01_delivered,
t1.yoy_delta, t1.mom_delta, t1.avg_peak_minutes_2024_02, 
t2.courier_name as top_courier_2024_02
from 
(select z.zone_id, 
    z.zone_name,
    COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2023-02'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
) AS peak_2023_02_delivered,

    COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2024-02'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
) AS peak_2024_02_delivered,

    COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2024-01'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
) AS peak_2024_01_delivered,

    (COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2024-02'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
) - COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2023-02'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
)) as yoy_delta,
     (COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2024-02'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
) - COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2024-01'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
)) as mom_delta,

    round(sum(CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2024-02'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN TIMESTAMPDIFF(minute, o.order_time, o.delivered_time)
    END)/ (COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2024-02'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
)),2) as avg_peak_minutes_2024_02
from Zones z
join Orders o on z.zone_id = o.zone_id
join Couriers c on o.courier_id = c.courier_id
group by z.zone_id, z.zone_name) t1
join 
(select t.zone_id, 
    t.zone_name,
    t.courier_name
    from (
select z.zone_id, 
    z.zone_name,
    c.courier_id,
    c.courier_name,
    COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2024-02'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
) as peak_2024_02_delivered1,
    row_number() over(partition by z.zone_id, z.zone_name order by COUNT(
    CASE 
        WHEN 
            DATE_FORMAT(o.delivered_time, '%Y-%m') = '2024-02'
            AND ((DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '11:00' AND '13:59' 
                OR DATE_FORMAT(o.delivered_time, '%H:%i') BETWEEN '18:00' AND '20:59'))
        THEN o.order_id  
    END
) desc, c.courier_id asc) rk
from Zones z
join Orders o on z.zone_id = o.zone_id
join Couriers c on o.courier_id = c.courier_id
group by z.zone_id, z.zone_name, c.courier_id, c.courier_name) t 
where t.rk = 1) t2
on t1.zone_id = t2.zone_id

只有靠耐心，一点点去做