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

select  (case when date_format(event_time,"%H")>=7 and date_format(event_time,"%H")<9 then '早高峰'
             when date_format(event_time,"%H")>=9 and date_format(event_time,"%H")<17 then '工作时间'
             when date_format(event_time,"%H")>=17 and date_format(event_time,"%H")<20 then '晚高峰'
             when date_format(event_time,"%H")>=20 or date_format(event_time,"%H")<7 then '休息时间' end)as period,
        count(event_time) as get_car_num,
        round(sum(timestampdiff(second,event_time,end_time))/60/count(event_time),1) as avg_wait_time,
        round(avg(timestampdiff(second,order_time,start_time))/60,1) as avg_dispatch_time
from tb_get_car_record a
left join tb_get_car_order b
on a.order_id=b.order_id
where weekday(event_time) between 0 and 4
group by period
order by get_car_num

计算avg_dispatch_time时round(avg(timestampdiff(second,order_time,start_time))/60,1)就可以，round(sum(timestampdiff(second,order_time,start_time))/60/count(event_time,1)，无法通过，不知道数据结构上什么样的问题会导致这样的结果出现

#本地建表试了以下发现是一样的...