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

with a2 as(
with a1 as (
select event_time dt,t1.order_id,order_time dt1,start_time dt2
,date_format(event_time,"%w") week_num
,case when date_format(event_time,'%T') >= '07:00:00' and date_format(event_time,'%T') < '09:00:00' then '早高峰'
when date_format(event_time,'%T') >= '09:00:00' and date_format(event_time,'%T') < '17:00:00' then '工作时间'
WHEN  date_format(event_time,'%T') >= '17:00:00' and date_format(event_time,'%T') <'20:00:00' then '晚高峰'
else '休息时间' end ac
from tb_get_car_record t1
left join tb_get_car_order t2
on t1.order_id = t2.order_id
where date_format(event_time,"%w") between 1 and 5)
select ac
,order_id
,timestampdiff(second,dt,dt1)/60 tm1
,if(dt2 is null,null, timestampdiff(second,dt1,dt2)/60) tm2
from a1
)
select ac
,count(order_id) get_car_num
,round(avg(tm1),1) avg_wait_time
,round(avg(tm2),1) avg_dispatch_time
from a2
group by ac
order by get_car_num;