窗口函数

 数据库   ZeroIsStart   2024-10-20 09:58   17
  从入门到精通SQL Server

SQL Server 窗口函数详解：ROW_NUMBER、RANK、DENSE_RANK、NTILE 和 聚合窗口函数

SQL Server 提供了强大的窗口函数，用于在查询结果集中基于某个排序、分组的逻辑执行行级操作。窗口函数通常与 OVER() 子句一起使用，这使得我们可以对查询结果的每一行执行计算，同时保持数据的原始结构。这些函数广泛应用于排名、分组统计、累积和滑动窗口计算等场景。

本文将详细介绍常见的窗口函数：ROW_NUMBER、RANK、DENSE_RANK、NTILE 和聚合窗口函数（例如 SUM() OVER() 和 AVG() OVER()），并提供详细的示例和应用场景。

1. ROW_NUMBER()

ROW_NUMBER() 是一个用于为查询结果中的每一行生成一个唯一的递增整数值的窗口函数。它通常用于排序，生成每行的行号。ROW_NUMBER() 会对结果集中的行进行排序，并为每一行分配一个唯一的整数值。

语法：

ROW_NUMBER() OVER (ORDER BY 列名 [ASC|DESC])

示例：

假设我们有一个员工表 Employees，其中包含 EmployeeID 和 Salary 字段，我们希望按照工资排序并为每个员工生成一个唯一的行号。

SELECT 
    EmployeeID, 
    Salary, 
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

解释：

  • ROW_NUMBER() 根据 Salary 字段对结果进行排序（降序排列），并为每一行生成一个唯一的行号。最高工资的员工将被分配行号 1，依此类推。

应用场景：

  • ROW_NUMBER() 可以用于分页查询，生成每行的行号，帮助我们从结果集的指定位置开始显示数据。
  • 它也常用于去重操作，如选择每个组中的第一行数据。

2. RANK()

RANK() 函数与 ROW_NUMBER() 类似，但它会为相同排序值的行分配相同的排名，并跳过后续的排名。例如，如果两个员工的工资相同，都会被分配到排名 1，接下来的人会从排名 3 开始。

语法：

RANK() OVER (ORDER BY 列名 [ASC|DESC])

示例：

使用与前面相同的 Employees 表，我们要为员工根据工资生成排名。

SELECT 
    EmployeeID, 
    Salary, 
    RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

解释：

  • RANK() 为工资相同的员工分配相同的排名。如果有两个员工的工资相同，他们将被赋予排名 1，然后跳过排名 2，第三个员工的排名是 3。

应用场景：

  • 在需要排序并为相同值分配相同排名的场景下使用，例如体育比赛中的名次排名。
  • 如果需要处理“并列排名”的情况，RANK() 是一个很好的选择。

3. DENSE_RANK()

DENSE_RANK() 类似于 RANK()，但不同之处在于它不会跳过排名。即使有多个行拥有相同的排序值，后续的排名也会紧跟在前一个排名后。例如，如果两个员工的工资相同，他们将被分配到排名 1，第三个员工会获得排名 2，而不是 3。

语法：

DENSE_RANK() OVER (ORDER BY 列名 [ASC|DESC])

示例：

使用与前面相同的 Employees 表，我们为员工生成密集排名（没有排名跳跃）。

SELECT 
    EmployeeID, 
    Salary, 
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

解释：

  • DENSE_RANK() 会为工资相同的员工分配相同的排名，且不跳过排名。例如，若有两个员工工资相同并排名 1，那么第三名员工的排名是 2，而不是 3。

应用场景：

  • 在需要连续排名而不跳过排名的场合使用，比如学术成绩排名。

4. NTILE()

NTILE() 将结果集分割为 N 个相等的部分，并为每一行分配一个桶编号。这个函数对于将数据划分为多个组，或进行等分段分析非常有用。

语法：

NTILE(N) OVER (ORDER BY 列名 [ASC|DESC])
  • N 是要分割的组数。

示例：

假设我们有一个学生表 Students，我们希望将学生根据成绩分成 4 组。

SELECT 
    StudentID, 
    Grade, 
    NTILE(4) OVER (ORDER BY Grade DESC) AS Quartile
FROM Students;

解释：

  • NTILE(4) 将学生分成 4 个组，并为每个学生分配一个组编号（1 到 4）。如果成绩相同的学生，分配的组号会保持一致。

应用场景：

  • NTILE() 用于将数据划分为不同的等级或分段，如将数据分为四分位数（quartiles）或其他分段。

5. 聚合窗口函数：SUM() OVER() 和 AVG() OVER()

聚合窗口函数允许我们在窗口内执行聚合操作，而不必将数据按组汇总。常见的聚合窗口函数包括 SUM()、AVG()、MIN()、MAX() 等。

语法：

聚合函数() OVER (PARTITION BY 分区列 ORDER BY 排序列)
  • PARTITION BY：用于定义如何将数据划分为不同的组。如果省略，默认会使用整个数据集。
  • ORDER BY：指定窗口内的排序。

示例：

假设我们有一个销售表 Sales，包含 SalesPersonID 和 Amount 字段，且我们希望计算每个销售员的累计销售额。

SELECT 
    SalesPersonID, 
    Amount,
    SUM(Amount) OVER (PARTITION BY SalesPersonID ORDER BY SaleDate) AS CumulativeSales
FROM Sales;

解释：

  • SUM(Amount) OVER (PARTITION BY SalesPersonID ORDER BY SaleDate) 计算每个销售员的累计销售额。PARTITION BY 将数据按 SalesPersonID 划分，而 ORDER BY 根据 SaleDate 排序，从而生成累计销售额。

其他聚合函数示例：

计算每个销售员的平均销售额：

SELECT 
    SalesPersonID, 
    Amount,
    AVG(Amount) OVER (PARTITION BY SalesPersonID) AS AvgSales
FROM Sales;

计算每个销售员的最大和最小销售额：

SELECT 
    SalesPersonID, 
    Amount,
    MAX(Amount) OVER (PARTITION BY SalesPersonID) AS MaxSales,
    MIN(Amount) OVER (PARTITION BY SalesPersonID) AS MinSales
FROM Sales;

计算总销售额：

SELECT 
    SalesPersonID, 
    SUM(Amount) OVER () AS TotalSales
FROM Sales;

6. 窗口函数的应用场景

  • 排名：使用 ROW_NUMBER()、RANK()、DENSE_RANK() 和 NTILE() 等窗口函数来为数据生成排名，如销售排名、比赛名次等。
  • 累计和滑动窗口：使用聚合窗口函数（如 SUM() OVER()、AVG() OVER()）计算累积值、滚动平均等，如计算每个月的累计销售额。
  • 分段统计：使用 NTILE() 将数据分为多个组，进行分段统计或分析，如将学生成绩分为四个等级。
  • 分组聚合：通过 PARTITION BY 子句为不同组计算聚合值，如按部门计算员工的平均工资。

总结

SQL Server 提供了丰富的窗口函数，可以帮助我们在查询结果集中执行行级别的计算，并保留数据的原始结构。常见的窗口函数包括：

  • ROW_NUMBER()：生成唯一的递增行号。
  • RANK() 和 DENSE_RANK()：用于排名，处理重复值。
  • NTILE()：将数据分为多个组并为每行分配一个组号。
  • 聚合窗口函数（如 SUM() OVER() 和 AVG() OVER()）：在窗口内计算聚合值，如累计总和和平均值。

这些窗口函数在复杂的数据分析和报告中非常有用，可以帮助我们高效地进行数据处理和分析。通过

合理的应用这些函数，我们可以极大地提高 SQL 查询的效率和灵活性。