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

-- 统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留1位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序
with t1 as (select a.uid,
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 '晚高峰' else '休息时间' end period,start_time,order_time,finish_time,
timestampdiff(second,event_time,order_time) wait_time,
timestampdiff(second,order_time,start_time) dispatch_time
from tb_get_car_record a 
left join tb_get_car_order b
on a.order_id = b.order_id and a.uid = b.uid
where weekday(a.event_time) between 0 and 4
)
select period,count(uid) get_car_num,round(avg(wait_time)/60,1) avg_wait_time,round(avg(dispatch_time)/60,1) avg_dispatch_time from t1 

group by period
order by get_car_num ASC