打车业务分析
“订单信息表”里记录了巴西乘客使用打车软件的信息,包括订单呼叫、应答、取消、完单时间。(滴滴2020年笔试题)
字段释义:
订单id;呼叫订单识别号
乘客id:乘客识别号
呼叫时间:乘客从应用发起需要用车请求的时间(北京时间)
应答时间:司机点击接单的时间(北京时间)
取消时间:司机或乘客取消订单的时间(北京时间)
完单时间:司机点击到达目的地的时间(北京时间)
注意:
(1)表中的时间是北京时间,巴西比中国慢11小时。
(2)应答时间列的数据值如果是“1970”年(巴西时间),表示该订单没有司机应答,属于无效订单。
#1. 订单的应答率,完单率分别是多少? select count(distinct case when year(date_sub(t1.应答时间,interval 11 hour))<>1970 then t1.订单id end)/count(t1.订单id) as 应答率, count(distinct case when year(date_sub(t1.完单时间,interval 11 hour))<>1970 then t1.订单id end)/count(t1.订单id) as 完单率 from 订单信息表 t1;
结果如下:
#2. 呼叫应答平均时间有多长? select avg(timestampdiff(minute,t1.呼叫时间,t1.应答时间)) as "呼叫应答平均时间(min)" from 订单信息表 t1 where year(date_sub(t1.应答时间,interval 11 hour))<>1970;
结果如下:
#3. 从这一周的数据来看,呼叫量最高的是哪一个小时(当地时间)?呼叫量最少的是哪一个小时(当地时间)?
#(3.1)呼叫量最高:
SELECt
HOUR(
DATE_SUB(呼叫时间, INTERVAL 11 HOUR)
) AS 当地小时,
COUNT(*) AS 呼叫量
FROM
订单信息表
GROUP BY HOUR(
DATE_SUB(呼叫时间, INTERVAL 11 HOUR)
)
HAVINg COUNT(*) >= ALL
(SELECt
COUNT(*) AS 呼叫量
FROM
订单信息表
GROUP BY HOUR(
DATE_SUB(呼叫时间, INTERVAL 11 HOUR)
))
#(3.2)呼叫量最低:
SELECt
HOUR(
DATE_SUB(呼叫时间, INTERVAL 11 HOUR)
) AS 当地小时,
COUNT(*) AS 呼叫量
FROM
订单信息表
GROUP BY HOUR(
DATE_SUB(呼叫时间, INTERVAL 11 HOUR)
)
HAVINg COUNT(*) <= ALL
(SELECt
COUNT(*) AS 呼叫量
FROM
订单信息表
GROUP BY HOUR(
DATE_SUB(呼叫时间, INTERVAL 11 HOUR)
))
最高如下:
最低如下:
#4. 呼叫订单第二天继续呼叫的比例有多少?
select
sum(case when next_day-day=1 then 1 else 0 end)/count(*) as 次日呼叫比例
from
(select *,
lead(day)over(partition by 乘客id order by day) as next_day
from
(select t1.乘客id ,day(date_sub(t1.呼叫时间,interval 11 hour)) as day
from订单信息表 t1) t2) t3;
结果如下:



