题解 | 工作日各时段叫车量、等待接单时间和调度时间

with t1 as (
    select
        t.uid,
        t.event_time,
        t.end_time,
        t.order_id,
        t.period,
        tb_get_car_order.order_time,
        tb_get_car_order.start_time,
        tb_get_car_order.finish_time,
        tb_get_car_order.mileage
    from (
        select
            *,
            case 
                when substr(event_time,12,8) >= '07:00:00' and substr(event_time,12,8) < '09:00:00' then '早高峰'
                when substr(event_time,12,8) >= '09:00:00' and substr(event_time,12,8) < '17:00:00' then '工作时间'
                when substr(event_time,12,8) >= '17:00:00' and substr(event_time,12,8) < '20:00:00' then '晚高峰'
                else '休息时间'
            end as period
        from 
            tb_get_car_record
    ) as t
    left join 
        tb_get_car_order
    on 
        t.order_id = tb_get_car_order.order_id
    where
        weekday(date(t.event_time)) between 0 and 4
)

select
    t2.*,
    t3.avg_dispatch_time
from (
    select
    period,
    count(*) as get_car_num,
    round(avg(timestampdiff(second,event_time,end_time) / 60),1) as avg_wait_time
from 
    t1
group by 
    period
) as t2
left join (
    select
        period,
        round(avg(timestampdiff(second,order_time,start_time) / 60),1) as avg_dispatch_time
    from 
        t1
    where 
        mileage is not null
    group by
        period
) as t3
on 
    t2.period = t3.period
order by 
    t2.get_car_num