图解面试题:滴滴2020求职真题-阿里云开发者社区

开发者社区> 大数据> 正文

图解面试题:滴滴2020求职真题

简介:

【题目】

“订单信息表”里记录了巴西乘客使用打车软件的信息,包括订单呼叫、应答、取消、完单时间。(滴滴2020年笔试题)

注意:
(1)表中的时间是北京时间,巴西比中国慢11小时。

(2)应答时间列的数据值如果是“1970”年,表示该订单没有司机应答,属于无效订单。

问题

  1. 订单的应答率,完单率分别是多少?
  2. 呼叫应答时间有多长?
  3. 从这一周的数据来看,呼叫量最高的是哪一个小时(当地时间)?呼叫量最少的是哪一个小时(当地时间)?
  4. 呼叫订单第二天继续呼叫的比例有多少?
  5. (选做)如果要对乘客进行分类,你认为需要参考哪一些因素?

【解题步骤】

我们首先对数据进行预处理,将北京时间转化为巴西时间。具体需要分两步来实现,首先为了确保表中的时间为标准的日期格式,我们统一对其进行日期格式处理。然后再将处理后的日期转换成巴西时间。

(1)日期格式化

由于在日期格式化中,我们会涉及到需要修改表中的日期数据,因此考虑用update语句。而修改表的具体操作会涉及到日期数据类型之间的转换,我们考虑用cast函数。

由于表中的时间应是datetime的格式,也就是精确到时分秒(YYYY-MM-DD HH:mm:ss)。转换后的效果如下图。

因此可以写出下列sql语句。

update 订单信息表 set call_time=cast(call_time as datetime);

update 订单信息表 set grab_time=cast(grab_time as datetime);

update 订单信息表 set cancel_time=cast(cancel_time as datetime);

update 订单信息表 set finish_time=cast(finish_time as datetime);

日期格式化后的表如下图。

(2) 转换成巴西时间

由于数据中的时间为北京时间,而且已知巴西比中国慢11小时,因此我们这里使用 date_sub函数。

因此可以写出下列sql语句:

update 订单信息表
set call_time= date_sub(call_time, interval 11 hour) ;

update 订单信息表
set grab_time= date_sub(grab_time, interval 11 hour) ;

update 订单信息表
set cancel_time= date_sub(cancel_time, interval 11 hour) ;

update 订单信息表
set finish_time= date_sub(finish_time, interval 11 hour) ;

时间转换结果如下图:

按照以上操作,数据日期预处理完成。

  1. 订单的应答率,完单率分别是多少?

(1)应答率

应答率=应答订单数/呼叫订单数

呼叫订单: 呼叫订单数等于呼叫时间(call_time)这一列的数据总数,可以用count(call_time)汇总。

应答订单:应答订单数等于应答时间(grab_time)这一列的数据总数,可以用count(grab_time)汇总。需要注意,这一列里的值不等于‘1970’的数据的数量才是有效的应答订单数。如下图:红框的部分为应答订单。

根据题目的业务要求,需要对不同的条件进行统计,在《猴子 从零学会sql》里讲过条件判断要用case when表达式。所以应答订单数对应的sql是:

sum(case when grab_time <> 1970 then 1 else 0 end)

现在可以计算出指标 应答率=应答订单数/呼叫订单数 :

select sum(case when grab_time <> 1970 then 1 else 0 end)/count(call_time) as 应答率
from 订单信息表;

查询结果如下:

(2)完单率

完单率=完成订单数/呼叫订单数

完成订单: 完成时间(finish_time)这一列中,值不等于‘1970’的数据数量为有效的完成订单数。如下图:红框的部分为完成订单。

所以完成订单数为:

sum(case when finish_time <> 1970 then 1 else 0 end)

现在可以计算出指标完单率=完成订单数/呼叫订单数 :

select sum(case when finish_time <> 1970 then 1 else 0 end)/count(*) as 完单率
from 订单信息表;

查询结果如下

  1. 呼叫应答时间有多长?

根据题目中指标定义:
呼叫应答时间=被应答订单从呼叫到被应答时长总和/被应答订单数量

被应答订单从呼叫到被应答时长=被应答的时间(grab_time) - 呼叫的时间(call_time)。

这涉及到计算两个日期之间的差值,《猴子 从零学会sql》里讲到对应单函数是timestampdiff。下图是这个函数的用法。

我们回到题目,利用timestampdiff函数计算呼叫到被应答时长的总和。

综上,相应的sql语句分析如下

查询结果如下

  1. 从这一周的数据来看,呼叫量最高的是哪一个小时(当地时间)?呼叫量最少的是哪一个小时(当地时间)?

(1)时间转换

由于题目中要求的是“哪一个小时”,因此我们首先将数据格式化转换成小时。新增一列来表示时间中的“小时”,列名设为call_time_hour。

-- 添加列
alter table 订单信息表 add column call_time_hour varchar(255);

利用date_format 函数,用于以不同的格式显示日期数据,将将数据格式转换成小时。
/**
给列添加数据
%k表示显示的是24小时制中的小时
*/
update 订单信息表
set call_time_hour=date_format(call_time,'%k');

转化后的表如下图

(2)呼叫量最高的是哪一个小时?

呼叫订单是order_id列。按“每个小时”分组(group by call_time_hour),然后统计每个小时的呼叫订单量count(order_id),然后排序就可以知道哪个小时的订单量最高。

下图给出sql语句分析过程:

此时得到查询结果如下图

因为题目要求的是排序后的最大值(呼叫量最高的小时),可以用limit子句 来筛选出第一行数据。

sql语句如下:

select call_time_hour,count(order_id) as 最大次数
from 订单信息表
group by call_time_hour
order by 最大次数 desc
limit 1;

(2)呼叫量最少的是哪一个小时?

接着上面的排序结果,我们看到有3个呼叫小时的数据都为最小次数,用limit 3都将它们筛选出来即可。

select call_time_hour,count(order_id) as 最小次数
from 订单信息表
group by call_time_hour
order by 最小次数 asc
limit 3;

  1. 呼叫订单第二天继续呼叫的比例有多少?

呼叫订单第二天继续呼叫的比例=第二天继续呼叫的用户量/总的呼叫订单量。

计算第二天继续呼叫的用户量的思路如下图:

我们具体分析看每一部分。

(1)自关联查询,求得呼叫的时间间隔。由于我们需要时间的单位为天,因此我们使用date_format函数来提取出日期中的“年月日”部分。

sql语句如下:
-- 添加一列来显示时间中的“年月日”部分
alter table 订单信息表 add column call_time_day varchar(255);
update 订单信息表
set call_time_day=date_format(call_time,'%Y-%m-%d');

此时变化后的表如下:

我们接下来利用表的联结来计算相隔天数。这里由于涉及到计算相隔的天数之差,我们使用上面讲过的timestampdiff函数。单位为天。

此时查询结果如下

筛选出时间差为1天的数据,也就是间隔=1的数据。

利用子查询嵌套,将上面的查询结果作为新表,在其中做出筛选,并求和。sql语句分析如下图。

此时查询结果如下图

最后我们计算出第二天继续呼叫比例

查询结果如下图

  1. (选做)如果要对表中乘客进行分类,你认为需要参考哪一些因素?

我们可以从以下两个角度来考虑对用户分类。

用户行为分类

1) 根据完成时间和接单时间,可大致计算出乘客在乘车过程中所消耗的时间,对这个时间进行预判,属于长途、中途或者是短途,来分析乘客的乘车习惯。

2) 根据呼叫时间可以判断乘客是在时间点发单的,乘客需求是如何产生的,可分析用户在哪些场景有乘车需求,上班、下班、就餐、出游、临时等场景。

用户价值分类

使用之前学过的RFM分析方法,对用户按价值分类。

RFM具体到本题可以做以下定义:

R:最近一次乘客的完单时间。
F:乘客打车的频率。
M:打车消费的金额。此处可以用乘车过程消耗的时长来代替等。

【本题考点】

1.对日期数据的处理,掌握题目里讲过对常用日期处理方法。
2.考查分析思维能力。使用学过的如何使用数据分析解决问题的框架来解决。

推荐:如何从零学会sql?

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
大数据
使用钉钉扫一扫加入圈子
+ 订阅

大数据计算实践乐园,近距离学习前沿技术

其他文章