题解 | 统计骑手信息

with
    tmp as (
        select
            t1.zone_id,
            t1.zone_name,
            date_format(order_time, '%Y-%m') order_time,
            avg(TIMESTAMPDIFF(MINUTE, order_time, delivered_time)) as delivered_time,
            count(order_id) as order_num
        FROM
            Zones t1,
            Couriers t2,
            Orders t3
        where
            t1.zone_id = t3.zone_id
            and t2.courier_id = t3.courier_id
            and status = 'delivered'
            and (
                HOUR(order_time) BETWEEN 11 AND 13
                OR HOUR(order_time) BETWEEN 18 AND 20
            )
            and date_format(order_time, '%Y-%m') in ('2024-02', '2024-01', '2023-02')
        group by
            t1.zone_id,
            t1.zone_name,
            date_format(order_time, '%Y-%m')
    ),
    tmp2 as (
        SELECT
            zone_name,
            courier_name
        FROM
            (
                SELECT
                    zone_name,
                    courier_name,
                    row_number() over (
                        partition by
                            zone_name
                        order by
                            o_num desc,
                            zone_id
                    ) as rk
                FROM
                    (
                        SELECT
                            zone_name,
                            t1.zone_id,
                            courier_name,
                            count(order_id) as o_num
                        FROM
                            Zones t1,
                            Couriers t2,
                            Orders t3
                        where
                            t1.zone_id = t3.zone_id
                            and t2.courier_id = t3.courier_id
                            and status = 'delivered'
                            and (
                                HOUR(order_time) BETWEEN 11 AND 13
                                OR HOUR(order_time) BETWEEN 18 AND 20
                            )
                            and date_format(order_time, '%Y-%m') in ('2024-02')
                        group by
                            zone_name,
                            courier_name,
                            t1.zone_id
                    ) a
            ) b
        where
            rk = 1
    ),
    tmp3 as (
        SELECT
            zone_id,
            zone_name,
            max(
                case
                    when order_time = '2023-02' then order_num
                    else 0
                end
            ) peak_2023_02_deliv,
            max(
                case
                    when order_time = '2024-02' then order_num
                    else 0
                end
            ) peak_2024_02_deliv,
            max(
                case
                    when order_time = '2024-01' then order_num
                    else 0
                end
            ) peak_2024_01_deliv,
            round(
                max(
                    case
                        when order_time = '2024-02' then delivered_time
                        else 0
                    end
                ),
                2
            ) avg_peak_minutes_2024_02
        FROM
            tmp
        group by
            zone_id,
            zone_name
    )
SELECT
    t2.zone_id,
    t2.zone_name,
    peak_2023_02_deliv as peak_2023_02_delivered,
    peak_2024_02_deliv as peak_2024_02_delivered,
    peak_2024_01_deliv as peak_2024_01_delivered,
    peak_2024_02_deliv - peak_2023_02_deliv as yoy_delta,
    peak_2024_02_deliv - peak_2024_01_deliv as mom_delta,
    avg_peak_minutes_2024_02,
    t1.courier_name as top_courier_2024_02
FROM
    tmp2 t1,
    tmp3 t2
where
    t1.zone_name = t2.zone_name
order by
    t2.zone_id,
    t2.zone_name