圖解面試題：滴滴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) ;

set grab_time= date_sub(grab_time, interval 11 hour) ;

set cancel_time= date_sub(cancel_time, interval 11 hour) ;

update 訂單資訊表

set finish_time= date_sub(finish_time, interval 11 hour) ;

時間轉換結果如下圖：

按照以上操作，資料日期預處理完成。

（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 完單率

查詢結果如下

根據題目中名額定義：

呼叫應答時間=被應答訂單從呼叫到被應答時長總和/被應答訂單數量

被應答訂單從呼叫到被應答時長=被應答的時間（grab_time） - 呼叫的時間（call_time）。

這涉及到計算兩個日期之間的內插補點，《猴子 從零學會sql》裡講到對應單函數是timestampdiff。下圖是這個函數的用法。

我們回到題目，利用timestampdiff函數計算呼叫到被應答時長的總和。

綜上，相應的sql語句分析如下

（1）時間轉換

由于題目中要求的是“哪一個小時”，是以我們首先将資料格式化轉換成小時。新增一列來表示時間中的“小時”,列名設為call_time_hour。

-- 添加列

alter table 訂單資訊表 add column call_time_hour varchar(255);

利用date_format 函數，用于以不同的格式顯示日期資料，将将資料格式轉換成小時。

/**

給列添加資料

%k表示顯示的是24小時制中的小時

*/

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 最小次數

order by 最小次數 asc

limit 3;

呼叫訂單第二天繼續呼叫的比例=第二天繼續呼叫的使用者量/總的呼叫訂單量。

計算第二天繼續呼叫的使用者量的思路如下圖：

我們具體分析看每一部分。

（1）自關聯查詢，求得呼叫的時間間隔。由于我們需要時間的機關為天，是以我們使用date_format函數來提取出日期中的“年月日”部分。

-- 添加一列來顯示時間中的“年月日”部分

alter table 訂單資訊表 add column call_time_day varchar(255);

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？