面经 | 滴滴数据分析岗试题分享
(给机器学习算法与Python实战加星标,提升AI技能)
1
![](https://filescdn.proginn.com/20af59289949988b42bed0d40705b181/8fee3cd7a2c728d63ecfde1b2d8b0419.webp)
2
订单的应答率、完单率分别是多少?
呼叫应答时间多长?
从这一周的数据来看,呼叫量最高的是哪一个小时(当地时间)?呼叫量最少的是哪一个小时(当地时间)?
呼叫订单第二天继续呼叫的比例有多少?
如果要对表中乘客进行分类,你认为需要参考哪一些因素?
3
将时间相关列转换格式
按巴西比中国慢11小时,将表中北京时间转换为巴西时间
![](https://filescdn.proginn.com/a9c4c4fa1ac96052fb6960529b48ad47/8181ffb6ccc62cfccafdda20b0f31507.webp)
-- 利用cast函数转换成日期数据
update didi set
call_time = cast(call_time as datetime),
grab_time = cast(grab_time as datetime),
cancel_time = cast(cancel_time as datetime),
finish_time = cast(finish_time as datetime);
-- 将北京时间调整为巴西时间,date_sub函数
update didi set
call_time = date_sub(call_time,interval 11 hour ),
grab_time = date_sub(grab_time,interval 11 hour ),
cancel_time = date_sub(cancel_time,interval 11 hour ),
finish_time = date_sub(finish_time,interval 11 hour );
![](https://filescdn.proginn.com/a9c4c4fa1ac96052fb6960529b48ad47/8181ffb6ccc62cfccafdda20b0f31507.webp)
应答率 = 应答订单数 / 呼叫订单数
完单率 = 完成订单数 / 呼叫订单数
select sum(if(year(grab_time)<>1970,1,0))/count(call_time)as '应答率',
sum(if(year(finish_time)<>1970,1,0))/count(call_time)as '完单率'
from didi
![](https://filescdn.proginn.com/2d1f2975c56ef4ff97a405c5d25d8fed/85db1cf39b9f2071387c061535e450b1.webp)
![](https://filescdn.proginn.com/3b5c4c958762b17396ebe76df5310187/f7c1c418ceec81978c9de2b93cf79e16.webp)
-- 使用timestampdiff函数,计算应答时间与呼叫时间之间的时长
select sum(TIMESTAMPDIFF(MINUTE,call_time,grab_time))/count(grab_time)as '呼叫应答时间'
from didi
where year(grab_time)<>1970;
-- 新增一列
alter table didi add column call_time_hour VARCHAR(255);
-- 使用substr函数做字符串截取,为新列赋值
update didi set call_time_hour = SUBSTR(call_time from 12 for 2);
-- 方法2:使用date_format函数转换格式
update didi set call_time_hour = DATE_FORMAT(call_time,'%k')
![](https://filescdn.proginn.com/473c00ee73abd79bce3c14d4f82222b2/7305e0e872d55742745b09d4e07899af.webp)
-- 找出呼叫量最高的小时,显示2行防止出现重复值。
select call_time_hour,count(call_time)as'呼叫量'
from didi
GROUP BY call_time_hour
ORDER BY count(call_time) desc
limit 2;
![](https://filescdn.proginn.com/7e91bc2a6efede0328d8594e5a181304/1ef737affeda60aa1438197c2e643c92.webp)
-- 找出呼叫量最少的小时,显示3行确认是否有第三个相等值。
select call_time_hour,count(call_time)as'呼叫量'
from didi
GROUP BY call_time_hour
ORDER BY count(call_time) asc
limit 3;
![](https://filescdn.proginn.com/207232ba394ee421061a88b70a99ef21/2ad7be8a2e0527cbba8f111064362c83.webp)
select count(DISTINCT a.order_id)/(select count(DISTINCT order_id) from didi)as'第二天继续呼叫比例'
from didi a join didi b
on a.passenger_id = b.passenger_id
where datediff(a.call_time,b.call_time)=1;
![](https://filescdn.proginn.com/586c622d6732bbf3a63b05851186a98c/5c006b4a5eb46c8e9d9549a182edbb63.webp)
R:乘客上一次打车距离3月11日的时间间隔
F:乘客在数据期间的打车频率
M:打车消费金额(表中无打车金额,可以用完成订单总时长代替)
![](https://filescdn.proginn.com/c36babff7e3821219e14a5bf44698f3e/41e6361b62d75a70657c102a3ba84834.webp)
请后台回复【入群】
推荐阅读
(点击标题可跳转阅读)
老铁,三连支持一下,好吗?↓↓↓
评论