hivesql练习_打车问题

现有用户下单表(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

 

解答:

 1 with tt as(
 2   SELECT
 3   gcr.order_id,
 4   CASE
 5   WHEN hour(event_time) >= 7 and hour(event_time) < 9 then '早高峰'
 6   WHEN hour(event_time) >= 9 and hour(event_time) < 17 then '工作时间'
 7   WHEN hour(event_time) >= 17 and hour(event_time) < 20 then '晚高峰'
 8   WHEN hour(event_time) >= 20 or hour(event_time) < 7 then '休息时间'
 9   END period,
10   unix_timestamp(order_time) - unix_timestamp(event_time) wait_time,
11   unix_timestamp(start_time) - unix_timestamp(order_time) dispatch_time
12   FROM
13   get_car_record gcr
14   join get_car_order gco
15   on gcr.order_id = gco.order_id
16 )
17 SELECT 
18 period,
19 count(order_id) get_car_num,
20 -- count(period), --周一到周五各时段的个数
21 cast(sum(wait_time) / count(period) / 60 as decimal(16, 2)) wait_time,
22 cast(sum(dispatch_time) / count(period) / 60 as decimal(16, 2)) dispatch_time
23 FROM
24 tt
25 group by period 
26 order by get_car_num

 

posted @ 2023-03-28 22:58  娜娜娜娜小姐姐  阅读(41)  评论(0编辑  收藏  举报