sqlserver开窗函数
从 http://jimshu.blog.51cto.com/3171847/1376637/ 转
开窗函数是在 ISO 标准中定义的。SQL Server 提供排名开窗函数和聚合开窗函数。
在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。SQL Server 2005 引入了开窗函数，使得这些经典的难题可以被轻松的解决。
窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。开窗函数分别应用于每个分区，并为每个分区重新启动计算。
OVER 子句用于确定在应用关联的开窗函数之前，行集的分区和排序。PARTITION BY 将结果集分为多个分区。
一、排名开窗函数
1. 语法
|
Ranking Window Functions < OVER_CLAUSE > :: = OVER ( [ PARTITION BY value_expression , ... [ n ] ] <ORDER BY_Clause> ) |
注意：ORDER BY 子句指定对相应 FROM 子句生成的行集进行分区所依据的列。value_expression 只能引用通过 FROM 子句可用的列。value_expression 不能引用选择列表中的表达式或别名。value_expression 可以是列表达式、标量子查询、标量函数或用户定义的变量。
2. 示例
可参考 http://jimshu.blog.51cto.com/3171847/1176067
二、聚合开窗函数
1. 语法
|
Aggregate Window Functions < OVER_CLAUSE > :: = OVER ( [ PARTITION BY value_expression , ... [ n ] ] ) |
2. 示例
下例将根据 SalesOrderID 进行分区，然后为每个分区分别统计SUM、AVG、COUNT、MIN、MAX。
|
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 SalesOrderDetail WHERE SalesOrderID IN(43659,43664); |
下例首先由 SalesOrderID 分区进行聚合，并为每个 SalesOrderID 的每一行计算 ProductID 的百分比）。
|
SELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total' ,CAST(1.0 * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID) *100 AS DECIMAL(5,2))AS 'Percent by ProductID' FROM SalesOrderDetail WHERE SalesOrderID IN(43659,43664); |
3. SQL Server 2012 增加功能
SQL Server 2012 为聚合函数提供了窗口排序和框架支持，可以将 OVER 子句与函数一起使用，以便计算各种聚合值，例如移动平均值、累积聚合、运行总计或每组结果的前 N 个结果。
更多详情，请参考 http://technet.microsoft.com/zh-cn/library/ms189461(v=sql.110).aspx
三、分析开窗函数
可参考 http://jimshu.blog.51cto.com/3171847/1176191
四、NEXT VALUE FOR 函数
通过将 OVER 子句应用于 NEXT VALUE FOR 调用，NEXT VALUE FOR 函数支持生成排序的序列值。 通过使用 OVER 子句，可以向用户保证返回的值是按照 OVER 子句的 ORDER BY 子子句的顺序生成的。
例如：
|
SELECT NEXT VALUE FOR Test.CountBy1 OVER (ORDER BY LastName) AS ListNumber, FirstName, LastName FROM Person.Contact ; |
详情请参考 http://technet.microsoft.com/zh-cn/library/ff878370.aspx
