當我們在用SQL時，有不少情況下我們可以用不同的方式來解決同一個問題。列出每一行的排名就屬於這種情況。我們在這裡用兩種方法來計算排名：第一種是用窗口函數，第二種是不用窗口函數。

用窗口函數計算排名

窗口函數本身就有三種計算排名的方式，要用哪一種我們可以按照實際需求來決定。假設我們有以下的表格：

Total_Sales 表格
 Name   Sales 
 John   10 
 Jennifer   15 
 Stella   20 
 Sophia   40 
 Greg   50 
 Jeff   20 

我們可以用以下的窗口函數來算出每一個人的排名：

SELECT a1.Name, Sales, RANK() over (ORDER BY Sales DESC) RK
FROM Total_Sales
ORDER BY a1.Sales DESC;

結果:

Name Sales RK
Greg  50 1
Sophia  40 2
Stella  20 3
Jeff  20 3
Jennifer  15 5
John  10 6

當我們用RANK()函數時，平手的資料會有一樣的排名。下一筆資料的排名只看有多少筆資料在它前面，而不管前面的資料中有沒有任何平手的狀況。所以在這個例子中，有兩筆資料並列第三，而下一筆資料是排第五。

我們也可以用ROW_NUMBER()函數。在這裡，SQL成為：

SELECT a1.Name, Sales, ROW_NUMBER() over (ORDER BY Sales DESC) RK
FROM Total_Sales
ORDER BY a1.Sales DESC;

結果:

Name Sales RK
Greg 501
Sophia 402
Stella 203
Jeff 204
Jennifer 155
John 106

由於Stella跟Jeff是平手，所以其中一筆資料會被列為第三，而另一筆會被列為第四。如果我們有特定的條件來讓平手的資料分出高低，那我們就需要將那個條件列在窗口函數內的ORDER BY。舉例來說，如果平手時我們要照Name的順序來排名，那SQL就成為：

SELECT a1.Name, Sales, ROW_NUMBER() over (ORDER BY Sales DESC, Name) RK
FROM Total_Sales
ORDER BY a1.Sales DESC;

結果:

Name Sales Sales_Rank
Greg 501
Sophia 402
Jeff 203
Stella 204
Jennifer 155
John< /td>106

不用窗口函數計算排名

計算排名沒有一定要用窗口函數。在不用窗口函數的情況下，要以SQL列出排名的基本概念是要做一個表格自我連結 (Self Join)，將結果依序列出，然後算出每一行之前 (包含那一行本身) 有多少行數。這樣講讀者聽得可能有點困惑，所以最好的方式是用一個實例來介紹。假設我們延續之前用的表格，我們可以打入以下的 SQL 語句：

SELECT a1.Name, a1.Sales, COUNT(a2.Sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

結果:

Name  Sales  Sales_Rank
Greg  50  1
Sophia  40  2
Stella  20  3
Jeff  20  3
Jennifer  15  5
John  10  6

我們先來看 WHERE 子句。在字句的第一部分 (a1.Sales <= a2.Sales)，我們算出有多少筆資料 Sales 欄位的值是比自己本身的值小或是相等。如果在 Sales 欄位中沒有同樣大小的資料，那這部分的 WHERE 子句本身就可以產生出正確的排名。

子句的第二部分，(a1.Sales=a2.Sales AND a1.Name = a2.Name)，則是讓我們在 Sales 欄位中有同樣大小的資料時 (像 Stella 及 Jeff 這兩筆資料)，仍然能夠產生正確的排名。在這裡我們的結果跟用窗口函數的RANK()產生的結果一樣。

下一頁：SQL 中位數

本頁最近於 2022年6月23日更新


Copyright © 2024   1keydata.com   版權所有