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

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