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

select aperiod,count(order_id) get_car_num,round(sum(wait_time)/(count(order_id) *60),1) avg_wait_time,/*单位是分每单*/
round(sum(dispatch_time)/(count(start_time)*60),1) avg_dispatch_time 
from
(select tgo.order_id,event_time,order_time,start_time,case when hour(event_time) >= 7 and hour(event_time) < 9 then '早高峰'
																	  when hour(event_time) >=9 and hour(event_time) < 17 then '工作时间'
                                                                      when hour(event_time) >=17 and hour(event_time) < 20 then "晚高峰"
                                                                      else "休息时间" end  aperiod,
timestampdiff(second,event_time,order_time) wait_time, /*等待时间*/
if(start_time is not null,timestampdiff(second,order_time,start_time),0) dispatch_time /*调度时间.如取消订单,则不计算*/
from tb_get_car_order tgo
join tb_get_car_record tgc on tgc.order_id=tgo.order_id
where dayofweek(event_time) between 2 and 6) zb
group by aperiod
order by get_car_num