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

with
    t as (
        select
            order_id,
            hour (event_time) h 
            ,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 period,
            timestampdiff (second, event_time, order_time) wait,
            timestampdiff (second, order_time, start_time) pick
        from
            tb_get_car_order
            join tb_get_car_record using (order_id)
        where dayofweek(event_time) between 2 and 6
    )

    select  period
            ,count(*) get_car_num
            ,round(avg(wait/60),1) avg_wait_time
            ,round(avg(pick/60),1) avg_dispatch_time
    from t
    group by period
    order by get_car_num