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

-- 思路：

• -- 1.筛选出工作日的所有打车记录，对时间进行格式化处理取出对应时间
• -- 2.构建period时间范围字段，按照period分组统计平均等待时间和平均调度时间

核心函数：TIMESTAMPDIFF()与 DAYOFWEEK()

-- 思路：
--      1.筛选出工作日的所有打车记录，对时间进行格式化处理取出对应时间
--      2.构建period时间范围字段，按照period分组统计平均等待时间和平均调度时间
with
    t as (
        select
            time(date_format (event_time, '%H:%i:%s')) as event_time,
            o.order_id,
            time(date_format (order_time, '%H:%i:%s')) as order_time,
            time(date_format (start_time, '%H:%i:%s')) as start_time,
            fare
        from
            tb_get_car_order o
            join tb_get_car_record r on o.order_id = r.order_id
        where
            dayofweek (event_time) between 2 and 6
    )
select
    case
        when event_time >= '07:00:00'
        and event_time < '09:00:00' then '早高峰'
        when event_time >= '09:00:00'
        and event_time < '17:00:00' then '工作时间'
        when event_time >= '17:00:00'
        and event_time < '20:00:00' then '晚高峰'
        else '休息时间'
    end as period,
    count(order_id) as get_car_num,
    round(
        sum(TIMESTAMPDIFF (second, event_time, order_time)) / (count(order_id) * 60),
        1
    ) as avg_wait_time,
    round(
        sum(TIMESTAMPDIFF (second, order_time, start_time)) / (count(if (fare is not null, order_id, null)) * 60),
        1
    ) as avg_dispatch_time
from
    t
group by
    period
order by
    get_car_num