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

#这个题总体来说还是容易，但这里的叫车量指的是某顾客的一条叫车记录在打车订单表tb_get_car_order有订单生成（有司机接了订单） 才叫一个打车，如果没有司机接单的就不叫打车，不统计没有司机接单的数据。

我提交的时候就是这个问题，题目中好像没描述对打车量的计算方式。

select
    c.period,sum(case when c.order_id is not null then 1 else 0 end) as get_car_num, 
    format(sum( case when c.order_time is not null then (unix_timestamp(c.order_time)-unix_timestamp(c.event_time))/60 else 0 end)/sum(case when c.order_id is not null then 1 else 0 end),1) as avg_wait_time,
    format(sum( case when c.start_time is not null and c.finish_time is not null then  (unix_timestamp(c.start_time) - unix_timestamp(c.order_time))/60 else 0 end)/sum(case when c.start_time is not null and c.finish_time is not null then 1 else 0 end),1) as avg_dispatch_time
from 
(
    select
        a.event_time,
        b.order_id,
        case when hour(a.event_time) >= 7 and hour(a.event_time) < 9 then '早高峰'
            when hour(a.event_time) >= 9 and hour(a.event_time) < 17 then '工作时间'
            when hour(a.event_time) >= 17 and hour(a.event_time) < 20 then '晚高峰'
            else '休息时间' end as period,
        b.order_time,b.start_time,b.finish_time
    from tb_get_car_record a left join tb_get_car_order b on a.order_id = b.order_id and WEEKDAY(a.event_time) <= 4
) c 
group by c.period
order by get_car_num