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

SELECT
period,
count(distinct order_id) as "get_car_num",
round(sum(wait_time)/60/count(distinct order_id),1) as "avg_wait_time",
round(sum(dispatch_time)/60/count(distinct case when 乘客上车时间 is not null then order_id end),1) as "avg_dispatch_time"
from (
        select 
        df2.order_id,
        df1.event_time as "叫车时间",
        df1.end_time as "等待结束时间",
        df2.start_time as "乘客上车时间",
        df2.finish_time as "订单完成时间",
        weekday(df1.event_time) as "周几",
        TIMESTAMPDIFF(second,df1.event_time,df1.end_time) as "wait_time",
        TIMESTAMPDIFF(second,case when df2.start_time is not null then df1.end_time end,case when df2.start_time is not    null then df2.start_time end) as "dispatch_time",
        case when date_format(df1.event_time,'%H:%i:%s') >= '07:00:00' and date_format(df1.event_time,'%H:%i:%s') < '09:00:00' then '早高峰'
        when date_format(df1.event_time,'%H:%i:%s') >= '09:00:00' and date_format(df1.event_time,'%H:%i:%s') < '17:00:00' then '工作时间'
        when date_format(df1.event_time,'%H:%i:%s') >= '17:00:00' and date_format(df1.event_time,'%H:%i:%s') < '20:00:00' then '晚高峰'
        when date_format(df1.event_time,'%H:%i:%s') >= '20:00:00' or date_format(df1.event_time,'%H:%i:%s') < '07:00:00' then '休息时间'
        end as "period"
        from tb_get_car_record as df1
        left join tb_get_car_order as df2
        on df1.order_id = df2.order_id
        where date_format(df1.event_time,'%W') not in('Saturday','Sunday')
) as t
group by period
order by get_car_num ASC,period desc

我感觉应该比较严谨了