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

with t1 as 
(
select order_id,case 
when hour(event_time)>=7 and hour(event_time) <9 then '早高峰'
when hour(event_time)>=9 and hour(event_time)<17 then '工作时间'
when hour(event_time)>=17 and hour(event_time)<20 then '晚高峰'
else '休息时间' #跨天的放在else 
end as period,event_time,end_time
from tb_get_car_record
where weekday(event_time) between 0 and 4
)
select period,count(*) as get_car_num,
round(avg(timestampdiff(second,event_time,end_time)/60),1),
round(avg(timestampdiff(second,order_time,start_time)/60),1)
from t1
inner join tb_get_car_order using(order_id)
group by period
order by get_car_num