2020年滴滴數(shù)據(jù)分析面試題

2020年滴滴數(shù)據(jù)分析面試題題目：?jiǎn)栴}：1.訂單的應(yīng)答率、完單率分別是多少？2.呼叫應(yīng)答時(shí)間多長(zhǎng)？3.從這一周的數(shù)據(jù)來看，呼叫量最高的是哪一個(gè)小時(shí)（當(dāng)?shù)貢r(shí)間）？呼叫量最少的是哪一個(gè)小時(shí)（當(dāng)?shù)貢r(shí)間）？4.呼叫訂單第二天繼續(xù)呼叫的比例有多少？5.如果要對(duì)表中乘客進(jìn)行分類，你認(rèn)為需要參考哪一些因素？解題過程：導(dǎo)入mysql數(shù)據(jù)庫(kù)及數(shù)據(jù)預(yù)處理觀察表中時(shí)間相關(guān)數(shù)據(jù)結(jié)合題目，要做出2步數(shù)據(jù)預(yù)處理【1】將時(shí)間相關(guān)列轉(zhuǎn)換格式【2】按巴西比中國(guó)慢11小時(shí)，將表中北京時(shí)間轉(zhuǎn)換為巴西時(shí)間。--利用cast函數(shù)轉(zhuǎn)換成日期數(shù)據(jù)update

didi

setcall_time=

cast(call_time

as

datetime),grab_time=

cast(grab_time

as

datetime),cancel_time=

cast(cancel_time

as

datetime),finish_time=

cast(finish_time

as

datetime);--將北京時(shí)間調(diào)整為巴西時(shí)間，date_sub函數(shù)update

didi

setcall_time=

date_sub(call_time,interval

11

hour

),grab_time=

date_sub(grab_time,interval

11

hour

),cancel_time=

date_sub(cancel_time,interval

11

hour

),finish_time=

date_sub(finish_time,interval

11

hour

);處理后數(shù)據(jù)問題1：訂單的應(yīng)答率、完單率分別是多少？應(yīng)答率=應(yīng)答訂單數(shù)/呼叫訂單數(shù)完單率=完成訂單數(shù)/呼叫訂單數(shù)select

sum(if(year(grab_time)<>1970,1,0))/count(call_time)as

'應(yīng)答率',sum(if(year(finish_time)<>1970,1,0))/count(call_time)as

'完單率'from

didi問題2：呼叫應(yīng)答時(shí)間多長(zhǎng)？--使用timestampdiff函數(shù)，計(jì)算應(yīng)答時(shí)間與呼叫時(shí)間之間的時(shí)長(zhǎng)select

sum(TIMESTAMPDIFF(MINUTE,call_time,grab_time))/count(grab_time)as

'呼叫應(yīng)答時(shí)間'from

didiwhere

year(grab_time)<>1970;問題3：從這一周的數(shù)據(jù)來看呼叫量最高的是哪一個(gè)小時(shí)（當(dāng)?shù)貢r(shí)間）？呼叫量最少的是哪一個(gè)小時(shí)（當(dāng)?shù)貢r(shí)間）？根據(jù)題意，需要新增一列數(shù)據(jù)，訂單呼叫時(shí)間的小時(shí)，進(jìn)行分組分析--新增一列alter

table

didi

add

column

call_time_hour

VARCHAR(255);--使用substr函數(shù)做字符串截取，為新列賦值update

didi

set

call_time_hour=

SUBSTR(call_time

from

12

for

2);—方法2：使用date_format函數(shù)轉(zhuǎn)換格式

update

didi

set

call_time_hour=

DATE_FORMAT(call_time,’%k’)--找出呼叫量最高的小時(shí)，顯示2行防止出現(xiàn)重復(fù)值。select

call_time_hour,count(call_time)as'呼叫量'from

didiGROUP

BY

call_time_hourORDER

BY

count(call_time)

desclimit

2;--找出呼叫量最少的小時(shí)，顯示3行確認(rèn)是否有第三個(gè)相等值。select

call_time_hour,count(call_time)as'呼叫量'from

didiGROUP

BY

call_time_hourORDER

BY

count(call_time)

asclimit

3;得出結(jié)論：呼叫量最高的是18時(shí)，呼叫量最少的是2和3時(shí)。問題4：呼叫訂單第二天繼續(xù)呼叫的比例有多少？select

count(DISTINCT

a.order_id)/(select

count(DISTINCT

order_id)

from

didi)as'第二天繼續(xù)呼叫比例'

from

didia

join

didibon

a.passenger_id=b.passenger_idwhere

datediff(a.call_time,b.call_time)=1；問題5：如果要對(duì)表中乘客進(jìn)行分類，你認(rèn)為需