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

select t1.period
,count(*) as get_car_num
,round(avg(t1.wait_time),1)
,round(avg(t1.dispatch_time),1) 
from (
select (case when hour(r1.event_time)>=7 and hour(r1.event_time)<9 then '早高峰' 
      when hour(r1.event_time)>=9 and hour(r1.event_time)<17 then '工作时间' 
      when hour(r1.event_time)>=17 and hour(r1.event_time)<20 then '晚高峰' 
      when (hour(r1.event_time)>=20 and hour(r1.event_time)<24) or  
       (hour(r1.event_time)>=0 and hour(r1.event_time)<7 ) then '休息时间' end ) as period, 
       timestampdiff(second,o1.order_time,o1.start_time)/60 as dispatch_time,
       timestampdiff(second,r1.event_time,o1.order_time)/60 as wait_time 
       from tb_get_car_order as o1 left join tb_get_car_record as r1 
       on o1.order_id = r1.order_id 
#        date_format(o1.event_time,'%W') not in ('Saturday','Sunday')
where weekday(r1.event_time) between 0 and 4 
) t1  group by t1.period order by count(*)