何谓SQL窗口函数
窗口函数主要应用于由Over子句定义的行集的函数，在应用关联的开窗函数前确定行集的分区和排序,OVER 子句定义查询结果集内的窗口或用户指定的行 集。 然后，开窗函数将计算窗口中每一行的值。 可以将 OVER 子句与函数一起使用，以便计算各种聚合值，例如移动平均值、累积聚合、运行总计或每 组结果的前N个结果。
窗口函数主要用于分析作用，对于窗口函数的主要依据标准SQL中的window概念，在这个概念下，允许针对集合，窗口，行集执行各种的计算并放回一个结果 通过窗口函数有助于解决各种查询任务通过更简单，直观，有效的数据集。
对于窗口函数的使用，目前主流的关系数据库都有支持，针对Hive Sql也支持使用窗口函数。为简单期间，使用sqlserver进行对窗口函数的使用说明
数据的准备
针对在Sqlserver上使用窗口函数，使用的测试数据为AdventureWorks，相关的表结构信息：
表结构信息
http://jeremykdev.github.io/SqlServerDatabaseDocumentationGenerator/AdventureWorks-sample.html
AdventureWorks数据库Github地址
https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
窗口函数语法
- OVER 语法
OVER ( [ ] [ ] [ ] )
- PARTITION BY 子句
::= PARTITION BY value_expression , ... [ n ] PARTITION BY 将查询结果集分为多个分区。 开窗函数分别应用于每个分区，并为每个分区重新启动计算。 value_expression 指定行集按其分区的列。 value_expression 只能引用可供 FROM 子句使用的列。 value_expression 不能引用选择列表中的表达式或别名。 value_expression 可以是列表达式、标量子查询、标量函数或用户定义的变量。
- ORDER BY 子句
::= ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ] 定义结果集的每个分区中行的逻辑顺序。 也就是说，它指定按其执行开窗函数计算的逻辑顺序 order_by_expression 指定用于进行排序的列或表达式。 order_by_expression 只能引用可供 FROM 子句使用的列。 不能将整数指定为表示列名或别名
- ROWS | RANGE 子句 , 也叫做Window子句
通过指定分区中的起点和终点，进一步限制分区中的行数。 这是通过按照逻辑关联或物理关联对当前行指定某一范围的行实现的。 物理关联通过使用 ROWS 子句实现 基于 ORDER BY 子句中的顺序对之前和之后的行进行定义。 UNBOUNDED PRECEDING ： 指定窗口在分区中的第一行开始。 UNBOUNDED PRECEDING 只能指定为窗口起点。 PRECEDING 使用 指示要置于当前行之前的行或值的数目。 CURRENT ROW ： 在与 ROWS 一起使用时指定窗口在当前行开始或结束。 UNBOUNDED FOLLOWING： 指定窗口在分区的最后一行结束。 UNBOUNDED FOLLOWING 只能指定为窗口终点。如，RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 定义以当前行开始、以分区的最后一行结束的窗口。 FOLLOWING 使用 指示要置于当前行之后的行或值的数目 PRECEDING：往前 FOLLOWING：往后 CURRENT ROW：当前行 UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点
使用窗口函数
- 聚合函数
聚合函数对一组值执行计算，并返回单个值。除了 COUNT 外，聚合函数都会忽略 Null 值 ，所有聚合函数均为确定性函数。 换言之，每次使用一组特定的输入值调用聚合函数时，它们所返回的值都是 相同的。 OVER子句可以跟在除 GROUPING 或 GROUPING_ID 函数以外的所有聚合函数的后面。
- 只是由PARTITION BY指定了分区列 SalesOrderID。后在制定的分区中使用聚合函数。结果中对应SalesOrderID都有汇总值，且都是相同的。
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);
结果
- 指定分区和排序列,分别按分区,年升序 对计算窗口平均值和汇总
SELECT BusinessEntityID, TerritoryID ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD ,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS MovingAvg ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS CumulativeTotal FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY TerritoryID,SalesYear;
计算结果
- 使用ROWS制定分区中的数据行数,其中包括两行，当前行和其的后一行
SELECT BusinessEntityID, TerritoryID ,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5;
计算结果
- 级别函数 主要有4个函数：RANK ， NTILE ， DENSE_RANK ， ROW_NUMBER
RANK():返回结果集的分区内每行的排名，行的排名是相关行之前的排名加1，如果两个或多个行与一个排名关联，则每个关联行得到相同的排名
NTILE():将有序分区中的行分发到制定数目的组中，各个组的编号，从1开始，对于每一行，将返回所有行所属组的编号。
DENSE_RANK():此行数放回结果集分区中每行的排名，排名值没有间断，特定行的排名等于该特定行之前不同排名值得数量之一。
ROW_NUMBER():对结果集输出进行编号，返回结果集分区内行的序列号，每个分区的第一行从1开始。
排名函数为分区中的每一行返回一个排名值，根据所用函数不同，某些行可能与其他行接受到相同的值
SELECT p.FirstName, p.LastName ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number" ,RANK() OVER (ORDER BY a.PostalCode) AS Rank ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank" ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile ,s.SalesYTD ,a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
计算结果
- 分析函数 分析函数基于一组行计算聚合值，与聚合函数不同，分析函数可能针对每组返回多个行，可以使用分析行数来计算移动平均值，运行总计，百分比或一个 组内的前n个结果。
CUST_DIST : 此函数会计算某个值在某个值组内的累积分布。 换言之，CUME_DIST 计算某指定值在一组值中的相对位置
SELECT Department, LastName, Rate, CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist, PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank FROM HumanResources.vEmployeeDepartmentHistory AS edh INNER JOIN HumanResources.EmployeePayHistory AS e ON e.BusinessEntityID = edh.BusinessEntityID WHERE Department IN (N'Information Services',N'Document Control') ORDER BY Department, Rate DESC;
计算结果
FIRST_VALUE: 返回有序值集中的第一个值
SELECT Name, ListPrice, FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive FROM Production.Product WHERE ProductSubcategoryID = 37;
计算结果
LAG:访问相同结果集中先前行的数据,而不需要使用自连接，LAG 以当前行之前的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与先前行中的值进行比较。
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');
计算结果
LEAD ：访问相同结果集的后续行中的数据
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');
PERCENTILE_CONT:基于列值的连续分布计算百分位数。 将内插结果，且结果可能不等于列中的任何特定值。
SELECT DISTINCT Name AS DepartmentName ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ph.Rate) OVER (PARTITION BY Name) AS MedianCont ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ph.Rate) OVER (PARTITION BY Name) AS MedianDisc FROM HumanResources.Department AS d INNER JOIN HumanResources.EmployeeDepartmentHistory AS dh ON dh.DepartmentID = d.DepartmentID INNER JOIN HumanResources.EmployeePayHistory AS ph ON ph.BusinessEntityID = dh.BusinessEntityID WHERE dh.EndDate IS NULL;
计算结果
PERCENT_RANK:计算中一组行内某行的相对排名。 使用 PERCENT_RANK 计算一个值在查询结果集或分区中的相对位置。 PERCENT_RANK 类似于 CUME_DIST 函数。
SELECT Department, LastName, Rate, CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist, PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank FROM HumanResources.vEmployeeDepartmentHistory AS edh INNER JOIN HumanResources.EmployeePayHistory AS e ON e.BusinessEntityID = edh.BusinessEntityID WHERE Department IN (N'Information Services',N'Document Control') ORDER BY Department, Rate DESC;
计算结果
- GROUPING和GROUPING_ID
GROUPING: 指示是否聚合 GROUP BY 列表中的指定列表达式。 在结果集中，如果 GROUPING 返回 1 则指示聚合；返回 0 则指示不聚合。 如果指定了 GROUP BY，则 GROUPING 只能用在 SELECT 列表、HAVING 和 ORDER BY 子句中
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping' FROM Sales.SalesPerson GROUP BY SalesQuota WITH ROLLUP;
计算结果
GROUPING_ID： 这是计算分组级别的函数。 仅当指定了 GROUP BY 时，GROUPING_ID 才能在 SELECT 列表、HAVING 或 ORDER BY 子句中使用
