何謂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 子句中使用
