【题目】
公司的app(类似滴滴、uber)为用户提供打车服务。现有四张表,分别是“司机数据”表,“订单数据”表,“在线时长数据”表,“城市匹配数据”表。(滴滴面试题)
下图左表是“司机数据”表的部分数据。为了便于讲解,之后在涉及到表的时候,用下图右表来代替。右表中展示了左表的部分代表性数据。
上表中的“产品线id”: 1是表示专车,2表示企业,3表示快车,4表示企业快车
业务问题
1. 分析出2020年8月各城市每天的司机数、快车订单量和快车流水数据。
2. 分析出2020年8月和9月,每个月的北京市新老司机(首单日期在当月为新司机)的司机数、在线时长和TPH(订单量/在线时长)数据。
3. 分别提取司机数大于20,司机总在线时长大于2小时,订单量大于1,乘客数大于1的城市名称数据。
(文末有模拟数据下载)
【解题思路】
为了方便数据处理,首先将这些表中所有的日期数据转化为日期格式 ‘年-月-日’ 的形式。需要使用 date_fromat 函数。
sql语句为
update 司机数据
set 日期=date_format(日期,'%Y-%m-%d');
update 司机数据
set 首次完成订单时间=date_format(首次完成订单时间,'%Y-%m-%d');
update 订单数据
set 日期=date_format(日期,'%Y-%m-%d');
update 在线时长数据
set 日期=date_format(日期,'%Y-%m-%d');
处理后的表如下图,可以发现对应日期列已经修改过来了。
接下来看需要分析的业务问题。
1. 提取2020年8月各城市每天的司机数、快车订单量和快车流水数据。
(1) 2020年8月各城市每天的司机数
使用逻辑树分析方法,拆解业务需求的每个部分。
“2020年8月”,可以用 between and 函数来对时间进行条件限制。
“每天的司机数”,司机数的计算用到的表是 “司机数据” 表。当出现“每天”要想到《猴子 从零学会sql》里讲过的分组汇总,来解决“每天”这样的问题。用“日期“来分组(group by),用 count(司机id) 来汇总司机数。
“各城市”,城市在“城市匹配数据“表中。也就是“每个城市”所以用“城市“来分组(group by)。
这里涉及到两个表“司机数据” 表和“城市匹配数据“表,所以遇到多表查询的情况,要想到