Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONESQLServer2005提供了幾個有用的分析函式如ROW_NUMBER,NTILE,RANK,DENSE_RANK，從而大大簡化了程式設計方式，並提供了系統效能。

此外根據測試MAX,MIN,AVG之類的統計函式還是可以在分析函式中應用的，不過和Oracle的處理有點不一樣，好像無法應用OVER中的ORDER BY 子句，大概是不夠成熟導致的。

SQLServer2005提供的函式也遠不如Oracle10G來的豐富，類似STDDEV之類的統計分析函式僅在MDX中提供；不過其他一些資料分佈的分析函式可以採用變通的方式予以實現。

經過測試，以下程式碼可以在SQLServer2005和Oracle10g中同時執行，結果順序有少許不一致。

--建立相關測試表

CREATE TABLE Sales

(

EmpID VARCHAR(10) NOT NULL PRIMARY KEY,

MgrID VARCHAR(10) NOT NULL,

Qty INT NOT NULL

);

INSERT INTO Sales VALUES('A','Z',300);

INSERT INTO Sales VALUES('B','X',100);

INSERT INTO Sales VALUES('C','X',200);

INSERT INTO Sales VALUES('D','Y',200);

INSERT INTO Sales VALUES('E','Z',250);

INSERT INTO Sales VALUES('F','Z',300);

INSERT INTO Sales VALUES('G','X',100);

INSERT INTO Sales VALUES('H','Y',150);

INSERT INTO Sales VALUES('I','X',250);

INSERT INTO Sales VALUES('J','Z',100);

INSERT INTO Sales VALUES('K','Y',250);

--ROW_NUMBER函式

SELECT EmpID,MgrID,Qty,

ROW_NUMBER() OVER(ORDER BY Qty) AS RowNum_BY_Qty,

ROW_NUMBER() OVER(ORDER BY Qty,EmpID) AS RowNum_BY_EmpIDQty,

ROW_NUMBER() OVER(PARTITION BY MgrID ORDER BY Qty,EmpID) AS RowNum_BY_MgrID_EmpIDQty

FROM Sales

ORDER BY Qty

--等效語句

SELECT EmpID,Qty,

(SELECT COUNT(*)

FROM Sales S2

WHERE S2.Qty

OR (S2.Qty=S1.Qty AND S2.EmpID<=S1.EmpID)) RowNum

FROM Sales S1

ORDER BY Qty,EmpID

--等效語句

SELECT MgrID,EmpID,Qty,

(SELECT COUNT(*)

FROM Sales S2

WHERE S2.MgrID=S1.MgrID

AND (S2.Qty

OR (S2.Qty=S1.Qty AND S2.EmpID<=S1.EmpID))) RowNum

FROM Sales S1

ORDER BY MgrID,Qty,EmpID

--RANK函式和DENSE_RANK函式

SELECT EmpID,MgrID,Qty,

RANK() OVER(ORDER BY Qty) AS Rank,

DENSE_RANK() OVER(ORDER BY Qty) AS Dense_Rank,

RANK() OVER(PARTITION BY MgrID ORDER BY Qty) AS Rank_Partition,

DENSE_RANK() OVER(PARTITION BY MgrID ORDER BY Qty) AS Dense_Rank_Partition

FROM Sales

ORDER BY Qty

--NTILE函式

SELECT EmpID,MgrID,Qty,

NTILE(3) OVER(ORDER BY Qty) AS Ntile_BY_Qty,

NTILE(3) OVER(ORDER BY Qty,EmpID) AS Ntile_BY_EmpIDQty,

NTILE(3) OVER(PARTITION BY MgrID ORDER BY Qty,EmpID) AS Ntile_BY_MgrID_EmpIDQty

FROM Sales

ORDER BY MgrID,Qty

關於這些函式的實現原理和Oracle是完全一致的，在此不多做敘述

關於SQLServer2005的學習筆記——分析函式

Oracle分析函式二——函式用法

Oracle分析函式三——SUM,AVG,MIN,MAX,COUNT

Oracle分析函式四——函式RANK,DENSE_RANK,FIRST,LAST…

Oracle分析函式五——統計分析函式

Oracle分析函式六——資料分佈函式及報表函式

Oracle分析函式七——分析函式案例

Oracle分析函式八——CUBE，ROLLUP

相關文章