关于SQLServer2005的学习笔记――分析函数
发布时间：2020-12-12 15:26:58 所属栏目：MsSql教程 来源：网络整理
导读：SQLServer2005 提供了几个有用的分析函数如 ROW_NUMBER,NTILE,RANK,DENSE_RANK ，从而大大简化了编程 方式，并提供了系统性能。 此外根据测试 MAX,MIN,AVG 之类的统计函数还是可以在分析函数中应用的，不过和 Oracle 的处理有点不一样，好像无法应用 OVER 中
|
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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |