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

我一开始忽略了周一-周五这个关键的时间限制

DATE_FORMAT(event_time,'%w') BETWEEN 1 and 5

后来加上了，但是发现数据还是对不上

发现可能是在计算时间差的时候使用的是minute，精度略小，于是修改成second之后结果就对上了

TIMESTAMPDIFF(second,b.event_time,a.order_time)

最终完整代码如下：

select period,
       count(order_id) num,
       round(avg(等待接单时间)/60,1),
       round(avg(调度时间)/60,1)
from(select
	case when time(b.event_time)>='07:00:00' and time(b.event_time)<'09:00:00' then '早高峰'
    when time(b.event_time)>='09:00:00' and time(b.event_time)<'17:00:00' then '工作时间'
    when time(b.event_time)>='17:00:00' and time(b.event_time)<'20:00:00' then '晚高峰'
    when (time(b.event_time)>='20:00:00' and time(b.event_time)<'24:00:00')
    or(time(b.event_time)>='00:00:00' and time(b.event_time)<'07:00:00') then '休息时间'end period,
    a.order_id,b.event_time,
	TIMESTAMPDIFF(second,b.event_time,a.order_time) 等待接单时间,
	TIMESTAMPDIFF(second,a.order_time,a.start_time) 调度时间
from tb_get_car_order a left join tb_get_car_record b on a.order_id=b.order_id
where DATE_FORMAT(event_time,'%w') BETWEEN 1 and 5 )a
group by period
order by num