此外根据测试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是完全一致的，在此不多做叙述
详情参见以下链接：
Oracle分析函数二——函数用法
Oracle分析函数三——SUM,AVG,MIN,MAX,COUNT
Oracle分析函数四——函数RANK,DENSE_RANK,FIRST,LAST…
Oracle分析函数五——统计分析函数
Oracle分析函数六——数据分布函数及报表函数
Oracle分析函数七——分析函数案例
Oracle分析函数八——CUBE，ROLLUP
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/6517/viewspace-625190/ ，如需转载，请注明出处，否则将追究法律责任。
