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

select
    period,
    count(1) as get_car_num,
    round(avg(wait_time)/60 ,1) as avg_wait_time,
    round(avg(dispatch_time)/60,1) as avg_dispatch_time
from(
    select 
        case when time(event_time) >= '07:00:00'
            and time(event_time) < '09:00:00' then '早高峰'
            when time(event_time) >= '09:00:00'
            and time(event_time) < '17:00:00' then '工作时间'
            when time(event_time) >= '17:00:00'
            and time(event_time) < '20:00:00' then '晚高峰'
            else  '休息时间' end as period,
        order_id,
        timestampdiff(second,event_time,end_time) as wait_time
    from 
        tb_get_car_record
    where 
        dayofweek(event_time) between 2 and 6
) as a
left join(
    select 
        order_id,
        timestampdiff(second,order_time,start_time) as dispatch_time
    from 
        tb_get_car_order
) as b
on a.order_id = b.order_id
group by 1
order by get_car_num asc