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

with
    base as (
        select
            case
                when hour(time(event_time)) >= 7
                and hour(time(event_time)) < 9 then '早高峰'
                when hour(time(event_time)) >= 9
                and hour(time(event_time)) < 17 then '工作时间'
                when hour(time(event_time)) >= 17
                and hour(time(event_time)) < 20 then '晚高峰'
                when (
                    hour(time(event_time)) >= 20
                    and hour(time(event_time)) < 24
                    or hour(time(event_time)) >= 0
                    and hour(time(event_time)) < 7
                ) then '休息时间'
            end as period,
            start_time,
            timestampdiff(second, event_time, order_time) as wait_time,
            timestampdiff(second, order_time, start_time) as dispacth_time
        from
            tb_get_car_record
            join tb_get_car_order using (order_id)
        where
            (WEEKDAY(event_time) + 1 between 1 and 5)
    )
select
    *
from
    (
        select
            period,
            count(period) as get_car_num
        from
            base
        group by
            period
    ) as t1
    join (
        select
            period,
            round(avg(wait_time) / 60, 1) as avg_wait_time,
            round(avg(dispacth_time) / 60, 1) as avg_dispacth_time
        from
            base
        where
            start_time is not null
        group by
            period
    ) as t2 using (period)
order by
    get_car_num