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

with a as(
 select a.order_id ,a.uid,event_time '开始时间',order_time,start_time, finish_time,
 round(timestampdiff(second,event_time,order_time)/60,1) '等待时间',round(timestampdiff(second,order_time,start_time)/60,1)'调度时间'
 from tb_get_car_order a
 join tb_get_car_record
 using(order_id)
 where dayofweek(event_time) between 2 and 6
 )
 select period,count(period) as get_car_num,round(avg(等待时间),1) as avg_wait_time,round(avg(调度时间),1) as avg_dispatch_time
 from(
 select *,
	  case when hour(开始时间) between 7 and 9 and hour(开始时间)<>9 then "早高峰"
           when hour(开始时间) between 9 and 17 and hour(开始时间)<>17 then "工作时间"
           when hour(开始时间) between 17 and 20 and hour(开始时间)<>20 then "晚高峰"
           else  "休息时间"
	  end as period
 from a
 )b
 group by period
 order by get_car_num