????????排名問題恐怕無論是公司面試還是日常工作都會經(jīng)常遇到的問題。有很多初學(xué)者雖然學(xué)會了基本的SQL查詢，也練習(xí)了一些SQL題目，但是始終不得SQL的要領(lǐng)。究竟怎么才能深刻掌握如何SQL取數(shù)呢？當(dāng)然是會舉一反三啦！舉一隅不以三隅反，你就是沒學(xué)會~

??????下面通過一個小小的例子來幫助你思考排名問題，希望對你有所幫助。而且我們使用的是公司實際訂單數(shù)據(jù)而不是簡單的幾行數(shù)據(jù)，在接下來的例子中你會感受到幾行數(shù)據(jù)的查詢和幾萬行乃至上百千萬的數(shù)據(jù)是不同的，如果你還停留在做題的思維而沒有實戰(zhàn)工程的思維，那么我可以說，少年你too young too simple啦

問題：怎么快速取出最高的價格？

背景：數(shù)據(jù)來源于微軟示例數(shù)據(jù)庫，一家自行車制造公司的銷售數(shù)據(jù)，分為網(wǎng)絡(luò)銷售FactInternetSales和經(jīng)銷商銷售FactResellerSales兩張表，其中網(wǎng)絡(luò)銷售訂單數(shù)據(jù)60398行，經(jīng)銷商銷售數(shù)據(jù)有60855行。此處我們簡化問題，重點在問題的解決上，因此只用到FactInternetSales表,它包含了價格UnitPrice在內(nèi)的26個字段。

分析：數(shù)據(jù)工作中往往有兩種需求，一種是未知的，這類問題難解決，沒有人告訴你要取什么數(shù)，而是實際問題要求你取什么數(shù)，這需要數(shù)據(jù)分析師獨立思考的能力；另一種是已知的，別人拋過來一個明確的需求，這類問題看似很難其實是很簡單的?？吹玫降木陀修k法分析和解決，看不到的才是最可怕的。這里最重要的關(guān)鍵詞是“價格”，限定條件一個是“最高的”，一個是“快速”，前者意味著我需要排序，后者意味著這個需求的解法不止一種，而我需要取出最好那一種。

???????????初學(xué)者一上來可能就直接使用orderby和limit關(guān)鍵詞，取出來就完了；又或者聰明點的會使用聚合函數(shù)、窗口函數(shù)蹭蹭蹭三下五去二了事兒。但實際上你只知其一不知其二，下次遇到相同問題變形就可能看不懂了。下面我由簡到難介紹四種解法，給大家一個解決問題的思路。

解法一 簡單查詢

? ?????當(dāng)遇到問題，最先想到的解決思路是什么？當(dāng)然是用最簡單的元素組合啦，在SQL中說的是最基本的關(guān)鍵詞，在這道題也就是上面說的orderby和limit關(guān)鍵詞。剛才我雖然批評這種解法并不是說它不能用，恰恰相反它是最應(yīng)該優(yōu)先使用的。為什么？黑貓白貓，抓住老鼠就是好貓。我們的目的是解決問題，這是根本所在，而找到最好的辦法是放在第二位去考慮的。所以用這種方法取出預(yù)期的結(jié)果。

select distinct unitprice?

from factinternetsales?

order by unitprice desc?

limit1;

? ?????解析一下這個答案：select關(guān)鍵字永遠是寫在第一個的，它后面跟的是我們要取的列，這里我們只需要取出“價格";取的數(shù)據(jù)是從哪里??？后面緊跟著的就是from+表名；order by 對數(shù)據(jù)進行排序，為什么要用desc呢？為什么不使用asc,然后取最后一條數(shù)據(jù)呢？這個思路是沒毛病的，但是無論是人還是機器，我們在一個時間點只能處理一種執(zhí)行順序。打個比方，你站在桌子的這一頭，需要從桌子上編有序號的一排水杯里取序號最大的那一個，現(xiàn)在你有個按鈕可以讓它們按順序排好，你可以選擇從大到小也可以選擇從小到大排。但是你肯定只會決定讓最大的那個號離你最近，而不是選擇離你最遠吧。你站在那里不動就可以使用最省力的方式取到水杯，肯定不會傻到走到對面去取。數(shù)據(jù)庫也一樣，它每一次執(zhí)行都只會從頭開始，而不會從尾部開始，如果你從小到大排，機器想要取出最后一個就只能走到最后去取，顯然浪費了時間和空間。這個思維看似無用，也容易被人忽略，但它是后面比較難以理解的SQL自連接問題的基本思維；最后就是通過limit關(guān)鍵字限定1個來取出最高。整體思路很簡單，只要是會寫基本的SQL語句，知道SQL的書寫順序和執(zhí)行順序，大都可以寫出。

????????最后還需要注意的點是結(jié)果集去重。在實際業(yè)務(wù)中往往會重復(fù)，我們只需要取一個，因此要用distinct關(guān)鍵字去重。在后面的所有的解法最后都會取出所有重復(fù)結(jié)果，也都需要去重的步驟。

解法二 聚合函數(shù)與窗口函數(shù)

?SQL里除了有最基本的關(guān)鍵字之外還有許多可以提高效率的內(nèi)置函數(shù)，對于對最大最小問題恰好有相應(yīng)的函數(shù)可以使用。

#max

select

max(unitprice)

from

factinternetsales;

#first_value

select

distinct

first_value(unitprice) over()

from factinternetsales;

? ?????這個解法有兩種，前者是用聚合函數(shù)，后者用窗口函數(shù)，在SQL中對排名問題恰好有max和min函數(shù)，還有很多其他業(yè)務(wù)問題都是沒有對應(yīng)的函數(shù)直接解決的；而窗口函數(shù)使用的范圍就廣一些。使用了max函數(shù)就可以不用寫orderby和limit，因為它內(nèi)部就已經(jīng)做了相關(guān)處理。first_value關(guān)鍵字從字面意思來看就是取第一個值，它與max函數(shù)有兩個不同，一個是無論想取出最大值還是最小值，只需要按相應(yīng)的排序取第一個值就可以，也就是說一個關(guān)鍵字可以當(dāng)兩個使用，簡單易用，而max函數(shù)要取最小值還得換成min函數(shù)；第二就是first_value是比max函數(shù)封裝的更復(fù)雜的函數(shù)，從上面看到max函數(shù)實際上是對orderby和limit功能的封裝，而first_value還考慮了將分組情況進行封裝，相當(dāng)于groupby之后再取max/min。在這里是一個簡單的全局排序，表面看沒有分組，其實全局就是一個巨大的組，所以我們也可以使用first_value。至于什么是窗口函數(shù)，我稍后會詳細解釋。這里簡單解釋一下就是：first_value(unitprice)表示取unitprice這一列的第一個值，over()表示窗口的大小，括號里沒有內(nèi)容默認(rèn)是全局為一個窗口，并且窗口里默認(rèn)排序方式為desc。這樣一看，是不是窗口函數(shù)更加簡潔？

解法三?用戶自定義變量

? ?????為什么要使用用戶自定義變量呢？因為對于這道排名問題，既然是排名，為什么不給每個結(jié)果編個號，排在第一的不就是最高的嗎？就像我們的成績表，在成績后面加上一列表示排名，那么誰是最高的就一目了然了。這加上的一列輔助列沒有在數(shù)據(jù)里，該怎么辦呢？這時候就需要用到用戶自定義變量，是mysql里的臨時變量，僅在SQL語句中生效。

第一步：先增加排名輔助列

select unitprice,if(@u = unitprice,@r:=@r,@r:=@r+1), @u := unitprice

from factinternetsales f1,(select@u:=0,@r:=0) init

order by unitprice desc;

? ?????這里的關(guān)鍵是(select @u:=0,@r:=0) init這張臨時表，說是一張表，其實就是一行數(shù)據(jù)，但是它和前面的f1表進行了笛卡爾積連接，這就組成了一張新表，在原來f1表每一行記錄后面都增加了一個列值就是@u:=0和@r:=0，這樣就形成了新的兩個輔助列的原型。如果在select后面接的不是if語句而是直接接的是@u:=0和@r:=0這兩個臨時變量，那么它們各自這一列的值都是一樣的都是0，但是我們使用臨時變量的目的就是讓它發(fā)生變化，相同的值相同的排名，不同的值增加排名。所以我們用if語句作出判斷，當(dāng)然需要先對價格進行降序排列，再進行一行一行的判斷，從哪里開始判斷呢，這就是解法一中說的SQL掃描方式，從第一行開始一行一行掃描直到所有數(shù)據(jù)遍歷。第一行先取出最高的價格unitprice，if語句判斷如果自定義變量@u = unitprice，則@r:=@r，在這里兩個變量的分別代表著價格和排名，如果新列的價格@u等于價格列的價格，說明排名相同，如果不是@r就加1表示排名增加，這樣就達到排名效果。需要解釋的是:=表示的是賦值而不是等于號，可以給變量臨時指定值。需要解釋的是第三個@u := unitprice輔助列不能丟掉，如果丟掉就沒有價格比較對象。

第二步：條件過濾最高

select distinct unitprice

from (selectunitprice,if(@u = unitprice, @r:=@r, @r:=@r +1) rk,? @u:=unitprice

from factinternetsales f1, (select@u:=0, @r:=0) init? ? ??

order by unitprice desc) a

where rk =1;

? ?????經(jīng)過第一步，我們得到價格、排名、臨時價格三個列，但是我們最后的結(jié)果只需要價格，那怎么辦？只好將上一步得到的結(jié)果集作為子查詢，再限定排名@r=1就可以得到預(yù)期結(jié)果了。

解法四? 自連接

? ?????除了增加輔助列，還可以怎么辦呢？還有一個思路就是既然取最高，那么數(shù)據(jù)里只有0個比他大；如果取第二，那么數(shù)據(jù)里只有1個比他大，以此類推，最小的那個數(shù)有n-1個比它大。那我們?yōu)槭裁床粚⑦@張表和它自身做一個關(guān)聯(lián)。我們知道笛卡爾積是將兩張表進行排列組合，A表的第一行會對應(yīng)B表中的每一行，組成的新表行數(shù)為nA*nB。但是進一步，將篩選條件改為對A表的每一行都只取出B表中大于A的值，就會出現(xiàn)A表中最大值對應(yīng)0個B表中的值，A表中第二大值對應(yīng)1個B表中的值，以此類推達到我們想要的效果；最后取1個就得到最終結(jié)果。

select distinct unitprice from t f1

where1= (select count(*)?

????????????????from t f2?

????????????????where f2.unitprice >= f1.unitprice);

? ?????但是正如上面解釋的，對于幾行數(shù)據(jù)用笛卡爾積可以很快解決，但是對于上萬行數(shù)據(jù)求笛卡爾積就會取數(shù)失敗。原因就是取出的結(jié)果集數(shù)據(jù)量太大。所以這里實際工程采用公共臨時表作為中繼，產(chǎn)生一個數(shù)據(jù)量遠小于原表但結(jié)果一樣的數(shù)據(jù)集，這樣就很快能提取結(jié)果。

with t as

(select distinct unitprice

from factinternetsales)

select distinct unitprice from t f1

where1= (select count(*)?

fromt f2 where f2.unitprice >= f1.unitprice);

? ?????以上的解題思路，大家可以看到，其實都是一步一步拆分小問題得到的，在實際工作中總是會遇到各種各樣的小問題，不是一個簡簡單單的答案能夠幫你解決的。你從書本中學(xué)到的叫知識，只有把它運用出來才叫經(jīng)驗，知識是無用的，經(jīng)驗才是寶貴的；當(dāng)你能夠把你的經(jīng)驗舉一反三解決同一類問題，這叫能力；更進一步地，如果你學(xué)會了這種解決問題的思維，能夠無師自通的解決另一類問題，那就是潛力。潛力無限，機會才會無窮！

??????最后歡迎大家關(guān)注我，我是拾陸，搜索公眾號“二八Data”，更多技術(shù)干貨持續(xù)奉獻。