Hive 刷题——打车问题
需求描述
现有用户下单表(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 <string> (时段) | get_car_num <int> (叫车量) | 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 period, count(order_id) get_car_num, cast(sum(wait_time) / 60 / count(order_id) as decimal(16, 2)) wait_time, cast(sum(dispatch_time) / 60 / count(order_id) as decimal(16, 2)) dispatch_time from (select gr.order_id, case when hour(event_time) >= 7 and hour(event_time) < 9 then '早高峰' when hour(event_time) >= 9 and hour(event_time) < 17 then '工作时间' when hour(event_time) >= 17 and hour(event_time) < 20 then '晚高峰' else '休息时间' end period, unix_timestamp(end_time) - unix_timestamp(event_time) wait_time, unix_timestamp(start_time) - unix_timestamp(order_time) dispatch_time from get_car_record gr join get_car_order go on gr.order_id = go.order_id) t group by period order by 2 desc;\