这题重点就在于时间相关的函数timestampdiff(), time(), weekday() 的应用以及对于case when 的应用
- 合并tb_get_car_record 以及 tb_get_car_order 利用weekday() 找到工作日产生的订单
select *
from tb_get_car_record
inner join tb_get_car_order
using(order_id)
where weekday(event_time) between 0 and 4
- 按照要求将划分period,统计等待接单时间 (end_time - event_time), 调度时间 (start_time - order_time)
select
case
when time(event_time) between '07:00:00' AND '08:59:59' THEN '早高峰'
when time(event_time) between '09:00:00' AND '16:59:59' Then '工作时间'
when time(event_time) between '17:00:00' AND '19:59:59' then '晚高峰'
ELSE '休息时间'
END period,
order_id,
timestampdiff(second,event_time,end_time)/60 as wait_time,
timestampdiff(second,order_time,start_time)/60 as dispatch_time
from tb_get_car_record
inner join tb_get_car_order
using(order_id)
where weekday(event_time) between 0 and 4
- 再按照period统计后以get_car_num做升序
select period,
count(order_id) as get_car_num,
round(avg(wait_time),1) as avg_wait_time,
round(avg(dispatch_time),1) as avg_dispatch_time
from (
select
case
when time(event_time) between '07:00:00' AND '08:59:59' THEN '早高峰'
when time(event_time) between '09:00:00' AND '16:59:59' Then '工作时间'
when time(event_time) between '17:00:00' AND '19:59:59' then '晚高峰'
ELSE '休息时间'
END period,
order_id,
timestampdiff(second,event_time,end_time)/60 as wait_time,
timestampdiff(second,order_time,start_time)/60 as dispatch_time
from tb_get_car_record
inner join tb_get_car_order
using(order_id)
where weekday(event_time) between 0 and 4
) temp
GROUP BY period
order by get_car_num