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

with workday as
(select *
from tb_get_car_record
where weekday(event_time) between 0 and 4
),
info as
(select 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) >='17: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)period,timestampdiff(second,event_time,order_time)/60 wait_time,timestampdiff(second,order_time,start_time)/60 dispatch_time
from (workday left join tb_get_car_order using(order_id))
)

select period,count(*)get_car_num,round(avg(wait_time),1)avg_wait_time,round(avg(dispatch_time),1)avg_dispatch_time
from info
#where finish_time is not null
group by period
order by get_car_num
;