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

# 3.13  11:00- 11:40

# 字段：period、get_car_num、avg_wait_time、avg_dispatch_time
# 时间：周一到周五  

# 平均等待时间
# tb11：链接
with tb1 as(
    select order_id,
    case 
        when hour(event_time)>=7 and hour(event_time)<9 then'早高峰'
        when hour(event_time)>=9 and hour(event_time)<17 then'工作时间'
        when hour(event_time)>=17 and hour(event_time)<20 then'晚高峰'
        else '休息时间' end 
        as period,
    event_time,
    timestampdiff(second,event_time,end_time) as wait_time,
    timestampdiff(second,order_time,start_time) as dispatch_time
    from tb_get_car_order left join tb_get_car_record using(order_id)
    where weekday(event_time) between 0 and 4  # 星期数公式？
    # AND fare is not null
)

# tb2：不同时间段，count car，
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 tb1
group by period
order by count(order_id) asc

# 星期数公式
# weekday：0到6代表星期一到星期日
# dayofweek：从1到7代表星期日到星期六


# 注意点：需要排除未完成的订单
# 但是这题不排除fare为空的反而正确的？