轉：SQL SERVER 開窗函數簡介

原帖：http://blog.csdn.net/liu_1983/article/details/7269312 

 

在SQL SERVER 2005/2008支援兩種排名開窗函數和聚集開窗函數。

以SQL SERVER中分面頁為例，按時間順序列出定單號。

WITH OrderInfo AS

(

 SELECT ROW_NUMBER() OVER(ORDER BY OrderDate) AS Number,

 OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)

)

 

SELECT Number,OrderID,CustomerID, EmployeeID ,OrderDate

From OrderInfo WHERE Number BETWEEN 0 AND 10

其中ROW_NUMBER()就是次序函數，OVER（）就是視窗函數。

視窗函數OVER（）指定一組行，開窗Function Compute從視窗函數輸出的結果集中各行的值。

 

開窗函數不需要使用GROUP BY就可以對資料進行分組，還可以同時返回基礎行的列和彙總列。

 

1．排名開窗函數

ROW_NUMBER、DENSE_RANK、RANK、NTILE屬於次序函數。

排名開窗函數可以單獨使用ORDER BY 語句，也可以和PARTITION BY同時使用。

PARTITION BY用於將結果集進行分組，開窗函數應用於每一組。

ODER BY 指定排名開窗函數的順序。在排名開窗函數中必須使用ORDER BY語句。

例如查詢每個僱員的定單，並按時間排序

WITH OrderInfo AS

(

 SELECT ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY OrderDate) AS Number,

 OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)

)

 

SELECT Number,OrderID,CustomerID, EmployeeID ,OrderDate

From OrderInfo WHERE Number BETWEEN 0 AND 10

視窗函數根據PARTITION BY語句按僱員ID對資料行分組，然後按照ORDER BY 語句排序，次序函數ROW_NUMBER()為每一組的資料分從1開始產生一個序號。

 

ROW_NUMBER（）為每一組的行按順序產生一個唯一的序號

RANK（）也為每一組的行產生一個序號，與ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值會產生相同的序號，並且接下來的序號是不連序的。例如兩個相同的行產生序號3，那麼接下來會產生序號5。

DENSE_RANK（）和RANK（）類似，不同的是如果有相同的序號，那麼接下來的序號不會間斷。也就是說如果兩個相同的行產生序號3，那麼接下來產生的序號還是4。

NTILE (integer_expression) 按照指定的數目將資料進行分組，並為每一組產生一個序號。

 

2．彙總開窗函數

很多彙總函式都可以用作視窗函數的運算，如SUM,AVG,MAX,MIN。

彙總開窗函數只能使用PARTITION BY子句或都不帶任何語句，ORDER BY不能與彙總開窗函數一同使用。

例如，查詢僱員的定單總數及定單資訊

WITH OrderInfo AS

(

SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)

)

SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCount

From OrderInfo ORDER BY EmployeeID

 

如果視窗函數不使用PARTITION BY 語句的話，那麼就是不對資料進行分組，彙總函式計算所有的行的值。

WITH OrderInfo AS

(

 SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)

)