這是很早之前面的，第一次面數(shù)據(jù)分析的面試磨澡，當時還傻乎乎的以為數(shù)據(jù)分析和數(shù)據(jù)挖掘是一回事呢。結(jié)果才發(fā)現(xiàn)质和，數(shù)據(jù)分析崗位大多注重的是數(shù)據(jù)庫的能力稳摄，比如sql語句的考察，hive的考察饲宿，以及一些運營思維的考察厦酬，所以第一次面試就很悲劇啦，不過題目還是很有代表性的瘫想。其他的不寫了仗阅，這里只分享一個關于sql的題目。
1国夜、問題引出
現(xiàn)在有兩個數(shù)據(jù)表减噪，一個數(shù)據(jù)表記錄司機的信息，比如司機id车吹，司機姓名旋廷，司機注冊時間等等，一個數(shù)據(jù)表記錄一天的訂單情況礼搁，比如訂單ID饶碘，訂單司機id，訂單時間馒吴。寫sql語句扎运，返回每個司機今天最早的一筆訂單。兩個數(shù)據(jù)表如下圖所示：
2饮戳、錯誤思路
好了豪治，模擬的數(shù)據(jù)我們準備完畢了，接下來我們就要開動腦筋解決這個問題了扯罐，想了半天负拟，腦子里蹦出這么一個想法，這不很簡單么歹河，我們先把兩個表鏈接起來掩浙，然后按照用戶進行一個分組，然后對數(shù)據(jù)排序秸歧，最后輸出第一個記錄不就好了厨姚，所以，我們寫了如下的sql語句：
select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid order by orderinfo.ordertime limit 1
信心滿滿地運行键菱，發(fā)現(xiàn) 報錯了谬墙！
這個only_full_group_by是什么鬼喲。百度了一下，這好像是mysql5.7版本的新特性拭抬，按照網(wǎng)上的方法部默，執(zhí)行如下的sql語句就可以取消這個模式：
SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
再次執(zhí)行我們上面的sql語句，就當我們以為大功告成之時造虎，沒想到結(jié)果只輸出了一條傅蹂，而不是所有的用戶的每一條記錄：
我們來探究一下原因，是什么出現(xiàn)了問題累奈，我們一步步分析贬派，首先我們執(zhí)行表鏈接語句：
select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid
結(jié)果正確急但，輸出如下：
隨后我們加入group by 語句：
select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid
在groupby語句的時候澎媒，已經(jīng)是返回每個用戶的一條記錄了：
剩下的兩不就不用解釋了，order by將上面返回的三條記錄進行一個排序波桩，最后limit 1返回了一條結(jié)果戒努。所以我們找到了問題所在，就是這個group by的問題镐躲，它只能返回每一組的一行储玫。
你可能會想，既然groupby只能返回一行萤皂，我們返回min(ordertime)不就好了：
select name,sex,register,min(ordertime) from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid
結(jié)果輸出為：
哇撒穷，結(jié)果是對的哎。結(jié)果真是對的么裆熙？如果我們在orderinfo里面加入了新的一列端礼，乘客姓名，將orderinfo表變?yōu)槿缦碌男问剑?/p>
實在沒有多余的腦細胞去想復雜的名字了入录，不過這已經(jīng)足以讓我們來解決問題了蛤奥。繼續(xù)運行上面的代碼,結(jié)果如下：
結(jié)果并不對，張三的第一個用戶應該是二號僚稿，細心的你可能已經(jīng)發(fā)現(xiàn)問題了凡桥，還是group by的問題，它返回的是鏈接之后分組的第一條記錄蚀同，min(ordertime)相當于是不在表中的一個新加入的字段缅刽，它的值通過min函數(shù)計算而來，所以會出現(xiàn)上面的結(jié)果蠢络。
3拷恨、看似正確的思路
那么解決這個問題的正確姿勢是什么呢？在融360面試的時候谢肾，我被問到了類似的問題腕侄，吸取在滴滴面試的教訓，我們用一個子查詢來解決這個問題：
select name,sex,register,ordertime,orderuser from userinfo,orderinfo where userinfo.id = orderinfo.userid and orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid)
結(jié)果輸出如我們所預料：
可以看到，子查詢返回的每個用戶的當天最早的訂單時間冕杠，然后外層查詢用一個in微姊，返回訂單時間在最早訂單時間列表里的記錄。
當然分预，你可能會說兢交，有的司機可能沒有訂單，但我們也想要返回這個司機的信息笼痹，比如我在userinfo表里添加一條龍六的信息配喳，這時候，用如上的語句就不行了凳干，因為上面相當于內(nèi)鏈接晴裹，我們這時候要考慮左外鏈接，語句變?yōu)椋?/p>
select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid)
結(jié)果輸出如下：
咦救赐，結(jié)果輸出好像跟我們之前沒有什么差別涧团？這是為什么呢？細心的你可能發(fā)現(xiàn)了经磅，我們用了where子句限定了ordertime的取值范圍泌绣，所以不會出現(xiàn)那些沒有訂單信息的用戶，所以我們還要對語句作如下修改预厌，讓ordertime可以為Null值：
select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid) or orderinfo.ordertime is Null
結(jié)果正確輸出：
4阿迈、再思考
就當我信心滿滿以為這樣就結(jié)束并把帖子發(fā)出去的時候，群里的專家提出了疑問轧叽，如果有不同司機在同一時間接了訂單怎么辦苗沧？記錄可能如下：
這時候我們就會發(fā)現(xiàn)，運行上面的代碼犹芹，某些用戶會返回兩條數(shù)據(jù)崎页，因為兩個用戶在同一時間接到了訂單，對于其中一個用戶來說腰埂，是當天的第一筆飒焦，對于另一個用戶來說，不是當天的第一筆屿笼，這樣就會出現(xiàn)問題了：
5牺荠、真正的正確思路
5.1 三表鏈接
大佬提供了兩種解決方案，一種是把子查詢的結(jié)果作為一個新表驴一，然后利用三表鏈接：
select name,sex,register,ordertime,orderuser from userinfo,orderinfo,(select userid,min(ordertime) as ordertime2 from orderinfo group by userid) as orderinfo2 where userinfo.id = orderinfo.userid and orderinfo.ordertime = orderinfo2.ordertime2 and orderinfo.userid = orderinfo2.userid
結(jié)果如下：
5.2 使用over函數(shù)(mysql不支持)
上面的結(jié)果是正確的休雌，不過太麻煩了吧，于是我們還有第二種解決方案肝断，使用row_number()/rank()/dense_rank() over(partition by)杈曲，這個在mysql中并沒有實現(xiàn)驰凛，在oracle或者sql server中是有實現(xiàn)的。
不過我們還是要來看一下這一語法的基本用法：
over()函數(shù)：
over不能單獨使用担扑，要和分析函數(shù)：rank(),dense_rank(),row_number()等一起使用恰响。
其參數(shù)：over（partition by columnname1 order by columnname2）
含義：按columname1指定的字段進行分組,并按照
例如：employees表中，有兩個部門的記錄：department_id ＝10和20
select department_id涌献，rank（） over（partition by department_id order by salary) from employees就是指在部門10中進行薪水的排名胚宦，在部門20中進行薪水排名。
row_number()函數(shù)
row_number()函數(shù)用于返回根據(jù)over函數(shù)分組排序結(jié)果的編號燕垃。例如row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據(jù)COL1分組枢劝，在分組內(nèi)部根據(jù) COL2排序，而此函數(shù)計算的值就表示每組內(nèi)部排序后的順序編號（組內(nèi)連續(xù)的唯一的)
比如下面的例子中卜壕，我們按照部門進行分組您旁，然后按照薪水進行降序排序，最后一列表示排序后的組內(nèi)排名印叁。
row_number()在我們這道題目的背景下是適用的被冒，不過在其他的場景军掂，比如按照每個部分進行分組轮蜕，再按照工人的薪資進行降序排序，如果有兩個人的薪資相同蝗锥，這兩個人的row_number值不會相同跃洛，這種情況下row_number()函數(shù)就不再適用，我們可以考慮rank()或者dense_rank()函數(shù)與over函數(shù)結(jié)合使用终议。
不過汇竭，這也引出了row_number()函數(shù)另一個比較有趣的作用，根據(jù)某幾列進行去重：假設表TAB中有a,b,c三列穴张，可以使用下列語句刪除a,b,c都相同的重復行细燎。
DELETE FROM (select year,QUARTER,RESULTS,row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO FROM SALE ) WHERE ROW_NO>1
rank()函數(shù)
rank() over()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內(nèi)）
dense_rank()函數(shù)
dense_rank() over()是連續(xù)排序皂甘，有兩個第二名時仍然跟著第三名玻驻。相比之下row_number是沒有重復值的 ．
好了，回到我們的題目偿枕，在oracle等其他數(shù)據(jù)庫中璧瞬，我們可以用下面的語法進行解決，但很遺憾渐夸，mysql不行(下面的語句沒有真正測試過嗤锉，因為在mysql環(huán)境中無法執(zhí)行，如果有錯誤墓塌，歡迎大家指正Ｎ脸馈）
select name,sex,register,ordertime,orderuser from userinfo,（select *,row_number() over(PARTITION by userid order by ordertime) as tn from orderinfo) as t1 where userinfo.id = t1.userid and t1.tn = 1
上面語句中的row_number()完全可以換做rank()或者dense_rank()奥额。同時，使用上面這種語法访诱，不僅僅是最早的一筆訂單披坏，最早的5筆，10筆都可以計算出盐数，功能十分強大棒拂。
5.3 mysql模擬實現(xiàn)rank_over
mysql沒有row_number()/rank()/dense_rank() over(partition by)這樣高級的sql語法，不過我們可以通過編程的方式來模擬實現(xiàn)類似的功能玫氢，下面給出了具體的代碼：
select u.name,u.sex,u.register,o.ordertime,o.orderuser from userinfo as u,(select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b order by orderinfo.userid,orderinfo.ordertime) as o where u.id = o.userid and o.rank = 1
輸出結(jié)果正確如下：
上面的代碼中用到了mysql變量的知識帚屉，首先，我們大體講一下mysql中變量的相關知識漾峡。
mysql變量
mysql中變量不用事前申明毅人，在用的時候直接用“@變量名”使用就可以了。
第一種用法：set @num=1; 或set @num:=1; //這里要使用變量來保存數(shù)據(jù)住册，直接使用@num變量
第二種用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……
注意上面兩種賦值符號趁矾，使用set時可以用“=”或“：=”，但是使用select時必須用“：=賦值”
使用變量添加行號
我們可以設置一個初始行號槽袄，接下來在 select語句中不斷改變行號的值即可：
set @i = 0;
select (@i:=@i+1) as i,userinfo.* from userinfo
如果使用一句話烙无，我們可以將設置初始值的過程放在from后面：
select (@i:=@i+1) as i,userinfo.* from userinfo,(select @i:=0) as it
運行效果如下：
上面的效果得以實現(xiàn)，得益于mysql中變量在select被循環(huán)賦值的特性遍尺，即每取出一行截酷，i的值都會變化一次，而在sql server中乾戏，i不會被循環(huán)賦值迂苛，所有列的值都是最后一次的i值。
if語句
mysql中if語句的語法如下：
IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)鼓择，則 IF()的返回值為expr2; 否則返回值則為 expr3三幻。IF() 的返回值為數(shù)字值或字符串值，具體情況視其所在語境而定呐能。
比如下面的例子,我們根據(jù)司機的注冊時間劃分司機類型：
select name,if(register > '2017-08-05','A','B') as type from userinfo
輸出結(jié)果為：
有了上面的知識儲備之后念搬，我們之前實現(xiàn)的mysql語句也就不難理解了，我們首先在內(nèi)部生成了一個新表o催跪，新表o對司機進行了分組锁蠕，并按照接單時間先后進行了排序：
select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b
可以看到，內(nèi)部查詢的輸出如下懊蒸，接下來就是簡單的兩表鏈接和篩選了：
6荣倾、總結(jié)
學習是一個不斷循環(huán)迭代的過程，這道題從最初的在滴滴面試中幼稚的group by 想法骑丸，到融360面試時自認為正確的解法舌仍，再到被大佬質(zhì)疑從而繼續(xù)思考妒貌，最終到基本get到解題的正確姿勢，經(jīng)歷了一系列迭代的過程铸豁。
希望我們在以后的學習過程中灌曙，能夠不斷的舉一反三，將知識一步步的掌握扎實节芥。
參考文章：
ROW_NUMBER() OVER()函數(shù)用法：http://www.cnblogs.com/alsf/p/6344197.html
mysql實現(xiàn)oracle分析函數(shù)功能 over：http://blog.csdn.net/mengtianyalll/article/details/45767603
MySql 申明變量以及賦值：
http://www.cnblogs.com/qixuejia/archive/2010/12/21/1913203.html
mysql數(shù)據(jù)庫 實現(xiàn)類似標記序號的偽列：
http://blog.csdn.net/ystyaoshengting/article/details/6904627
MySQL的if在刺，case語句使用總結(jié)：
http://outofmemory.cn/code-snippet/1149/MySQL-if-case-statement-usage-summary
