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

# dayofweek()返回星期几 hour 返回时间
with a as(
select
    case when hour(r.event_time) in (7,8) then '早高峰'
        when hour(r.event_time) between 9 and 16 then '工作时间'
        when hour(r.event_time) between 17 and 19 then '晚高峰'
        else '休息时间' end as period,
    r.order_id,
    round(timestampdiff(second,r.event_time,r.end_time)/60,1) as wait_time,
    round(timestampdiff(second,o.order_time,o.start_time)/60,1) as dispatch_time
from 
    tb_get_car_record as r
    left join tb_get_car_order as o on r.order_id = o.order_id
where
    dayofweek(r.event_time) between 2 and 6
)
select
    period,
    count(order_id) as get_car_num,
    round(avg(wait_time),1) as avg_wait_time,
    round(avg(dispatch_time),1) as avg_dispath_time
from 
    a 
group by 
    period
order by 
    get_car_num

开始因为dayofweek函数没设置好报错，周一到周五应该是between2 and 6 之后因为case when 函数没写对报错，看了大佬题解后，发现应该修改，改好了就泡桐了