【题目】
公司的app(类似滴滴、uber)为用户提供打车服务。现有四张表,分别是“司机数据”表,“订单数据”表,“在线时长数据”表,“城市匹配数据”表。(滴滴面试题)
下图左表是“司机数据”表的部分数据。为了便于讲解,之后在涉及到表的时候,用下图右表来代替。右表中展示了左表的部分代表性数据。
上表中的“产品线id”: 1是表示专车,2表示企业,3表示快车,4表示企业快车
业务问题
- 分析出2020年8月各城市每天的司机数、快车订单量和快车流水数据。
- 分析出2020年8月和9月,每个月的北京市新老司机(首单日期在当月为新司机)的司机数、在线时长和TPH(订单量/在线时长)数据。
- 分别提取司机数大于20,司机总在线时长大于2小时,订单量大于1,乘客数大于1的城市名称数据。
(文末有模拟数据下载)
【解题思路】
为了方便数据处理,首先将这些表中所有的日期数据转化为日期格式 ‘年-月-日’ 的形式。需要使用 date_fromat 函数。
sql语句为
update 司机数据
set 日期=date_format(日期,'%Y-%m-%d');
set 首次完成订单时间=date_format(首次完成订单时间,'%Y-%m-%d');
update 订单数据
update 在线时长数据
处理后的表如下图,可以发现对应日期列已经修改过来了。
接下来看需要分析的业务问题。
- 提取2020年8月各城市每天的司机数、快车订单量和快车流水数据。
(1) 2020年8月各城市每天的司机数
使用逻辑树分析方法,拆解业务需求的每个部分。
“2020年8月”,可以用 between and 函数来对时间进行条件限制。
“每天的司机数”,司机数的计算用到的表是 “司机数据” 表。当出现“每天”要想到《猴子 从零学会sql》里讲过的分组汇总,来解决“每天”这样的问题。用“日期“来分组(group by),用 count(司机id) 来汇总司机数。
“各城市”,城市在“城市匹配数据“表中。也就是“每个城市”所以用“城市“来分组(group by)。
这里涉及到两个表“司机数据” 表和“城市匹配数据“表,所以遇到多表查询的情况,要想到《猴子 从零学会sql》里讲过的多表联结。下图是两表联结的条件(通过城市id联结)。
使用哪种联结呢?
因为要查询的是司机数,所以要保留“司机数据”表中的全部数据,因此使用左联结。
sql语句如下
查询结果如下图(部分展示)
(2) 2020年8月各城市每天的快车订单量
“每天的快车订单量”,对于计算快车订单量,用到的表是 “订单数据” 表。根据题目的字段解释,“产品线id”: 1是表示专车,2表示企业,3表示快车,4表示企业快车。可以用where子句把快车数据先筛选出来(产品线id=3)。
当出现“每天”要想到《猴子 从零学会sql》里讲过的分组汇总,来解决“每天”这样的问题。用“日期“来分组(group by),用 count(订单id) 来汇总订单量。
在 “订单数据” 表、“司机数据”表中都没有城市数据,所以需要三表联结,下面是3表的关系图。
因为要查询的是快车订单量,所以要保留“订单数据”表中的全部数据,因此使用左联结来与“司机数据”进行联结(联结依据为“司机id”)。然后,因为要对第一次联结后的表的“城市id”与“城市名称”进行匹配,所以我们用左联结来进行匹配。
查询结果如下图
(3) 2020年8月各城市每天的快车流水数据
“2020年8月”,可以用 between and 来对时间进行条件限制。
“每天的快车流水数据”,对于计算快车订单量,用到的表是 “订单数据” 表。根据题目的字段解释,“产品线id”: 1是表示专车,2表示企业,3表示快车,4表示企业快车。可以用where子句把快车数据先筛选出来(产品线id=3)。
当出现“每天”要想到《猴子 从零学会sql》里讲过的分组汇总,来解决“每天”这样的问题。用“日期“来分组(group by),用 sum(流水) 来汇总流水。
因为要查询的是快车流水量,所以要保留“订单数据”表中的全部数据,因此使用左联结来与“司机数据”进行联结(联结依据为“司机id”)。然后,因为要对第一次联结后的表的“城市id”与“城市名称”进行匹配,所以我们用左联结来进行匹配。
查询结果如下
- 提取2020年8月和9月,每个月的北京市新老司机(首单日期在当月为新司机)的司机数、在线时长和TPH(订单量/在线时长)数据。
我们将新老司机分开来分析,先针对新司机进行提取,然后老司机同理可得。
(1)提取2020年8月和9月,每个月的北京市新司机的司机数。
使用多维度拆解分析方法来拆解题目为以下子问题:
1)每个月的司机数
2)条件:新司机
2)时间条件:2020年8月和9月
3)城市条件:北京市
先来看子问题1:每个月的司机数
对于司机数的计算,用到 “ 司机数据” 表。根据《猴子 从零学会sql》里讲过的,遇到“每个”这类型问题要用分组汇总。“每个月”按月份分组(group by),用count(司机id)来汇总司机数。
select count(司机id) as 司机数
from 司机数据
group by 月份;
这里的月份怎么得到呢?
可以通过“日期”列获得:date_format(日期,'%Y-%m')
所以,上面sql修改为:
group by date_format(日期,'%Y-%m') as 月份;
子问题2,条件:新司机å
对于 ‘新司机’ 这个条件,由题目中的释义可知:首单日期在当月为新司机。因此我们通过比较 "日期" 与 “首次完成订单日” 两列的年月 相等的,即为新司机。
我们用函数 year(日期) 来提取日期中的年份。用函数 month(日期) 来提取日期中的月份。
利用如下“where”条件来表示新司机。
where year(首次完成订单时间)=year(日期)
and month(首次完成订单时间)=month(日期)
加入“新司机”条件后的sql如下:
select count(司机id) as 新司机数
group by date_format(日期,'%Y-%m') as 年月;
子问题3,时间条件:2020年8月和9月
利用where加条件,between...and 函数限制时间范围。
where 日期 between '2020-08-01' and '2020-08-31'
or 日期 between '2020-09-01' and '2020-09-31'
and (日期 between '2020-08-01' and '2020-08-31'
or 日期 between '2020-09-01' and '2020-09-31')
子问题4,城市条件:北京市
利用where添加城市条件,城市id等于北京市的id。
where 城市id ='100000'
最终sql如下
(2) 提取2020年8月和9月,每个月的北京市新司机的在线时长。
对于在线时长的计算,用到 “ 在线时长数据” 表。根据《猴子 从零学会sql》里讲过的,遇到“每个”这类型问题要用分组汇总。“每个月”按月份分组(group by),在线时长的总长利用sum(在线时长)来计算。
而我们发现新司机在 “在线时长数据” 表 中并没有,而是在 “司机数据”表 中通过查询语句才能得到,因此我们用两个表的联结,得到 8,9月北京新司机的在线时长,然后再利用子查询,使用sum(在线时长)得到总时长。
sql语句解析如下
(3) 提取2020年8月和9月,每个月的北京市新司机的TPH(订单量/在线时长)。
由题可知,TPH=订单量/在线时长。其中在线时长我们在上一题中已经求得,因此只需求出订单量即可。
订单量的计算,会用到 “订单数据” 表,用count(订单id)来计算。然后思路与上题一样,新司机在 “在线时长数据” 表中并没有,而是在 “司机数据”表 中通过查询语句才能得到。
因此我们用两个表的联结,得到 8,9月北京新司机的在线时长,然后再利用子查询,使用count(订单id)得到总订单量。
因此,2020年8月和9月,每个月的北京市新司机的TPH=订单量/在线时长=2/4=0.5。
对于老司机的司机数,在线时长以及TPH,同理可得,只需要将 “新司机” 的条件 换成 “老司机” 即可,其他语句都不变。
(1) 司机数大于20的城市名称。
司机数的计算用count(司机id),用到的是“司机数据”表,城市名称在 “城市匹配数据”中,用表的联结。联结图如下。
(2) 司机总在线时长大于2小时城市名称。
总在线时长用sum(在线时长)来计算,用的是 “在线时长数据”表,而这个表中没有城市id,因此我们需要先联结“司机数据”表,得到城市id,再通过联结“城市匹配数据”表,得到对应的城市名称。
sql 语句解析如下
(3) 订单量大于1的城市名称。
订单量的计算用count(订单id),乘客数的计算用count(乘客id),用到的表为“订单数据表”,而这个表中没有城市id。
我们需要先通过“司机数据”表联结,得到对应的城市id,在通过“城市匹配数据”表进行联结,得到相应的城市名称。
(4) 乘客数大于1的城市名称。
乘客数通过count(乘客id)来计算,用到的表为“订单数据”表,而这个表中并没有城市id,所以我们需要联结 “司机数据”表来得到相应的城市id,再通过“城市匹配数据”联结,得到相应的城市名称。
【本题考点】
1.这次试题重点要考察的是表的联结。当题目中涉及到多个表之间的关系时,我们要找到多个表之间是通过什么条件关联的,然后进行多表关联。
2.考查如何将复杂问题拆解为简单问题的能力,可以使用逻辑树分析方法。
3.如何下载案例数据?
推荐:如何从零学会sql?