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

明确题意：

统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间

问题分解：

关联用户打车记录表和打车订单表：join tb_get_car_order tgco on tgcr.order_id = tgco.order_id

筛选周一到周五的订单记录：where dayofweek(event_time) between 2 and 6

计算每个订单所属时段：case when hour(event_time) between 7 and 8 then '早高峰' when hour(event_time) between 9 and 16 then '工作时间' when hour(event_time) between 17 and 19 then '晚高峰' else '休息时间' end

计算每个订单的等待接单时间：timestampdiff(second, event_time, order_time)

计算每个订单的调度时间时间：if(mileage is not null, timestampdiff(second, order_time, start_time), null)

按时段分组：group by period

计算各时段的叫车量：count(1)

计算各时段的平均等待时间：avg(wait_time)

计算各时段的平均调度时间：avg(dispatch_time)

细节问题：

平均等待接单时间和平均调度时间均保留1位小数：round(x, 1)

结果按叫车量升序排序：order by get_car_num

select
    period,
    count(1) get_car_num,
    round(avg(wait_time) / 60, 1) avg_wait_time,
    round(avg(dispatch_time) / 60, 1) avg_dispatch_time
from(
select
    case 
    when hour(event_time) between 7 and 8 then '早高峰'
    when hour(event_time) between 9 and 16 then '工作时间'
    when hour(event_time) between 17 and 19 then '晚高峰'
    else '休息时间' 
    end period,
    timestampdiff(second, event_time, order_time) wait_time,
    if(mileage is not null, timestampdiff(second, order_time, start_time), null) dispatch_time	
from tb_get_car_record tgcr
join tb_get_car_order tgco on tgcr.order_id = tgco.order_id
where dayofweek(event_time) between 2 and 6
  -- 星期一 = 2
) t1
group by period
order by get_car_num;