题解 | 统计骑手信息

with
    orders_encoder as (
        select
            order_id,
            courier_id,
            zone_id,
            order_time,
            delivered_time,
            status,
            TIMESTAMPDIFF(MINUTE, order_time, delivered_time) as spend_time,
            case
                when date_format(order_time, '%Y-%m') = '2023-02' then 1
                else 0
            end as 2023_02,
            case
                when date_format(order_time, '%Y-%m') = '2024-02' then 1
                else 0
            end as 2024_02,
            case
                when date_format(order_time, '%Y-%m') = '2024-01' then 1
                else 0
            end as 2024_01
        from
            Orders
        where
            status = 'delivered'
            AND HOUR(order_time) BETWEEN 11 AND 13
            OR hour(order_time) BETWEEN 18 AND 20
    ),
    peak_delivered as (
        select
            z.zone_id,
            z.zone_name,
            sum(oe.2023_02) as peak_2023_02_delivered,
            sum(oe.2024_02) as peak_2024_02_delivered,
            sum(oe.2024_01) as peak_2024_01_delivered,
            sum(oe.2024_02) - sum(oe.2023_02) as yoy_delta,
            sum(oe.2024_02) - sum(oe.2024_01) as mom_delta
        from
            orders_encoder oe
            left join Zones z on oe.zone_id = z.zone_id
        where
            date_format(oe.order_time, '%Y-%m') = '2023-02'
            or date_format(oe.order_time, '%Y-%m') = '2024-02'
            or date_format(oe.order_time, '%Y-%m') = '2024-01'
        group by
            z.zone_id,
            z.zone_name
    ),
    avg_time as (
        select
            z.zone_id,
            z.zone_name,
            round(avg(oe.spend_time), 2) as avg_peak_minutes_2024_02
        from
            orders_encoder oe
            left join Zones z on oe.zone_id = z.zone_id
        where
            date_format(oe.order_time, '%Y-%m') = '2024-02'
        group by
            z.zone_id,
            z.zone_name
    ),
    max_courier as (
        select
            zone_id,
            zone_name,
            courier_name as top_courier_2024_02
        from
            (
                select
                    zone_id,
                    zone_name,
                    courier_name,
                    dense_rank() over (
                        partition by
                            zone_id
                        order by
                            cnt_takeaway desc,
                            courier_id
                    ) as ranks
                from
                    (
                        select
                            z.zone_id,
                            z.zone_name,
                            c.courier_name,
                            c.courier_id,
                            count(oe.order_id) as cnt_takeaway
                        from
                            orders_encoder oe
                            left join Zones z on oe.zone_id = z.zone_id
                            left join Couriers c on oe.courier_id = c.courier_id
                        where
                            date_format(oe.order_time, '%Y-%m') = '2024-02'
                        group by
                            z.zone_id,
                            z.zone_name,
                            c.courier_name,
                            c.courier_id
                    ) tb1
            ) tb2
        where
            ranks = 1
    )
select 
    peak_delivered.zone_id,
    peak_delivered.zone_name,
    peak_delivered.peak_2023_02_delivered,
    peak_delivered.peak_2024_02_delivered,
    peak_delivered.peak_2024_01_delivered,
    peak_delivered.yoy_delta,
    peak_delivered.mom_delta,
    avg_time.avg_peak_minutes_2024_02,
    max_courier.top_courier_2024_02
from 
    peak_delivered
    join avg_time on peak_delivered.zone_id = avg_time.zone_id
    join max_courier on peak_delivered.zone_id = max_courier.zone_id

感觉代码过于冗余了，请求大佬指点