SQL Server 2008 开窗函数

开窗函数的概念

SQL Server 2008引入了开窗函数（Window Functions），允许在查询结果集的子集（称为“窗口”）上执行计算，而无需分组或聚合整个结果集。开窗函数通过OVER()子句定义窗口的范围，支持对数据进行分区、排序和帧划分。

常见的开窗函数类型

聚合开窗函数
如SUM()、AVG()、COUNT()等，可与OVER()结合使用，实现分区的聚合计算。

SELECT SalesOrderID, ProductID, LineTotal,  
       SUM(LineTotal) OVER(PARTITION BY SalesOrderID) AS OrderTotal  
FROM Sales.SalesOrderDetail;  

排名开窗函数
包括ROW_NUMBER()、RANK()、DENSE_RANK()和NTILE()，用于生成分区内的排名值。

SELECT ProductID, Name, ListPrice,  
       RANK() OVER(ORDER BY ListPrice DESC) AS PriceRank  
FROM Production.Product;  

分析开窗函数
如FIRST_VALUE()、LAST_VALUE()、LAG()和LEAD()，用于访问窗口内其他行的数据。

SELECT BusinessEntityID, RateChangeDate, Rate,  
       LAG(Rate, 1, 0) OVER(ORDER BY RateChangeDate) AS PreviousRate  
FROM HumanResources.EmployeePayHistory;  

OVER()子句的组成部分

PARTITION BY
将数据划分为多个分区，函数在每个分区内独立计算。

SELECT CustomerID, SalesOrderID, TotalDue,  
       AVG(TotalDue) OVER(PARTITION BY CustomerID) AS AvgOrderByCustomer  
FROM Sales.SalesOrderHeader;  

ORDER BY
定义分区内数据的排序方式，影响排名函数和帧的范围。

SELECT ProductID, SalesOrderID, OrderQty,  
       SUM(OrderQty) OVER(PARTITION BY ProductID ORDER BY SalesOrderID) AS RunningTotal  
FROM Sales.SalesOrderDetail;  

ROWS/RANGE
指定帧的边界，如ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING。

SELECT Date, SalesAmount,  
       AVG(SalesAmount) OVER(ORDER BY Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg  
FROM Sales.DailySales;  

实际应用场景

计算移动平均
通过定义帧范围实现动态计算。

SELECT Month, Revenue,  
       AVG(Revenue) OVER(ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg  
FROM Sales.MonthlyRevenue;  

填充缺失数据
使用LAG()或LEAD()引用相邻行的值。

SELECT Date, Temperature,  
       LAG(Temperature, 1) OVER(ORDER BY Date) AS PreviousDayTemp  
FROM Weather.Readings;  

生成连续排名
DENSE_RANK()避免排名值的间隔。

SELECT StudentID, Score,  
       DENSE_RANK() OVER(ORDER BY Score DESC) AS Rank  
FROM Exam.Results;  

性能注意事项

开窗函数可能增加查询复杂度，尤其在处理大数据集时。合理使用索引和限制分区范围可优化性能。避免在OVER()子句中使用不必要的排序或宽泛的帧定义。

开窗函数在SQL Server 2008中显著增强了数据分析能力，适用于复杂报表、趋势分析和数据比对场景。

PARTITION BY 的适用场景

PARTITION BY 用于窗口函数中，将数据分成多个分区（组），在每个分区内独立计算。适合需要对数据进行分组后分别聚合或分析的场景。

需要按照某一列或多列的值将数据分组时使用。例如计算每个部门的平均工资，可以按部门分区。每个分区会单独计算平均值，互不干扰。

需要对分组后的数据进行排名、累计求和等操作时使用。例如计算每个部门内员工的工资排名，需要先按部门分区，再在分区内排序计算排名。

ORDER BY 的适用场景

ORDER BY 用于对查询结果或窗口函数中的数据进行排序。适合需要明确排序规则的场景。

在普通查询中需要对最终结果排序时使用。例如查询员工信息并按工资从高到低显示。

在窗口函数中需要定义计算顺序时使用。例如计算累计和，需要先按日期排序再累加。排序决定了窗口函数计算的逻辑顺序。

PARTITION BY 和 ORDER BY 的组合使用

窗口函数中经常同时使用这两个子句。PARTITION BY 先分组，ORDER BY 再定义组内顺序。

计算移动平均时需要先按股票代码分区，再按交易日期排序。这样会在每支股票的时间序列上计算移动平均值。

计算部门内工资排名时需要先按部门分区，再按工资降序排序。这样能得到每个部门独立的工资排名。

关键区别

PARTITION BY 影响数据的分组方式，不改变每个分区内行的顺序。ORDER BY 只影响排序，不改变数据的分组。

PARTITION BY 后的窗口函数会对每个分区独立计算。只有 ORDER BY 时，窗口函数会跨所有数据计算。