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

select 
    period,
    count(get_car) as get_car_num,
    round(avg(wait_time/60),1) as avg_wait_time,
    round(avg(dispatch_time/60),1) as avg_dispatch_time
from
(
select 
    o.order_id as get_car,
    case 
    when hour(event_time) >=7 and hour(event_time) < 9 then '早高峰'
    when hour(event_time) >=9 and hour(event_time) < 17 then '工作时间'
    when hour(event_time) >=17 and hour(event_time) < 20 then '晚高峰'
    else '休息时间' end as period,
    timestampdiff(second,event_time,order_time) as wait_time,
    timestampdiff(second,order_time,start_time) as dispatch_time
from 
    tb_get_car_record r  left join tb_get_car_order o using(order_id)
where 
    weekday(event_time) between 0 and 4
) t1
group by 
    period
order by 
    count(get_car)

timestampdiff(minute,event_time,order_time)会对结果直接取整，如果是1.4分钟，也四舍五入为1分钟了，所以先计算秒