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

with t1 as(select
order_id,
timestampdiff(second,event_time,order_time) as wait_time,
timestampdiff(second,order_time,start_time) as dispatch_time,
case 
    when hour(event_time) between 7 and 8 then '早高峰'
    when hour(event_time) between 9 and 16 then '工作时间'
    when hour(event_time) between 17 and 19 then '晚高峰'
    else '休息时间'
end as period
from tb_get_car_order left join tb_get_car_record using(order_id)
where weekday(event_time) between 0 and 4)
 select
 period,
 count(order_id) 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 t1
 group by period
 order by get_car_num