滴滴面試題：打車業務問題如何分析？

【題目】

公司的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');

​

set 首次完成訂單時間=date_format(首次完成訂單時間,'%Y-%m-%d');

update 訂單資料

update 線上時長資料

處理後的表如下圖，可以發現對應日期列已經修改過來了。

接下來看需要分析的業務問題。

1. 提取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”與“城市名稱”進行比對，是以我們用左聯結來進行比對。

查詢結果如下

1. 提取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？