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

with
t1 as(
    select
        o.order_id,
        case 
            when time(r.event_time)>='07:00:00' and time(r.event_time)<'09:00:00' then '早高峰'
            when time(r.event_time)>='09:00:00' and time(r.event_time)<'17:00:00' then '工作时间'
            when time(r.event_time)>='17:00:00' and time(r.event_time)<'20:00:00' then '晚高峰'
            else '休息时间'
            end as period,
        timestampdiff(second,r.event_time,o.order_time) as await_time,
        timestampdiff(second,o.order_time,o.start_time) as dispatch_time,
        o.fare is null as if_completed
    from 
        tb_get_car_order o left join tb_get_car_record r using(order_id)
    where 
        weekday(event_time) between 0 and 4)

select
    period,
    count(order_id) as get_car_num,
    round(sum(await_time)/count(order_id)/60,1) as avg_await_time,
    round(sum(if(if_completed=0,dispatch_time,0))/count(if(if_completed=0,order_id,null))/60,1) as avg_dispatch_time
from 
    t1
group by 
    period
order by 
    get_car_num