sql面试题5 滴滴出行数据分析师面试

朋友说实际工作中，能用on就不用where，因为on可以让表缩小，where是形成表以后筛选

这里好几个时间函数

1.date_sub(cast(call_time as datetime),interval 11 hour) 先转换datetime，然后sub减去11小时

2.timestampdiff(minute,call_time_bz,grab_time_bz) 时间差 grab_time_bz-call_time_bz以minute计

3.date_format(a.call_time_bz,'%k') as call_time_hour, -- 学到了提取小时的方法，不能写成h 会只有12小时，导致晚上20点也是8点，可以写 H是24小时的0102到24，写k就是24小时的123到24没有前面的0

4.date_format(a.call_time_bz,'%Y-%m-%d')=date_sub(date_format(b.call_time_bz,'%Y-%m-%d'),interval 1 day) 提取年月日 date_add date_sub(原时间，interval x day) x minute

5.我用了 a.call_time_bz >='2018-03-05' and a.call_time_bz<'2018-03-12'

和a.call_time_bz between '2018-03-05' and '2018-03-12'在mysql里面是一样的，大概是左边取到右边不取到，我之前a.call_time_bz >='2018-03-05' and a.call_time_bz<='2018-03-11'的时候 3.11 零点以后的就取不到了emm～

滴滴出行数据分析师面试经验

开心鸭

一个玩数据分析的鸭鸭

已关注

20 人赞同了该文章

最近投了滴滴国际部的数据分析师面试，第一轮线上面试，题目有点复杂，很容易做错，不过也是基础的业务SQL题目。

Part. 1 数据

ta本来的解法挺好https://zhuanlan.zhihu.com/p/107495784

我也做了一下

因为自己不习惯alter表，两个代码都试了一遍，放一下自己的供记录探讨，因为自己也是很容易出错的人～大致是理清楚了～很感谢楼主的分享～文章都很棒～

1。对应楼主的计算巴西时间，如果不alter表的话可以这样写，本质是一样的：

(select *,date_sub(cast(call_time as datetime),interval 11 hour) as call_time_bz,

date_sub(cast(grab_time as datetime),interval 11 hour) as grab_time_bz,

date_sub(cast(cancel_time as datetime),interval 11 hour) as cancel_time_bz,

date_sub(cast(finish_time as datetime),interval 11 hour) as finish_time_bz

from didi2)a

2。计算应答率，应答率，呼叫应答时间

select

b.grab_sum/b.call_sum as '应答率',

b.finish_sum/b.call_sum as '完单率',

b.call_grab_time/b.grab_sum as '呼叫应答时间'

from

(

select

sum(case when '2018-03-05'<= a.grab_time_bz and a.grab_time_bz<'2018-03-12' then 1 else 0 end) as grab_sum,

sum(case when '2018-03-05'<= a.finish_time_bz and a.finish_time_bz<'2018-03-12' then 1 else 0 end) as finish_sum,

sum(case when '2018-03-05'<= a.call_time_bz and a.call_time_bz<'2018-03-12' then 1 else 0 end) as call_sum,

sum(case when '2018-03-05'<= a.grab_time_bz and a.grab_time_bz<'2018-03-12' then timestampdiff(minute,call_time_bz,grab_time_bz) else 0 end) as call_grab_time -- 在这个时间内，就输出grab和call的差值，以minute计

from

(select *,date_sub(cast(call_time as datetime),interval 11 hour) as call_time_bz,

date_sub(cast(grab_time as datetime),interval 11 hour) as grab_time_bz,

date_sub(cast(cancel_time as datetime),interval 11 hour) as cancel_time_bz,

date_sub(cast(finish_time as datetime),interval 11 hour) as finish_time_bz

from didi2)a

3。select

date_format(a.call_time_bz,'%k') as call_time_hour, -- 学到了提取小时的方法，不能写成h 会只有12小时，导致晚上20点也是8点，可以写 H是24小时的0102到24，写k就是24小时的123到24没有前面的0

count(a.order_id) as call_sum

-- sum(case when a.call_time_bz is not null then 1 else 0 end) as call_sum -- 我之前是按call time来算的，感觉orderid和call time差不多

from

(select *,date_sub(cast(call_time as datetime),interval 11 hour) as call_time_bz,

date_sub(cast(grab_time as datetime),interval 11 hour) as grab_time_bz,

date_sub(cast(cancel_time as datetime),interval 11 hour) as cancel_time_bz,

date_sub(cast(finish_time as datetime),interval 11 hour) as finish_time_bz

from didi2)a

group by 1

order by call_sum desc -- 或者asc

limit 0,1

本来想用row_number写写崩了还是这样比较好

4。sum(case when b.call_time_bz is not null and b.call_time_bz >='2018-03-05' and b.call_time_bz<'2018-03-12'then 1 else 0 end) /count(a.call_time_bz) ,

-- count(a.call_time_bz)

from

(select *,date_sub(cast(call_time as datetime),interval 11 hour) as call_time_bz,

date_sub(cast(grab_time as datetime),interval 11 hour) as grab_time_bz,

date_sub(cast(cancel_time as datetime),interval 11 hour) as cancel_time_bz,

date_sub(cast(finish_time as datetime),interval 11 hour) as finish_time_bz

from didi2)a

left join a as b

on a.passenger_id=b.passenger_id

and date_format(a.call_time_bz,'%Y-%m-%d')=date_sub(date_format(b.call_time_bz,'%Y-%m-%d'),interval 1 day)

where a.call_time_bz >='2018-03-05' and a.call_time_bz<'2018-03-12'

-- b.call_time_bz >='2018-03-05' and b.call_time_bz<'2018-03-12'

我发现在我写的这种情况里，不能在这里加b的这条因为我用的是left join 和on ，后面计算的时候就需要b是有null的，a是整体的，然后让b是非空的/a的整体来算得比例，如果b的限制加了以后 ab都变成一样的数目，结果就是100%了，当然如果这样的话，那就类似楼主的算法一样直接除以count原先的总数也就可以了，楼主写的版本还是比较好理解的～

特别感谢鸭鸭，感觉你的思路都很棒啊～～～

今天特别来学习了一下，记录几个有出入的点。

1。关于计算应答率等的count（*），如果要严谨一些的话

select sum(case when grab_time between '2018-03-05' and '2018-03-12' then 1 else 0 end)/count(*) as 应答率,

sum(case when finish_time between '2018-03-05' and '2018-03-12' then 1 else 0 end)/count(*) as 完答率

from didi

这个地方的count(*)里面也可能存在5-12范围之外的，还是改成count(call time between。。。)比较好～

2.另外between的用法在不同的sql里面是不是取到，是不太一样的？？？

后来我就用了最保守的>=的时间判断啦～这个细节提的真的不错～

我用了 a.call_time_bz >='2018-03-05' and a.call_time_bz<'2018-03-12'

3.最后第四题的会产生对称所以 /2 ，这个我自己也算了一遍，我认为是不用哒。之前做过类似的题目，也是自连接，然后要除以2，但是那个情况是去除不一样的，用a<>b 以外就没有大小关系了。

这里用了timestampdiff其实算出来就是有正负的，已经限定了间隔就是比如b比a大一天，所以是不用除以2的。

最后想说看楼主写的很长见识～自己也是有很多地方不够扎实，也很少用到直接alter 表的做法，以及看到评论区的解释date_format()是显示不同格式的日期。cast是转换格式感觉很好。

如果要对这100个乘客进行分类，你认为需要参考哪一些因素？

我们的数据提供了完成时间和接单时间，忽略司机到达乘客的接单地点的时间，我们可以计算出乘客在乘车中花了多少时间，对于这个时间可以进行预判，这是长途、中途还是短途，可以分析乘客的乘车习惯。

按照我们的发单时间可以判断乘客是在时间点发单的，他的需求是怎么产生的，比如切分成上班、下班、就餐、出游、临时等场景。

使用RFM模型，这里的数据提供了最近一次打车的时间并且可以计算出打车频率，将用户切分成重要价值用户和重要发展用户或者中要保持用户和一般价值用户。对不用的用户考虑不用的用户挽留策略。对于打车频率高但是近期没有消费的重要保持用户，使用邮件推送、APP push的方法、活动短信提醒的方法召回。对于打车频率不高但是近期有打车的重要发展用户，应该用会员权益或者发放优惠券的形式促进消费次数。

高峰期集中在18点，说明用户大多喜欢下午因公打车。可以考虑从远处进行调度车辆，看能否起始地和目的地形成互补，不造成空车回去或者过去。

因为知道了取消订单的量，可以考虑在高峰期间取消订单的数量也就是订单的满足率，滴滴公司应该着重考虑快车调度问题。

我们可以用sql计算出打车的平均时长，从滴滴数据库中获得该地区总车辆，从而得出车辆的需求量。

总结：

题目存在很多易错点，因为是真实的业务问题，特别是对日期的处理大家要加以注意。