一、题目拆解

1、条件：

周一到周五：weekday(event_time) between 0 and 4

各时段：(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) as period

2、计算指标：

叫车量：count(period) as get_car_num

平均等待接单时间：先计算timestampdiff(second,event_time,order_time)/60 as wait_time，再计算round(avg(wait_time),1) as avg_wait_time

平均调度时间：先计算timestampdiff(second,order_time,start_time)/60 as dispatch_time，再计算round(avg(dispatch_time),1) as avg_dispatch_time

3、要求

结果按叫车量升序排序：order by get_car_num。

二、题解

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
;

三、后记

题目中有一个要求：平均调度时间仅计算完成了的订单，这个我写的题解中并没有考虑这一点，但提交通过了，应该是测试用例没有覆盖到。但话说回来，这个要求要怎么实现，一下子还真没啥思路，欢迎有思路的大佬们来交流~