题解 | 统计骑手信息

-- 连接商圈表和订单表，统计每个商圈的订单数，以及同比和环比增长额
with temp as (
    select
        z.zone_id,
        z.zone_name,
        sum(if(date_format(o.order_time,'%Y-%m') = '2024-02' , 1 , 0)) as peak_2024_02_delivered,
        sum(if(date_format(o.order_time,'%Y-%m') = '2023-02' , 1 , 0)) as peak_2023_02_delivered,
        sum(if(date_format(o.order_time,'%Y-%m') = '2024-01' , 1 , 0)) as peak_2024_01_delivered,
        sum(if(date_format(o.order_time,'%Y-%m') = '2024-02' , 1 , 0)) - sum(if(date_format(o.order_time,'%Y-%m') = '2023-02' , 1 , 0)) as yoy_delta,
        sum(if(date_format(o.order_time,'%Y-%m') = '2024-02' , 1 , 0)) - sum(if(date_format(o.order_time,'%Y-%m') = '2024-01' , 1 , 0)) as mom_delta
        from Zones z
        left join Orders o  -- 左连接的原因是，现在查找的是各个商圈的订单，有可能在这个时间段，某个商圈没有订单
        on o.zone_id = z.zone_id -- 注意！！！！ 这里不能用where筛选，会破坏原表
        and o.status = 'delivered' 
        and (hour(o.order_time) between 11 and 13 or HOUR(o.order_time) between 18 and 20)
        group by z.zone_id , z.zone_name
),

-- 统计各个商圈的平均送达时间
temp1 as (
    select 
        z.zone_id,
        round(avg(timestampdiff(minute,o.order_time , o.delivered_time)) , 2) as avg_peak_minutes_2024_02
        from Zones z
        left join Orders o
        on o.zone_id = z.zone_id
        where status='delivered'
        and (HOUR(o.order_time) BETWEEN 11 AND 13 OR HOUR(o.order_time) BETWEEN 18 AND 20)
        and date_format(o.order_time,'%Y-%m') = '2024-02'
        group by z.zone_id 
),
-- 找到2024年2月每个商圈订单数最多的骑手
temp2 as (
    select
        z.zone_id,
        c.courier_id,
        c.courier_name,
        row_number() over(partition by z.zone_id order by count(o.order_id) desc , c.courier_id asc) as rnk
        from Zones z
        left join Orders o on o.zone_id = z.zone_id
        left join Couriers c on c.courier_id = o.courier_id
        where status='delivered'
        and (HOUR(o.order_time) BETWEEN 11 AND 13 OR HOUR(o.order_time) BETWEEN 18 AND 20)
        and date_format(o.order_time,'%Y-%m') = '2024-02'
        group by z.zone_id , c.courier_id , c.courier_name
)
-- 进行拼接
select
    temp.zone_id,
    temp.zone_name,
    temp.peak_2023_02_delivered,
    temp.peak_2024_02_delivered,
    temp.peak_2024_01_delivered,
    yoy_delta,
    mom_delta,
    temp1.avg_peak_minutes_2024_02,
    temp2.courier_name as top_courier_2024_02
    from temp
    left join temp1 on temp1.zone_id = temp.zone_id
    left join temp2 on temp2.zone_id = temp.zone_id and temp2.rnk = 1
    order by temp.zone_id , temp.zone_name

有问题滴滴