大数据SQL题47 打车问题

原题链接：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:002021-09-20 11:00:309017108北京2021-09-20 21:00:002021-09-20 21:00:409008108北京2021-09-20 18:59:302021-09-20 19:01:009018102北京2021-09-21 08:59:002021-09-21 09:01:009002
司机订单信息表（get_car_order）如下。
order_id(课程id)uid(用户id)driver_id(用户id)order_time(接单时间)start_time(开始时间)finish_time(结束时间)fare(费用)grade(评分)90171072132021-09-20 11:00:302021-09-20 11:02:102021-09-20 11:31:0038590081082042021-09-20 21:00:402021-09-20 21:03:002021-09-20 21:31:0038490181082142021-09-20 19:01:002021-09-20 19:04:502021-09-20 19:21:00385
统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以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)>(调度时长)工作时间10.501.67休息时间10.672.33晚高峰32.067.28早高峰42.218.00

解题思路

SELECT  t1.period,COUNT(t1.order_id)AS get_car_num,
        cast(AVG(order_time - event_time)/60ASdecimal(16,2))AS wait_time,
        cast(AVG(start_time - order_time)/60ASdecimal(16,2))AS dispatch_time
FROM(SELECT  order_id,
            unix_timestamp(event_time)AS event_time,CASEWHENhour(event_time)BETWEEN7AND8THEN'早高峰'WHENhour(event_time)BETWEEN9AND16THEN'工作时间'WHENhour(event_time)BETWEEN17AND19THEN'晚高峰'WHENhour(event_time)BETWEEN20AND23THEN'休息时间'WHENhour(event_time)BETWEEN0AND6THEN'休息时间'ENDAS period
    FROM get_car_record
    WHERE dayofweek(event_time)BETWEEN2AND6) t1
LEFTJOIN(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
GROUPBY  t1.period