TSQL窗口函数

T-SQL（Transact-SQL）窗口函数是SQL Server中的一种高级功能，它允许用户在查询结果集中执行复杂的计算，而不需要对数据进行分组或聚合。窗口函数通过在数据集上定义一个“窗口”来工作，这个窗口可以是整个数据集，也可以是数据集的一个子集。

基础概念

窗口函数通常与OVER()子句一起使用，该子句定义了窗口的范围和排序规则。窗口函数可以分为两类：

1. 排名窗口函数：如ROW_NUMBER(), RANK(), DENSE_RANK()等，用于生成行号或排名。
2. 聚合窗口函数：如SUM(), AVG(), MIN(), MAX()等，用于在窗口内执行聚合计算。

优势

• 减少数据冗余：不需要多次扫描相同的数据。
• 提高查询效率：可以在一次查询中完成复杂的计算。
• 增强数据表达能力：可以轻松地实现复杂的业务逻辑。

类型

• 排名函数：
  • ROW_NUMBER(): 为每一行分配一个唯一的序号。
  • RANK(): 为每一行分配一个排名，相同值的行会得到相同的排名。
  • DENSE_RANK(): 类似于RANK()，但相同值的行之间不会留下空位。
• 聚合函数：
  • SUM(): 计算窗口内所有值的总和。
  • AVG(): 计算窗口内所有值的平均值。
  • MIN(): 返回窗口内的最小值。
  • MAX(): 返回窗口内的最大值。

应用场景

• 财务分析：计算累计销售额、滚动平均值等。
• 时间序列分析：计算移动平均、增长率等。
• 排名和评级：对产品、员工等进行排名或评级。

示例代码

假设我们有一个销售表Sales，包含以下列：SaleID, ProductID, SaleDate, Amount。

计算每个产品的累计销售额

SELECT 
    ProductID, 
    SaleDate, 
    Amount,
    SUM(Amount) OVER (PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeSales
FROM 
    Sales;

计算每个产品的滚动月平均销售额

SELECT 
    ProductID, 
    SaleDate, 
    Amount,
    AVG(Amount) OVER (PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS RollingMonthlyAverage
FROM 
    Sales;

常见问题及解决方法

问题：窗口函数的结果不符合预期。

原因：

• OVER()子句中的PARTITION BY或ORDER BY条件设置不正确。
• 窗口范围的定义有误。

解决方法：

• 检查PARTITION BY和ORDER BY子句是否正确反映了业务逻辑。
• 使用ROWS BETWEEN或RANGE BETWEEN明确指定窗口的范围。

例如，如果需要计算过去三个月的累计销售额，可以这样写：

SELECT 
    ProductID, 
    SaleDate, 
    Amount,
    SUM(Amount) OVER (PARTITION BY ProductID ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ThreeMonthCumulativeSales
FROM 
    Sales;

确保窗口范围正确地覆盖了过去三个月的数据。

通过理解和正确应用窗口函数，可以大大提高SQL查询的效率和灵活性。