|
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',200);
INSERT INTO Sales VALUES('D','Y',200);
INSERT INTO Sales VALUES('E',250);
INSERT INTO Sales VALUES('F',300);
INSERT INTO Sales VALUES('G',100);
INSERT INTO Sales VALUES('H',150);
INSERT INTO Sales VALUES('I',250);
INSERT INTO Sales VALUES('J',100);
INSERT INTO Sales VALUES('K',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,
??????(SELECT COUNT(*)
?????????FROM Sales S2
????????WHERE S2.Qty<S1.Qty
???????????OR (S2.Qty=S1.Qty AND S2.EmpID<=S1.EmpID)) RowNum
?FROM Sales S1
?ORDER BY Qty,EmpID
-- 等效语句
SELECT MgrID,EmpID,
??????(SELECT COUNT(*)
?????????FROM Sales S2
????????WHERE S2.MgrID=S1.MgrID
??????????AND (S2.Qty<S1.Qty
???????????OR (S2.Qty=S1.Qty AND S2.EmpID<=S1.EmpID))) RowNum
?FROM Sales S1
?ORDER BY MgrID,EmpID
?
--RANK 函数和 DENSE_RANK 函数
SELECT EmpID,
??????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,
??????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分析函数二—— 函数用法
Oracle分析函数三——SUM,AVG,MAX,COUNT
Oracle分析函数四—— 函数RANK,DENSE_RANK,FIRST,LAST…
Oracle分析函数五—— 统计分析函数
Oracle分析函数六—— 数据分布函数及 报表 函数
Oracle分析函数七—— 分析函数案例
Oracle分析函数八——CUBE ，ROLLUP
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
