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

select period,count(*) get_car_num,
round(sum(timestampdiff(second,event_time,end_time)/60)/count(*),1)avg_wait_time,
round(sum(timestampdiff(second,order_time,ifnull(start_time,order_time))/60)/count(start_time),1)avg_dispatch_time
from
(
select distinct cr.order_id,cr.event_time,cr.end_time,co.order_time,co.start_time,
case 
when date_format(cr.event_time,"%H%i%S")>='070000' and date_format(cr.event_time,"%H%i%S")<'090000' then '早高峰'
when date_format(cr.event_time,"%H%i%S")>='090000' and date_format(cr.event_time,"%H%i%S")<'170000' then '工作时间'
when date_format(cr.event_time,"%H%i%S")>='170000' and date_format(cr.event_time,"%H%i%S")<'200000' then '晚高峰'
else '休息时间'
end period
from  tb_get_car_record cr join tb_get_car_order co using(order_id)
where DAYOFWEEK(cr.event_time) BETWEEN 2 AND 6
)t1
group by period
order by get_car_num,avg_dispatch_time