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

#思路：
#①有订单就计入叫车量，所以要用叫车表左联接单表，保证叫车数据不丢失；
#②调度时间是用上车时间-接单时间，上车时间可能是null，但不影响计算平均值，因为avg()会忽略null值；
#③叫车表在左联之前，要将每日时间做分类。

select a, 
count(event_time) b, 
round(avg(timestampdiff(second,event_time,end_time))/60,1),
round(avg(timestampdiff(second,order_time,start_time))/60,1)
from
(select event_time, end_time, order_id,
case when hour(event_time) between 7 and 8 then '早高峰'
when hour(event_time) between 9 and 16 then '工作时间'
when hour(event_time) between 17 and 19 then '晚高峰'
else '休息时间' end as a
from tb_get_car_record) as tb1
left join 
tb_get_car_order
using(order_id)
where dayofweek(event_time) between 2 and 6
group by a
order by b