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

### 没想到一次就成功了的。纪录每一次打车纪录的 时间属性分类，打车等待时间，打车调度时间。然后根据打车纪录时间属性分类计算  平均等待接单时间和平均调度时间。


   select period,
          count(event_time) get_car_num,
          round(sum(waitTimeS)/(60*count(if(waitTimeS>0,waitTimeS,null))),1) avg_wait_time,
          round(sum(dispatchTimeS)/(60*count(if(dispatchTimeS>0,dispatchTimeS,null))),1) avg_dispatch_time 
     from (
   select (case when date_format(tgcr.event_time,'%H:%i:%s')>='07:00:00' 
                 and date_format(tgcr.event_time,'%H:%i:%s')<'09:00:00' then 
                 '早高峰' 
                when date_format(tgcr.event_time,'%H:%i:%s')>='09:00:00' 
                 and date_format(tgcr.event_time,'%H:%i:%s')<'17:00:00' then 
                 '工作时间' 
                when date_format(tgcr.event_time,'%H:%i:%s')>='17:00:00' 
                 and date_format(tgcr.event_time,'%H:%i:%s')<'20:00:00' then 
                 '晚高峰' 
                when (date_format(tgcr.event_time,'%H:%i:%s')>='20:00:00' and date_format(tgcr.event_time,'%H:%i:%s')<='23:59:59')
                  or (date_format(tgcr.event_time,'%H:%i:%s')>='00:00:00' and date_format(tgcr.event_time,'%H:%i:%s')<'07:00:00') then 
                 '休息时间' end) period,
            tgcr.event_time,
            if(tgcr.end_time=tgco.order_time,timestampdiff(second,tgcr.event_time,tgcr.end_time),0) waitTimeS,
            if(tgco.start_time is not null,timestampdiff(second,tgco.order_time,tgco.start_time),0) dispatchTimeS 
     from tb_get_car_order tgco join tb_get_car_record tgcr using(order_id,uid) 
    where weekday(tgcr.end_time) not in (5,6) 
    ) A 
    group by period order by get_car_num ;