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

select period,count(1) get_car_num,round(avg(timestampdiff(second,event_time,end_time))/60,1) avg_wait_time,round(avg(timestampdiff(second,order_time,start_time))/60,1) avg_dispatch_time
from(
select event_time,end_time,order_time,start_time,finish_time,(case when time(event_time) >= '07:00:00' and time(event_time) < '09:00:00'
then '早高峰' when time(event_time) >= '09:00:00' and time(event_time) < '17:00:00'
then '工作时间' when time(event_time) >= '15:00:00' and time(event_time) < '20:00:00' then '晚高峰' when time(event_time) >= '20:00:00' or time(event_time) < '07:00:00'
then '休息时间' 
end) as period
from tb_get_car_record tgcr
join tb_get_car_order tgco
on tgcr.order_id = tgco.order_id
where date_format(date(event_time),'%w') between 1 and 5) as t1
group by period
order by get_car_num