select
period,
count(order_id) get_car_num,
round(avg(wait_time),1) avg_wait_time,
round(avg(dispatch_time),1) avg_dispatch_time
from (
select t2.order_id,if(weekday(date(event_time))<5,1,0) xingqi,
case when
hour(event_time) in (7,8) then '早高峰'
when hour(event_time) in (17,18,19) then '晚高峰'
when hour(event_time) >=9 and hour(event_time) < 17 then '工作时间'
else '休息时间' end period ,
timestampdiff(second,event_time,end_time)/60 wait_time ,
timestampdiff(second,order_time,start_time)/60 dispatch_time
from tb_get_car_record t1
left join tb_get_car_order t2
on t1.order_id=t2.order_id ) a
where xingqi=1
group by period
order by get_car_num