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

select period,
count(event_time) as get_car_num,
round(avg(wait_time)/60,1) as avg_wait_time,
round(avg(dispatch_time)/60,1) as avg_dispatch_time
from(
select (case when hour(t2.event_time) between 7 and 8 then '早高峰' when hour(t2.event_time) between 9 and 16 then '工作时间'
when hour(t2.event_time) between 17 and 19 then '晚高峰' else '休息时间' end) as period,
event_time,
end_time,
start_time,
round(timestampdiff(second,event_time,end_time),1) as wait_time,
round(timestampdiff(second,end_time,start_time),1) as dispatch_time
from tb_get_car_order as t1
left join tb_get_car_record as t2
using(order_id)
WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6) as t1
group by period
order by get_car_num