sql 差值_滴滴2020年春招笔试题分析（Sql)

题目数据来自开心鸭专栏，侵删，仅供参考，有错误请及时指正，非常感谢

背景分析：

滴滴目前则同时在巴西和墨西哥运营其业务，滴滴位于圣保罗的总部，此前收购了当地的一家网约车公司——99公司。巴西的交通是个问题，公共交通工具很难搭乘，并且安全问题也很严重，因此选择打车或者网约车成了不二选择，所以像Uber、滴滴-99和Cabify这样的网约车公司非常重要。在中国，滴滴司机是一份非常不错的工作，强过其他许多工作选择，在巴西，由于经济问题，很多人有车却没有收入，当地人将滴滴司机这一职业作为额外收入，在这两种不同的想法和不同的安全环境下，巴西滴滴司机提供的服务情况是什么样的呢？

数据来源：

数据来自开心鸭专栏，滴滴2020年2月份春招笔试题目（侵删）

数据集内容理解：字段含义

根据理解：订单识别号和乘客识别号应该是唯一的

提出问题

1.订单的平均应答率是多少？完单率是多少？

2.每个订单的平均应答时间是多少？

3.从这一周的时间来看，呼叫次数最多的时间是几点（当地时间），最少的随时间是几点（当地时间）

4.客户留存的情况：呼叫订单第二天继续呼叫的比例是多少？

5.如果对这100个乘客进行分类，你认为要考虑那些因素？

数据清洗

首先我们先利用sql的导入功能将表导入sql

1.选择子集

这里所有的字段我们都需要用到，因此暂时不做处理

2.列名重命名

使用设计表的功能将列名改成中文，方便理解

2.删除重复值

前面了解字段时，我们认识到，订单id唯一的，因此当这两个字段同时重复时，我们视为重复值

#首先查找重复数据
select 订单号 from dididata
group by 订单号
having count(*)>=2
#结果显示没有重复值

3.缺失值处理

select count(order_id),count(passenger_id),count(call_time),count(grab_time),count(cancel_time),count(finish_time)
from dididata

共297条数据，因count不计入空值，因此没有空值

4 .数据类型转换

现在我们需要将部分字段的格式进行修改，将时间格式统一修改为datetime

1.因为是巴西的网约车服务，因此我们需要将时间统一修改为巴西时间，方便后续计算

已知巴西的时间比中国时间慢11小时

update dididata set 呼叫时间=date_add(呼叫时间,interval -11 hour);
update dididata set 应答时间=date_add(应答时间,interval -11 hour);
update dididata set 取消时间=date_add(取消时间,interval -11 hour);
update dididata set 完成时间=date_add(完成时间,interval -11 hour);

2.将呼叫时间单独拿出来，转换成呼叫时刻和呼叫日期

alter table dididata add 呼叫日期 date;
update dididata set 呼叫日期=substring(呼叫时间,1,10);

alter table dididata add 呼叫时刻 int(2);
update dididata set 呼叫时刻=substring(呼叫时间,12,2);

5.异常值处理

将不属于时间范围内的数据进行删除，这里我们以呼叫时间为标准，先查看有没有异常值

select 呼叫日期 from dididata
where 呼叫日期<'2018-03-05' or 呼叫日期>'2018-03-11'

我们没有发现异常值（注：尽量不要直接删除）

构建模型

1.订单的平均应答率是多少？完单率是多少？

应答率=应答订单/呼叫订单(呼叫订单被应答的比例，也就是说，那些没有应答的订单我们需要排除在外）

#因为不清楚应答时间的分布，我们可以先查找出来分析一下
select min(应答时间),max(应答时间) from dididata

小的应答时间表示没有应答到，最大应答时间表示这些订单在3月11均被应答完

select concat(round(count(订单号)/(select count(订单号) from dididata)*100,2),'%') as 应答率 from dididata
where 应答时间 >='2018-03-05' and 应答时间<='2018-03-12'

完单率=完成订单/呼叫订单（呼叫的订单中被完成的订单有多少）

#呼叫的订单也有可能有临时取消的订单,因此我们需要了解下完成时间的分布
select min(完成时间),max(完成时间) from dididata

我们可以看出，完成时间也是在3月11号

select concat(round(count(订单号)/(select count(订单号) from dididata where 呼叫时间>='2018-3-5' and 呼叫时间<='2018-3-12')*100,2),'%')as 完单率
from dididata
where 完成时间>='2018-3-5' and 完成时间<='2018-3-12'

2.每个订单的平均应答时间是多少

平均应答时间=被应答订单从呼叫被应答的时长总和/被应答订单数量

#为了方便计算，我们将他们的差值转换成时间戳
select avg(unix_timestamp(应答时间)-unix_timestamp(呼叫时间)) as 应答总时间 from dididata
where 应答时间>='2018-03-05' and 应答时间<='2018-03-12'

从这一周的时间来看，呼叫次数最多的时间是几点，最少的随时间是几点

(select 呼叫时刻,count(呼叫时刻) as 呼叫次数 from dididata
group by 呼叫时刻
order by 呼叫次数 desc
limit 1)
union
(select 呼叫时刻,count(呼叫时刻) as 呼叫次数 from dididata
group by 呼叫时刻
order by 呼叫次数 
limit 1)

呼叫次数最多的是18点，最少的是凌晨2点

4.客户留存的情况：呼叫订单第二天继续呼叫的比例是多少？

条件：第一天呼叫的乘客第二天选择继续呼叫，也就是继续使用滴滴出现的人数，占前一天的比例是多少

select concat(round(avg(继续呼叫比例)*100,2),'%') as 平均呼叫比例 from(select a.呼叫日期,count(distinct(a.乘客号)) as 乘客数,
count(distinct(a.乘客号))/(select count(distinct(c.乘客号)) from dididata c where a.呼叫日期=c.呼叫日期) as 继续呼叫比例
from dididata a
where 1=(select count(distinct(乘客号)) from dididata b 
where a.呼叫日期+1=b.呼叫日期
and a.乘客号=b.乘客号)
group by a.呼叫日期) as x

5.如果对这100个乘客进行分类，你认为要考虑那些因素？

这里使用RFM模型对客户进行分类

首先将用户分为完成订单的用户和未完成订单的用户

总结：

业务常规题，没有太难的考察，考察的要点有：子查询，关联子查询，topN问题，以及时间格式转换，sql面试题复习可以看我之前写的文章：

优梨：SQL常见求职面试题​zhuanlan.zhihu.com

在时间处理上容易出错，因为时间精确到了秒，所以特别要注意时间范围的问题，所以正式做题之前需要做好数据清洗

希望大家提出效率更高的方法，谢谢！