题解 | 统计骑手信息

with
    t1 as (
        select /*group by 本身会去重无需distinct*/
            zone_id,
            zone_name,
            sum(
                if (year(order_time) = 2023
                and month(order_time) = 2,
                1,
                0)
            ) as peak_2023_02_delivered,
            sum(
                if (year(order_time) = 2024
                and month(order_time) = 2,
                1,
                0)
            ) as peak_2024_02_delivered,
            sum(
                if (year(order_time) = 2024
                and month(order_time) = 1,
                1,
                0)
            ) as peak_2024_01_delivered,
            sum(
                if (year(order_time) = 2024
                and month(order_time) = 2,
                1,
                0)
            ) - sum(
                if (year(order_time) = 2023
                and month(order_time) = 2,
                1,
                0)
            ) as yoy_delta,
            sum(
                if (year(order_time) = 2024
                and month(order_time) = 2,
                1,
                0)
            ) - sum(
                if (year(order_time) = 2024
                and month(order_time) = 1,
                1,
                0)
            ) as mom_delta
        from
            Zones
            join Orders 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"
        group by
            zone_id,
            zone_name
    ),
    t2 as (
        select zone_id
        ,avg_peak_minutes_2024_02
        ,courier_name as top_courier_2024_02
        from(
        select zone_id
        ,avg_peak_minutes_2024_02
        ,courier_id
        ,courier_name
        ,row_number()over(partition by zone_id order by cnt desc, courier_id) as rk
        from(
        select
            zone_id,
            zone_name,
            courier_id,
            courier_name,
            round(avg(
                timestampdiff (minute, order_time, delivered_time)
            ) over (
                partition by
                    zone_id
            ),2) as avg_peak_minutes_2024_02,
            count(courier_id) over(partition by zone_id,courier_id) as cnt
        from Zones
        join Orders using(zone_id)
        join Couriers using(courier_id)
        where
            year(order_time) = 2024
            and month(order_time) = 2
            and (
                hour(order_time) between 11 and 13
                or hour(order_time) between 18 and 20
            )
            and status = "delivered"
        ) t
        ) tt
        where rk = 1
    )

select distinct zone_id
,zone_name
,peak_2023_02_delivered
,peak_2024_02_delivered
,peak_2024_01_delivered
,yoy_delta
,mom_delta
,avg_peak_minutes_2024_02
,top_courier_2024_02
from t1
join t2 using(zone_id)