【场景】：周一到周五各时间段数据

【分类】：时间函数

分析思路

难点：

1.统计周一到周五: weekday(event_time) between 0 and 4 或者 date_format(event_time,‘%w') between 1 and 5

注意：

2.平均等待接单时间和平均调度时间要换算成秒之后再换算回分钟

（1）统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。结果按叫车量升序排序

叫车量：打车记录（包含未接单）；

等待接单时间：开始打车时间order_time 到司机接单时间event_time；

调度时间：司机接单时间event_time到上车时间start_time（完成订单）；

平均等待接单时间和平均调度时间以分钟为单位，所以先计算秒，再除以60换算成分钟

  • [条件] 统计周一到周五: weekday(event_time) between 0 and 4 或者 date_format(event_time,‘%w') between 1 and 5

  • [使用] 各时间段：case when hour(event_time) 或者 date_format(event_time,'%H')

正确代码

方法一

使用 hour(event_time) + weekday(event_time) between 0 and 4

#统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。结果按叫车量升序排序
select
    (case
        when 7 <= hour(event_time) and hour(event_time) < 9 then '早高峰'
        when 9 <= hour(event_time) and hour(event_time) < 17 then '工作时间'
        when 17 <= hour(event_time) and hour(event_time) < 20 then '晚高峰'
        else '休息时间'
    end) period,
    count(event_time) as get_car_num,
    round(avg(timestampdiff(second,event_time,order_time))/60,1) as avg_wait_time,
    round(avg(timestampdiff(second,order_time,start_time))/60,1) as avg_dispatch_time
from tb_get_car_record a,tb_get_car_order b
where a.order_id = b.order_id
and weekday(event_time) between 0 and 4
group by period
order by get_car_num

方法二

使用 substring_index(event_time) + WeekDay(event_time) not in (5,6)

#统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。结果按叫车量升序排序
select
    (case
        when substring_index(event_time,' ',-1) between '07:00:00' and '08:59:59' then '早高峰'
        when substring_index(event_time,' ',-1) between '09:00:00' and '16:59:59' then '工作时间'
        when substring_index(event_time,' ',-1) between '17:00:00' and '19:59:59' then '晚高峰'
        else '休息时间'
    end) period,
    count(event_time) as get_car_num,
    round(avg(timestampdiff(second,event_time,order_time))/60,1) as avg_wait_time,
    round(avg(timestampdiff(second,order_time,start_time))/60,1) as avg_dispatch_time
from tb_get_car_record a,tb_get_car_order b
where a.order_id = b.order_id
and WeekDay(event_time) not in (5,6)
group by period
order by get_car_num

方法三

使用 date_format(event_time,'%H') + date_format(event_time,‘%w') between 1 and 5

#统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。结果按叫车量升序排序
select
    (case
        when date_format(event_time,'%H') >= 7 and date_format(event_time,'%H') < 9 then '早高峰'
        when date_format(event_time,'%H') >= 9 and date_format(event_time,'%H') < 17 then '工作时间'
        when date_format(event_time,'%H') >= 17 and date_format(event_time,'%H') < 20 then '晚高峰'
        else '休息时间' 
    end) as period,
    count(event_time) as get_car_num,
    round(avg(timestampdiff(second,event_time,order_time))/60,1) as avg_wait_time,
    round(avg(timestampdiff(second,order_time,start_time))/60,1) as avg_dispatch_time
from tb_get_car_record a,tb_get_car_order b
where a.order_id = b.order_id
and date_format(event_time,‘%w') between 1 and 5
group by period
order by get_car_num