关于SQL Server 2005的学习笔记—分析函数

SQLServer2005 提供了几个有用的分析函数如 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 是完全一致的，在此不多做叙述