阅读量:0
原题链接：http://practice.atguigu.cn/#/question/47/desc?qType=SQL
题目需求
现有用户下单表（get_car_record）如下。
| uid(用户id) | city(城市) | event_time（下单时间） | end_time(结束时间:取消或者接单) | order_id(订单id) |
|---|---|---|---|---|
| 107 | 北京 | 2021-09-20 11:00:00 | 2021-09-20 11:00:30 | 9017 |
| 108 | 北京 | 2021-09-20 21:00:00 | 2021-09-20 21:00:40 | 9008 |
| 108 | 北京 | 2021-09-20 18:59:30 | 2021-09-20 19:01:00 | 9018 |
| 102 | 北京 | 2021-09-21 08:59:00 | 2021-09-21 09:01:00 | 9002 |
司机订单信息表（get_car_order）如下。
| order_id(课程id) | uid(用户id) | driver_id(用户id) | order_time(接单时间) | start_time(开始时间) | finish_time(结束时间) | fare(费用) | grade(评分) |
|---|---|---|---|---|---|---|---|
| 9017 | 107 | 213 | 2021-09-20 11:00:30 | 2021-09-20 11:02:10 | 2021-09-20 11:31:00 | 38 | 5 |
| 9008 | 108 | 204 | 2021-09-20 21:00:40 | 2021-09-20 21:03:00 | 2021-09-20 21:31:00 | 38 | 4 |
| 9018 | 108 | 214 | 2021-09-20 19:01:00 | 2021-09-20 19:04:50 | 2021-09-20 19:21:00 | 38 | 5 |
统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留2位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。
注：不同时段定义：早高峰 [07:00:00 , 09:00:00)、工作时间 [09:00:00 , 17:00:00）、晚高峰 [17:00:00 ,20:00:00）、休息时间 [20:00:00 , 07:00:00） 时间区间左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）
从开始打车到司机接单为等待接单时间，从司机接单到上车为调度时间
期望结果如下：
| period(时段) | get_car_num(叫车量) | wait_time<decimal(16,2)>(等待时长) | dispatch_time<decimal(16,2)>(调度时长) |
|---|---|---|---|
| 工作时间 | 1 | 0.50 | 1.67 |
| 休息时间 | 1 | 0.67 | 2.33 |
| 晚高峰 | 3 | 2.06 | 7.28 |
| 早高峰 | 4 | 2.21 | 8.00 |
解题思路
SELECT t1.period, COUNT(t1.order_id) AS get_car_num, cast(AVG(order_time - event_time)/60 AS decimal(16,2)) AS wait_time, cast(AVG(start_time - order_time)/60 AS decimal(16,2)) AS dispatch_time FROM ( SELECT order_id, unix_timestamp(event_time) AS event_time, 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 '晚高峰' WHEN hour(event_time) BETWEEN 20 AND 23 THEN '休息时间' WHEN hour(event_time) BETWEEN 0 AND 6 THEN '休息时间' END AS period FROM get_car_record WHERE dayofweek(event_time) BETWEEN 2 AND 6 ) t1 LEFT JOIN ( SELECT order_id, unix_timestamp(order_time) AS order_time, unix_timestamp(start_time) AS start_time FROM get_car_order ) t2 ON t1.order_id = t2.order_id GROUP BY t1.period 