朋友說實際工作中，能用on就不用where，因為on可以讓表縮小，where是形成表以后篩選
這里好幾個時間函數(shù)
1.date_sub(cast(call_time as datetime),interval 11 hour)? ?先轉(zhuǎn)換datetime，然后sub減去11小時
2.timestampdiff(minute,call_time_bz,grab_time_bz) 時間差?grab_time_bz-call_time_bz以minute計
3.date_format(a.call_time_bz,'%k') as call_time_hour, -- 學(xué)到了提取小時的方法，不能寫成h 會只有12小時，導(dǎo)致晚上20點也是8點，可以寫 H是24小時的0102到24，寫k就是24小時的123到24沒有前面的0
4.date_format(a.call_time_bz,'%Y-%m-%d')=date_sub(date_format(b.call_time_bz,'%Y-%m-%d'),interval 1 day) 提取年月日? date_add? date_sub(原時間，interval x day)? x minute
5.我用了 a.call_time_bz >='2018-03-05' and a.call_time_bz<'2018-03-12'
和a.call_time_bz between '2018-03-05' and '2018-03-12'在mysql里面是一樣的，大概是左邊取到右邊不取到，我之前a.call_time_bz >='2018-03-05' and a.call_time_bz<='2018-03-11'的時候 3.11 零點以后的就取不到了emm～
滴滴出行數(shù)據(jù)分析師面試經(jīng)驗
一個玩數(shù)據(jù)分析的鴨鴨
已關(guān)注
20 人贊同了該文章
最近投了滴滴國際部的數(shù)據(jù)分析師面試，第一輪線上面試，題目有點復(fù)雜，很容易做錯，不過也是基礎(chǔ)的業(yè)務(wù)SQL題目。
Part. 1 數(shù)據(jù)
ta本來的解法挺好https://zhuanlan.zhihu.com/p/107495784
我也做了一下
因為自己不習(xí)慣alter表，兩個代碼都試了一遍，放一下自己的供記錄探討，因為自己也是很容易出錯的人～大致是理清楚了～很感謝樓主的分享～文章都很棒～
1。對應(yīng)樓主的計算巴西時間，如果不alter表的話可以這樣寫，本質(zhì)是一樣的：
(select *,date_sub(cast(call_time as datetime),interval 11 hour) as call_time_bz,
date_sub(cast(grab_time as datetime),interval 11 hour) as grab_time_bz,
date_sub(cast(cancel_time as datetime),interval 11 hour) as cancel_time_bz,
date_sub(cast(finish_time as datetime),interval 11 hour) as finish_time_bz
from didi2)a
2。計算應(yīng)答率，應(yīng)答率，呼叫應(yīng)答時間
select
b.grab_sum/b.call_sum as '應(yīng)答率',
b.finish_sum/b.call_sum as '完單率',
b.call_grab_time/b.grab_sum as '呼叫應(yīng)答時間'
from
(
select
sum(case when '2018-03-05'<= a.grab_time_bz and a.grab_time_bz<'2018-03-12' then 1 else 0 end) as grab_sum,
sum(case when '2018-03-05'<= a.finish_time_bz and a.finish_time_bz<'2018-03-12' then 1 else 0 end) as finish_sum,
sum(case when '2018-03-05'<= a.call_time_bz and a.call_time_bz<'2018-03-12' then 1 else 0 end) as call_sum,
sum(case when '2018-03-05'<= a.grab_time_bz and a.grab_time_bz<'2018-03-12' then timestampdiff(minute,call_time_bz,grab_time_bz) else 0 end) as call_grab_time -- 在這個時間內(nèi)，就輸出grab和call的差值，以minute計
from
(select *,date_sub(cast(call_time as datetime),interval 11 hour) as call_time_bz,
date_sub(cast(grab_time as datetime),interval 11 hour) as grab_time_bz,
date_sub(cast(cancel_time as datetime),interval 11 hour) as cancel_time_bz,
date_sub(cast(finish_time as datetime),interval 11 hour) as finish_time_bz
from didi2)a
)b
3。select
date_format(a.call_time_bz,'%k') as call_time_hour, -- 學(xué)到了提取小時的方法，不能寫成h 會只有12小時，導(dǎo)致晚上20點也是8點，可以寫 H是24小時的0102到24，寫k就是24小時的123到24沒有前面的0
count(a.order_id) as call_sum
-- sum(case when a.call_time_bz is not null then 1 else 0 end) as call_sum -- 我之前是按call time來算的，感覺orderid和call time差不多
from
(select *,date_sub(cast(call_time as datetime),interval 11 hour) as call_time_bz,
date_sub(cast(grab_time as datetime),interval 11 hour) as grab_time_bz,
date_sub(cast(cancel_time as datetime),interval 11 hour) as cancel_time_bz,
date_sub(cast(finish_time as datetime),interval 11 hour) as finish_time_bz
from didi2)a
group by 1
order by call_sum desc -- 或者asc
limit 0,1
本來想用row_number寫 寫崩了 還是這樣比較好
4。sum(case when b.call_time_bz is not null and b.call_time_bz >='2018-03-05' and b.call_time_bz<'2018-03-12'then 1 else 0 end) /count(a.call_time_bz) ,
-- count(a.call_time_bz)
from
(select *,date_sub(cast(call_time as datetime),interval 11 hour) as call_time_bz,
date_sub(cast(grab_time as datetime),interval 11 hour) as grab_time_bz,
date_sub(cast(cancel_time as datetime),interval 11 hour) as cancel_time_bz,
date_sub(cast(finish_time as datetime),interval 11 hour) as finish_time_bz
from didi2)a
left join a as b
on a.passenger_id=b.passenger_id
and date_format(a.call_time_bz,'%Y-%m-%d')=date_sub(date_format(b.call_time_bz,'%Y-%m-%d'),interval 1 day)
where a.call_time_bz >='2018-03-05' and a.call_time_bz<'2018-03-12'
-- b.call_time_bz >='2018-03-05' and b.call_time_bz<'2018-03-12'
我發(fā)現(xiàn)在我寫的這種情況里，不能在這里加b的這條 因為我用的是left join 和on ，后面計算的時候就需要b是有null的，a是整體的，然后讓b是非空的/a的整體來算得比例，如果b的限制加了以后 ab都變成一樣的數(shù)目，結(jié)果就是100%了，當(dāng)然如果這樣的話，那就類似樓主的算法一樣直接除以count原先的總數(shù)也就可以了，樓主寫的版本還是比較好理解的～
特別感謝鴨鴨，感覺你的思路都很棒啊～～～
今天特別來學(xué)習(xí)了一下，記錄幾個有出入的點。
1。關(guān)于計算應(yīng)答率等的count（*），如果要嚴(yán)謹(jǐn)一些的話
select sum(case when grab_time between '2018-03-05' and '2018-03-12' then 1 else 0 end)/count(*) as 應(yīng)答率,
sum(case when finish_time between '2018-03-05' and '2018-03-12' then 1 else 0 end)/count(*) as 完答率
from didi
這個地方的count(*)里面也可能存在5-12范圍之外的，還是改成count(call time between。。。)比較好～
2.另外between的用法在不同的sql里面是不是取到，是不太一樣的？？？
后來我就用了最保守的>=的時間判斷啦～這個細(xì)節(jié)提的真的不錯～
我用了 a.call_time_bz >='2018-03-05' and a.call_time_bz<'2018-03-12'
和a.call_time_bz between '2018-03-05' and '2018-03-12'在mysql里面是一樣的，大概是左邊取到右邊不取到，我之前a.call_time_bz >='2018-03-05' and a.call_time_bz<='2018-03-11'的時候 3.11 零點以后的就取不到了emm～
3.最后第四題的會產(chǎn)生 對稱 所以 /2 ，這個我自己也算了一遍，我認(rèn)為是不用噠。之前做過類似的題目，也是自連接，然后要除以2，但是那個情況是去除不一樣的，用a<>b 以外就沒有大小關(guān)系了。
這里用了timestampdiff其實算出來就是有正負(fù)的，已經(jīng)限定了間隔就是比如b比a大一天，所以是不用除以2的。
最后想說看樓主寫的很長見識～自己也是有很多地方不夠扎實，也很少用到直接alter 表的做法，以及看到評論區(qū)的解釋date_format()是顯示不同格式的日期。cast是轉(zhuǎn)換格式感覺很好。
如果要對這100個乘客進行分類，你認(rèn)為需要參考哪一些因素？
我們的數(shù)據(jù)提供了完成時間和接單時間，忽略司機到達乘客的接單地點的時間，我們可以計算出乘客在乘車中花了多少時間，對于這個時間可以進行預(yù)判，這是長途、中途還是短途，可以分析乘客的乘車習(xí)慣。
按照我們的發(fā)單時間可以判斷乘客是在時間點發(fā)單的，他的需求是怎么產(chǎn)生的，比如切分成上班、下班、就餐、出游、臨時等場景。
使用RFM模型，這里的數(shù)據(jù)提供了最近一次打車的時間并且可以計算出打車頻率，將用戶切分成重要價值用戶和重要發(fā)展用戶或者中要保持用戶和一般價值用戶。對不用的用戶考慮不用的用戶挽留策略。對于打車頻率高但是近期沒有消費的重要保持用戶，使用郵件推送、APP push的方法、活動短信提醒的方法召回。對于打車頻率不高但是近期有打車的重要發(fā)展用戶，應(yīng)該用會員權(quán)益或者發(fā)放優(yōu)惠券的形式促進消費次數(shù)。
高峰期集中在18點，說明用戶大多喜歡下午因公打車?？梢钥紤]從遠(yuǎn)處進行調(diào)度車輛，看能否起始地和目的地形成互補，不造成空車回去或者過去。
因為知道了取消訂單的量，可以考慮在高峰期間取消訂單的數(shù)量也就是訂單的滿足率，滴滴公司應(yīng)該著重考慮快車調(diào)度問題。
我們可以用sql計算出打車的平均時長，從滴滴數(shù)據(jù)庫中獲得該地區(qū)總車輛，從而得出車輛的需求量。
總結(jié)：
題目存在很多易錯點，因為是真實的業(yè)務(wù)問題，特別是對日期的處理大家要加以注意。
