明确题意:
- 统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间
- 以event_time-开始打车时间为时段划分依据
- 平均等待接单时间和平均调度时间均保留1位小数
- 平均调度时间仅计算完成了的订单
- 结果按叫车量升序排序
问题拆解:
- 指标:
- 叫车辆
- 平均等待接单时间=总的等待接单时间(打车时间-接单时间)/订单数目
- 平均调度时间=总的调度时间(上车时间-接单时间)/订单数目
- 筛选条件:周一到周五 -- WHERE DATE_FORMAT(event_time,'%W') NOT IN ( 'Saturday' ,'Sunday')
- 新生成的时段字段 -- case when
解题流程:
1.先提取出求解指标所需要的数据:连接tb_get_car_record和tb_get_car ,根据order_id,筛选出工作日的记录
select a.*,b.*
from tb_get_car_record a
inner join tb_get_car_order b
on a.order_id=b.order_id
where date_format(event_time,'%W') not in ('Saturday','Sunday')
2.在1.得到的表的基础上求出求解指标所需字段和新生成的时段字段
select a.order_id
,TIMESTAMPDIFF(SECOND,event_time,order_time)/60 wait_time
,TIMESTAMPDIFF(SECOND,order_time,start_time)/60 dispatch_time
,CASE WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '07-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '09-00-00' THEN '早高峰'
WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '09-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '17-00-00' THEN '工作时间'
WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '17-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '20-00-00' THEN '晚高峰'
ELSE '休息时间'
end period
from tb_get_car_record a
inner join tb_get_car_order b
on a.order_id=b.order_id
where date_format(event_time,'%W') not in ('Saturday','Sunday')
3.根据时段字段分组聚合,求出所有指标,并按照叫车量排序
select period,count(*) get_car_num, ROUND(AVG(wait_time),1) avg_wait_time,
ROUND(AVG(dispatch_time),1) avg_dispatch_time
from (
select a.order_id
,TIMESTAMPDIFF(SECOND,event_time,order_time)/60 wait_time
,TIMESTAMPDIFF(SECOND,order_time,start_time)/60 dispatch_time
,CASE WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '07-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '09-00-00' THEN '早高峰'
WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '09-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '17-00-00' THEN '工作时间'
WHEN DATE_FORMAT(event_time,'%H-%i-%s') >= '17-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s') < '20-00-00' THEN '晚高峰'
ELSE '休息时间'
end period
from tb_get_car_record a
inner join tb_get_car_order b
on a.order_id=b.order_id
where date_format(event_time,'%W') not in ('Saturday','Sunday')
) as t
group by period
ORDER BY get_car_num