# 图解面试题：滴滴 2020 求职真题

![](/files/-MV5x98tKwCYA-dGqDGF)

**【题目】**

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

![](/files/-MV5xgvNWW7fysr9Y4G-)

![](/files/-MV5xmN-3N8Sub1p5H-2)

注意：

（1）表中的时间是北京时间，巴西比中国慢 11 小时。

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

![](/files/-MV5xprxastf8b_Yi5q-)

**问题**

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

**【解题步骤】**

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

![](/files/-MV5xtHQaxaSnJDzGI8M)

（1）日期格式化

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

![](/files/-MV5xzAIDaak6BSbBwPm)

![](/files/-MV5y0t6m5LdHqXRD99w)

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

![](/files/-MV5y8NCyHDmx3N2UIht)

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

```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);
```

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

![](/files/-MV5yCcT9tRDe62o9AbQ)

（2） 转换成巴西时间

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

![](/files/-MV5yGPoeIC2bXIou6Ej)

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

```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) ;
```

时间转换结果如下图：

![](/files/-MV5yN5BIlSPl9Kfm0Bz)

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

**1. 订单的应答率，完单率分别是多少？**

**（1）应答率**

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

![](/files/-MV5z-7f7NRqgHIK6IOW)

**呼叫订单：** 呼叫订单数等于呼叫时间（call\_time）这一列的数据总数，可以用 count(call\_time) 汇总。

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

![](/files/-MV5z2kBYBVe_gLR_YuU)

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

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

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

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

查询结果如下：

![](/files/-MV5z8IDhTP75lJi06v7)

**（2）完单率**

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

![](/files/-MV5zCFCr6HPSqyQ3pZi)

**完成订单：** 完成时间（finish\_time）这一列中，值**不等于‘1970’**&#x7684;数据数量为**有效**的完成订单数。如下图：红框的部分为完成订单。

![](/files/-MV5zH1i9MtpqxhIOW0c)

所以完成订单数为：

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

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

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

查询结果如下

![](/files/-MV5zL8K0ZvqlY_J2reT)

**2. 呼叫应答时间有多长？**

根据题目中指标定义：

> 呼叫应答时间 = 被应答订单从呼叫到被应答时长总和 / 被应答订单数量

被应答订单从呼叫到被应答时长 = 被应答的时间（grab\_time） - 呼叫的时间（call\_time）。

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

![](/files/-MV5zQ11fcLikyH82aZ7)

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

![](/files/-MV5zV3dUFaVHbk4Pxqw)

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

![](/files/-MV5zbRaWxPDlisG-5FF)

‍

查询结果如下

![](/files/-MV5zfia-VIP3WQ9zY_l)

**3.从这一周的数据来看，呼叫量最高的是哪一个小时（当地时间）？呼叫量最少的是哪一个小时（当地时间）？**

（1）时间转换

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

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

利用 date\_format 函数，用于以不同的格式显示日期数据，将将数据格式转换成小时。

```sql
/** 
给列添加数据
%k表示显示的是24小时制中的小时
*/
update 订单信息表
set call_time_hour=date_format(call_time,'%k');
```

转化后的表如下图

![](/files/-MV5zpt37mTPoqVD7HVk)

（2）呼叫量最高的是哪一个小时？

呼叫订单是 order\_id 列。按 “每个小时” 分组（group by call\_time\_hour），然后统计每个小时的呼叫订单量 count(order\_id)，然后排序就可以知道哪个小时的订单量最高。

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

![](/files/-MV5zwvL9iVt7JB-XEGe)

此时得到查询结果如下图

![](/files/-MV6-3S3ytWU-bC01AE5)

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

![](/files/-MV6-7lUl_Yv_WsVsnqa)

sql 语句如下：

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

（3）呼叫量最少的是哪一个小时？

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

![](/files/-MV6-CdNri-GlfPAxX2W)

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

**4.呼叫订单第二天继续呼叫的比例有多少？**

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

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

![](/files/-MV6-Gps6TQRGUVQRthC)

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

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

![](/files/-MV6-Mktmj2rPJd4WF1x)

sql 语句如下：

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

此时变化后的表如下：

![](/files/-MV6-T0gEDQQykqOgkUL)

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

![](/files/-MV6-kpYcULRidu2IuPA)

此时查询结果如下

![](/files/-MV6-paTnRt6urBbp-qc)

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

![](/files/-MV6-z_CTVFnxMF6H0IU)

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

![](/files/-MV602zU6RX5ITWsMmXP)

此时查询结果如下图

![](/files/-MV607-08tJe_tcLuqmk)

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

![](/files/-MV60CLiDP4OJzrcLqJs)

查询结果如下图

![](/files/-MV60H8bkzJ06wrGbo7i)

**5.（选做）如果要对表中乘客进行分类，你认为需要参考哪一些因素？**

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

![](/files/-MV60LTO9-FpYaCQ9Ub4)

**用户行为分类**

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

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

**用户价值分类**

使用之前学过的 [RFM 分析方法](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649246563\&idx=2\&sn=3ffe509999d144d23dec5acc101fc2ef\&chksm=835fc353b4284a45ce01391453fe2fec1b225bbd6bbdb67dd7f304aacdd4f21f60d0b27ba309\&scene=21#wechat_redirect)，对用户按价值分类。

![](/files/-MV60Oy2glgeM1ckE96j)

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

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

**【本题考点】**

1. 对日期数据的处理，掌握题目里讲过对常用日期处理方法。
2. 考查分析思维能力。使用学过的如何使用[数据分析解决问题的框架](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649246563\&idx=2\&sn=3ffe509999d144d23dec5acc101fc2ef\&chksm=835fc353b4284a45ce01391453fe2fec1b225bbd6bbdb67dd7f304aacdd4f21f60d0b27ba309\&scene=21#wechat_redirect)来解决。

推荐：[如何从零学会 sql？](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)

![](/files/-MV60_LXZyk6XgFi6TFM)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://houzidata.gitbook.io/sql/di-5-zhang-xiang-mu-shi-zhan/tu-jie-mian-shi-ti-di-di-2020-qiu-zhi-zhen-ti.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.