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

知识点，主要是时间函数的一些使用

1. mysql求日期差值 timestampdiff(INTERVAL, START_TIME, END_TIME)，注意这里计算分钟并精确到小数点后一位时，基础数据计算单位应用second，否则存在精度问题；
2. 求日期的星期 dayofweek()，在mysql中"周日-周一-...-周六"对应"1-2-...-7"
3. 取完整日期的时间部分 time()

select period, 
count(1) get_car_num,
round(avg(wait_time) / 60, 1) avg_wait_time,
round(avg(dispatch_time) / 60, 1) avg_dispatch_time
from 
(
select 
case when time(t1.event_time) >= '07:00:00' and  time(t1.event_time) < '09:00:00' then '早高峰'
     when time(t1.event_time) >= '09:00:00' and  time(t1.event_time) < '17:00:00' then '工作时间'
     when time(t1.event_time) >= '17:00:00' and  time(t1.event_time) < '20:00:00' then '晚高峰'
     else '休息时间' end period,
timestampdiff(second, t1.event_time, t2.order_time) wait_time,
timestampdiff(second, t2.order_time, t2.start_time) dispatch_time
from tb_get_car_record t1 
left join tb_get_car_order t2
on t1.order_id = t2.order_id 
where dayofweek(t1.event_time) >= 2 and dayofweek(t1.event_time) <= 6
    ) t3 
group by 1 
order by 2 asc