数据库查询中排名开窗函数.doc

数据库查询中排名开窗函数 　　摘 要 排名开窗函数可以实现确定在应用关联的开窗函数之前，行集的分区和排序。排名开窗函数具有非常广泛的用途 关键词 SQL语句 数据库查询 排名开窗函数 排名开窗函数可以实现确定在应用关联的开窗函数之前，行集的分区和排序。排名开窗函数具有非常广泛的用途。下面以SQL_SERVER 2008中的排名函数为例简要说明 1排名开窗函数的功能 开窗函数是在 ISO SQL 标准中定义的。SQL Server 提供排名开窗函数和聚合开窗函数。窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。可以在单个查询中将多个排名或聚合开窗函数与单个 FROM 子句一起使用。但是，每个函数的 OVER 子句在分区和排序上可能不同。OVER 子句不能与 CHECKSUM 聚合函数结合使用 2语法及参数说明 Ranking Window Functions ：： = OVER （ [ PARTITION BY value_expression ， ... [ n ] ] ） 其中，PARTITION BY 将结果集分为多个分区。开窗函数分别应用于每个分区，并为每个分区重新启动计算。value_expression 指定对相应 FROM 子句生成的行集进行分区所依据的列。value_expression 只能引用通过 FROM 子句可用的列。value_expression 不能引用选择列表中的表达式或别名。value_expression 可以是列表达式、标量子查询、标量函数或用户定义的变量。ORDER BY 子句指定应用排名开窗函数的顺序 3应用实例 3.1将 OVER 子句与 ROW_NUMBER 函数结合使用 每个排名函数（ROW_NUMBER、DENSE_RANK、RANK、NTILE）都使用 OVER 子句。以下示例显示了将 OVER 子句与 ROW_NUMBER 结合使用 SELECT c.FirstName， c.LastName ，ROW_NUMBER（） OVER（ORDER BY SalesYTD DESC） AS ‘Row Number’ ，s.SalesYTD， a.PostalCode FROM Sales.SalesPerson s INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID INNER JOIN Person.Address a ON a.AddressID = c.ContactID WHERE TerritoryID IS NOT NULL AND SalesYTD 0； 3.2将 OVER 子句与聚合函数结合使用 以下示例显示了将 OVER 子句与聚合函数结合使用。在此示例中，使用 OVER 子句比使用子查询的效率高 SELECT SalesOrderID， ProductID， OrderQty ，SUM（OrderQty） OVER（PARTITION BY SalesOrderID） AS ‘Total’ ，AVG（OrderQty） OVER（PARTITION BY SalesOrderID） AS ‘Avg’ ，COUNT（OrderQty） OVER（PARTITION BY SalesOrderID） AS ‘Count’ ，MIN（OrderQty） OVER（PARTITION BY SalesOrderID） AS ‘Min’ ，MAX（OrderQty） OVER（PARTITION BY SalesOrderID） AS ‘Max’ FROM Sales.SalesOrderDetail WHERE SalesOrderID IN（43659，43664）； ?⒖嘉南? [1] 杜佰林.网络数据库SQL Server 2000[M].清华大学出版社，2009（09）. [2] 何薇，舒后.网络数据库技术与应用[M].清华大学出版社，2014（10）. 1