题解 | 统计骑手信息

with
    tab as (
        # 统计同比、环比增量和平均运送时长
        select
            zone_id,
            zone_name,
            peak_2023_02_delivered,
            peak_2024_02_delivered,
            peak_2024_01_delivered,
            peak_2024_02_delivered - peak_2023_02_delivered as yoy_delta,
            peak_2024_02_delivered - peak_2024_01_delivered as mom_delta,
            round(total_minutes / peak_2024_02_delivered, 2) as avg_peak_minutes_2024_02
        from
            (
                select
                    zone_id,
                    zone_name,
                    # 2023-02 高峰时段已送达订单数
                    count(
                        case
                            when status = 'delivered'
                            and order_time like '2023-02%'
                            and (
                                HOUR(order_time) BETWEEN 11 AND 13
                                OR HOUR(order_time) BETWEEN 18 AND 20
                            ) then 1
                            else null
                        end
                    ) as peak_2023_02_delivered,
                    # 2024-02 高峰时段已送达订单数
                    count(
                        case
                            when status = 'delivered'
                            and order_time like '2024-02%'
                            and (
                                HOUR(order_time) BETWEEN 11 AND 13
                                OR HOUR(order_time) BETWEEN 18 AND 20
                            ) then 1
                            else null
                        end
                    ) as peak_2024_02_delivered,
                    # 2024-01 高峰时段已送达订单数
                    count(
                        case
                            when status = 'delivered'
                            and order_time like '2024-01%'
                            and (
                                HOUR(order_time) BETWEEN 11 AND 13
                                OR HOUR(order_time) BETWEEN 18 AND 20
                            ) then 1
                            else null
                        end
                    ) as peak_2024_01_delivered,
                    # 2024-02 高峰时段总运送分钟
                    sum(
                        case
                            when status = 'delivered'
                            and order_time like '2024-02%'
                            and (
                                HOUR(order_time) BETWEEN 11 AND 13
                                OR HOUR(order_time) BETWEEN 18 AND 20
                            ) then TIMESTAMPDIFF(MINUTE, order_time, delivered_time)
                        end
                    ) as total_minutes
                from
                    # 连接所有表
                    (
                        select
                            o.order_id,
                            o.courier_id,
                            c.courier_name,
                            o.zone_id,
                            z.zone_name,
                            o.order_time,
                            o.delivered_time,
                            o.status
                        from
                            Orders as o
                            left join Zones as z on o.zone_id = z.zone_id
                            left join Couriers as c on o.courier_id = c.courier_id
                    ) as temp
                group by
                    zone_id,
                    zone_name
            ) as temp2
    )
    # 连接with创建的临时表
select
    tab.zone_id,
    tab.zone_name,
    tab.peak_2023_02_delivered,
    tab.peak_2024_02_delivered,
    tab.peak_2024_01_delivered,
    tab.yoy_delta,
    tab.mom_delta,
    tab.avg_peak_minutes_2024_02,
    final.courier_name as top_courier_2024_02
from
    (
        # 筛选排名为1的骑手
        select
            zone_id,
            zone_name,
            courier_name
        from
            (
                # 降序计算排名
                select
                    zone_id,
                    zone_name,
                    courier_name,
                    row_number() over (
                        partition by
                            zone_id,
                            zone_name
                        order by
                            counting desc,
                            courier_id asc
                    ) as ranking
                from
                    (
                        # 按照地区id、地区名、骑手id分组统计配送次数，避免重名现象
                        select
                            zone_id,
                            zone_name,
                            courier_id,
                            courier_name,
                            count(
                                case
                                    when status = 'delivered'
                                    and order_time like '2024_02%'
                                    and (
                                        HOUR(order_time) BETWEEN 11 AND 13
                                        OR HOUR(order_time) BETWEEN 18 AND 20
                                    ) then 1
                                    else null
                                end
                            ) as counting
                        from
                            (
                                # 单独统计各2024_02高峰时段各商圈送达订单最多的骑手姓名，并列按Id升序
                                select
                                    o.order_id,
                                    o.courier_id,
                                    c.courier_name,
                                    o.zone_id,
                                    z.zone_name,
                                    o.order_time,
                                    o.delivered_time,
                                    o.status
                                from
                                    Orders as o
                                    left join Zones as z on o.zone_id = z.zone_id
                                    left join Couriers as c on o.courier_id = c.courier_id
                            ) as temp
                        group by
                            zone_id,
                            zone_name,
                            courier_id
                        order by
                            zone_id asc
                    ) as temp2
            ) as temp3
        where
            ranking <= 1
    ) as final
    inner join tab on final.zone_id = tab.zone_id