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

select 
    period,
    count(a.order_id) as get_car_num,
    round(avg(timestampdiff(second,event_time,order_time)/60), 1) as avg_wait_time,
    round(avg(timestampdiff(second,order_time,start_time)/60), 1) as avg_dispatch_time
from 
    (
        select 
            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,
            event_time,
            order_id
        from 
            tb_get_car_record
        where 
            weekday(event_time) between 0 and 4
    ) a
left join 
    tb_get_car_order b 
using(order_id) 
group by    
    period
order by 
    get_car_num