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

with t1 as(
    select tbd.order_id,case 
        when hour(event_time) in (7,8) then "早高峰"
        when hour(event_time) between 9 and 16 then "工作时间"
        when hour(event_time) in (17,18,19) then "晚高峰"
        else "休息时间" end as period,
timestampdiff(second,event_time,order_time) as wait_time,
timestampdiff(second,order_time,start_time) as dispatch_time
from tb_get_car_record tbd 
left join tb_get_car_order tbo
on tbd.order_id =tbo.order_id
where dayofweek(event_time) between 2 and 6
)

select period,count(order_id) as get_car_num,
round(avg(wait_time/60),1) as avg_wait_time,
round(avg(dispatch_time/60),1) as avg_dispatch_time 
from t1
group by period 
order by get_car_num

1. 先查询 分组、等待时间、调度时间
1. case函数实现分组
2. timestdiff 这边要确认好时间 order和endtime其实是一样的
3. 过滤条件 dayofweek函数 只选择周一到周五
2. 查询总数量 平均数
1. 使用count函数查询总数量
2. avg函数 同时转换时间
3. 按period分组

细节：

in（7，8） 代表就是七点多八点多 9点就忽略了