SqlServer 文法

標籤：use   func   sql   不能   不同的   相互   根據   tom   表示   

四、            Ranking

排序與排名是我們最為常用的統計方式，比如對班級的學生根據成員進行排名，或者按照成績高低把學生劃分成若干梯隊：比如最好成績的10名學生屬於第一梯隊，後10名又劃分為第二梯隊，以此類推。Ranking設計的Key Words包括：ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()。我們現在就來介紹一下他們的用法和相互之間的差異。

1．          1. ROW_NUMBER()

看到ROW_NUMBER(),我想絕大多數人會像想到Oracle的ROWNUM。他們的作用相似，都是表示某條記錄所處的Index。ROW_NUMBER()比Oracle的ROWNUM更加強大的是，它可以通過OVER語句指定一個進行排序的Column，比如：ROW_NUMBER() OVER (ORDER BY CustomerID)。

我們來看一個例子：對Sales.SalesOrderHeader按照CustomerID進行排序，並顯示每條記錄的Row Number。

SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader

下面是查詢結果：

我們發現最終的結果按照CutomerID進行排序，RowNum從1開始以此遞增，每條記錄（不管是否具有相同的CustomerID）擁有不同的RowNum。

提到排序，我們就不得不提到Order BY，如果我們在後面加上ORDER BY，並指定不同的排序欄位，會出現怎樣的結果呢？

SELECT SalesOrderID,CustomerID,ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID

查詢獲得的結果是：

從中可以看到，最終的結果以ORDER BY中指定的SalesOrderID進行排序，但是ROW_NUMBER()體現的值卻是基於CustmerID排序的。

由於ROW_NUMBER()體現是基於某個確定的欄位進行排序後某個DataRow所處的位置，所以它不能直接使用到Aggregate的Column中。比如下面的SQL是不合法的：

SELECT CustomerID,COUNT(*) AS OrderCount,ROW_NUMBER() OVER (ORDER BY OrderCount)
FROM Sales.SalesOrderHeader
GROUP BY CustomerID

要是想按照OrderCount，可以使用第一部分介紹的CTE：

WITH CTE_Order(CustomerID,OrderCount)
AS
(
SELECT CustomerID,COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
)

SELECT CustomerID,OrderCount,ROW_NUMBER() OVER (ORDER BY OrderCount)
FROM CTE_Order

2．          RANK()

RANK()的使用和ROW_NUMBER()類似。不過它與ROW_NUMBER()所不同的是：對於被指定為排序的欄位，具有相同值得Row對應的傳回值相同。比如：

SELECT SalesOrderID,CustomerID,RANK() OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader

下面是相應的查詢結果：

對於RANK()，還有一點需要說明的是，它的回返值不是連續的, 比如第五條記錄的Row_Num是5而不是2。如果想實現這樣需求，就需要用下面一個Function：DENSE_RANK()。

3．          3. DENSE_RANK()

DENSE_RANK()實現了一個連續的Ranking。比如下面的SQL：

SELECT SalesOrderID,CustomerID,DENSE_RANK() OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader

就來產生如下的查詢結果：

4. NTILE()

上面我們說到劃分梯隊的問題，這樣的問題可以通過NTILE() Function來實現。比如我們現在按照CustomerID排序，把CustomerID為1和2的劃分到3梯隊中：

SELECT SalesOrderID,CustomerID,NTILE(3) OVER (ORDER BY CustomerID) AS RowNum
FROM Sales.SalesOrderHeader
WHERE CustomerID <3

其查詢結果為：

我們可以看到，一共12條記錄，劃分為3組，平均下來每組4條記錄。

5．          5.PARTITION　BY

上面提到的所有Ranking都是基於真箇結果基的。而有的時候我們需要將真箇結果集按照某個Column 進行分組，進行基於組的Ranking。這就需要PARTITION　BY了。PARTITION　BY置於OVER Clause中，和ORDER BY 平級。

比如下面的SQL將Order記錄按照CustomerID進行分組，在每組中輸出排名（安OrderDate排序）：

SELECT SalesOrderID,CustomerID,RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RowNum
FROM Sales.SalesOrderHeader

相應的查詢結果：

SqlServer 文法