SQL 窗口函数

2025 年 8 月 18 日 | 14 分钟阅读

结构化查询语言 (SQL) 不仅仅是选择、插入或更新表中的数据。窗口函数，也称为分析函数，对于执行分析任务非常有用，例如排名、计算移动平均值（不分组或丢失行级数据）或运行总计。

SQL 中的窗口函数是什么？

窗口函数对与当前行（称为窗口帧）相关的表行集执行计算。窗口函数为每一行返回一个值并提供额外的详细信息，这与聚合函数（如 SUM() 和 AVG()）不同，聚合函数为每个组返回一个值。

示例： 聚合函数就像计算城市的平均温度，而窗口函数就像计算每天的温度以及城市的平均温度。

语法

SELECT column1,
WindowFunctionName(column2) OVER (
  PARTITION BY column3
  ORDER BY column4
) AS NewColumn
FROM TableName;  

参数

SELECT： 这是一个 SQL 关键字，用于从数据库中特定表检索数据。
WindowFunctionName： 这是要应用于列的窗口函数的名称。
OVER： 它是所有窗口函数的必需子句，用于定义窗口帧（行集）。
PARTITION BY： 它将数据分成组。它是可选的。
ORDER BY： 它为每个分区内的行提供排序。它是可选的，但许多窗口函数都需要它。
column1： 这是一个常规列，将从表中选择。
column2： 这是将应用窗口函数的列。
column3： 这是在 PARTITION BY 子句中用于划分数据的列。
column4： 这是在 ORDER BY 子句中用于对每个分区内的数据进行排序的列。
NewColumn： 它是保存窗口函数结果的列的别名（自定义名称）。
TableName： 这是执行查询的表的名称。

SQL 窗口函数的类型

下面讨论了各种类型的 SQL 窗口函数

1. 排名窗口函数

这些函数用于根据指定标准为分区内的每一行分配排名。

以下是一些窗口排名函数

函数	描述
RANK()	它为每一行分配排名，但会跳过重复值的排名。
DENSE_RANK()	它类似于 RANK()，因此它也为行分配排名，但不跳过重复值。
ROW_NUMBER()	它为每个分区中的每一行分配从 1 开始的排名，但会跳过重复值的排名。
NTILE(n)	它将行分成 n 个组（即瓦片）并为每一行分配一个组号。

2. 聚合窗口函数

这些函数用于对定义的行集（窗口帧）执行计算。它与常规聚合函数不同，因为聚合窗口函数在结果中返回单独的行，而常规聚合函数返回单个聚合结果。

以下是一些聚合窗口函数

函数	描述
AVG()	它计算窗口帧中可用值的平均值。
SUM()	它计算窗口帧中可用值的总和。
MIN()	它返回窗口中的最小值。
MAX()	它返回窗口中的最大值。
COUNT()	它计算分区中存在的行数。

3. 值窗口函数

这些函数用于从相对于当前行的不同行返回一个值。

以下是一些值窗口函数

函数	描述
LEAD()	它返回下一行的值。
LAG()	它返回上一行的值。
FIRST_VALUE()	它返回第一行的值。
LAST_VALUE()	它返回最后一行的值。

SQL 窗口函数示例

让我们考虑一个简单的 Employees 表来解释窗口函数的所有概念

首先，让我们创建一个名为 Employees 的表并向其中插入数据。

创建表格

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Department VARCHAR(50),
    Salary DECIMAL(10, 2),
    SaleDate DATE
); 

插入数据

INSERT INTO Employees (EmployeeID, Department, Salary, SaleDate) VALUES
(1, 'IT', 60000, '2025-01-10'),
(2, 'IT', 60000, '2025-02-11'),
(3, 'HR', 45000, '2025-02-14'),
(4, 'HR', 48000, '2025-08-15'),
(5, 'IT', 70000, '2025-06-14'),
(6, 'HR', 46000, '2025-08-25'),
(7, 'IT', 52000, '2025-04-16');

获取表

输出

EmployeeID	部门	薪金	SaleDate
1	IT	60000.00	2025-01-10
2	IT	60000.00	2025-02-11
3	HR	45000.00	2025-02-14
4	HR	48000.00	2025-08-15
5	IT	70000.00	2025-06-14
6	HR	46000.00	2025-08-25
7	IT	52000.00	2025-04-16

排名窗口函数

示例 1：使用 RANK() 窗口函数

RANK() 窗口函数为分区内的每一行分配唯一的排名。如果两行具有相同的值，则两行将获得相同的排名，并且会跳过下一个排名，这意味着排名中会出现一个间隙。

查询

SELECT EmployeeID, Department, Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS EmployeeRank
FROM Employees;  

输出

EmployeeID	部门	薪金	EmployeeRank
4	HR	48000.00	1
6	HR	46000.00	2
3	HR	45000.00	3
5	IT	70000.00	1
1	IT	60000.00	2
2	IT	60000.00	2
7	IT	52000.00	4

说明

在上述查询中，有一个排名窗口函数，它根据 ORDER BY 子句和降序为每个分区内的每一行提供不同的排名，这意味着每个部门的排名是单独进行的。我们使用了 RANK()，因此在输出中，该函数为每一行提供了唯一的排名，但在 EmployeeID（2 和 7）之间存在并列，因此我们可以看到该函数为两行提供了相同的排名，但跳过了下一个排名并跳到 4 处理下一个值。

示例 2：使用 DENSE_RANK() 窗口函数

DENSE_RANK() 窗口函数类似于 RANK()，但如果行中存在重复值，它不会跳过下一个排名，这意味着排名中不会有间隙。这种间隙是 RANK() 与 DENSE_RANK() 的区别所在。

查询

SELECT EmployeeID, Department, Salary,
       DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS EmployeeDenseRank
FROM Employees; 

输出

EmployeeID	部门	薪金	EmployeeDenseRank
4	HR	48000.00	1
6	HR	46000.00	2
3	HR	45000.00	3
5	IT	70000.00	1
1	IT	60000.00	2
2	IT	60000.00	2
7	IT	52000.00	3

说明

在上述查询中，有一个排名窗口函数，它根据 ORDER BY 子句和降序为每个分区内的每一行提供不同的排名，这意味着每个部门的排名是单独进行的。我们使用了 DENSE_RANK()，因此在输出中，该函数为每一行提供了唯一的排名，但在 EmployeeID（2 和 7）之间存在并列，因此我们可以看到该函数为两行提供了相同的排名，但下一个排名没有跳过。

示例 3：使用 ROW_NUMBER() 窗口函数

ROW_NUMBER() 窗口函数为分区内的每一行分配唯一的序列号，即使两行中的值相同。

查询

SELECT EmployeeID, Department, Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNumber
FROM Employees; 

输出

EmployeeID	部门	薪金	RowNumber
4	HR	48000.00	1
6	HR	46000.00	2
3	HR	45000.00	3
5	IT	70000.00	1
1	IT	60000.00	2
2	IT	60000.00	3
7	IT	52000.00	4

说明

在上述查询中，有一个排名窗口函数，它根据 ORDER BY 子句和降序为每个分区内的每一行提供不同的序列号，这意味着每个部门都获得一个不同的序列号。我们使用了 ROW_NUMBER()，因此在输出中，该函数为每一行提供了唯一的序列号，但在 EmployeeID（2 和 7）之间存在并列，但我们可以看到该函数仍然为两行提供了唯一的排名。

示例 4：使用 NTILE(n) 窗口函数

NTILE(n) 窗口函数将结果集划分为 n 个桶（即每个分区内的相等部分），并为每一行分配一个桶号。

查询

SELECT EmployeeID, Department, Salary,
NTILE(2) OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryGroup
FROM Employees; 

输出

EmployeeID	部门	薪金	SalaryGroup
4	HR	48000.00	1
6	HR	46000.00	1
3	HR	45000.00	2
5	IT	70000.00	1
1	IT	60000.00	1
2	IT	60000.00	2
7	IT	52000.00	2

说明

在上述查询中，有一个 NTILE(2) 排名窗口函数，它根据薪资降序将每个部门分成两个组。在输出中，我们可以看到每个部门都分成两个组。

注意：如果行无法均匀拆分，例如 HR 的 3 行分成 2 组，那么 NTILE() 会将额外的行分配到前面的组中，因此组 1 可能比组 2 拥有更多的行。

聚合窗口函数

示例 1：使用 AVG() 窗口函数

AVG() 窗口函数计算窗口中的平均值。特定部门的每一行的平均值将相同。

查询

SELECT EmployeeID, Department, Salary,
AVG(Salary) OVER (PARTITION BY Department) AS AverageSalary
FROM Employees;  

输出

EmployeeID	部门	薪金	AverageSalary
3	HR	45000.00	46333.333333
4	HR	48000.00	46333.333333
6	HR	46000.00	46333.333333
1	IT	60000.00	60500.000000
2	IT	60000.00	60500.000000
5	IT	70000.00	60500.000000
7	IT	52000.00	60500.000000

说明

在上述查询中，有一个聚合窗口函数，它计算特定部门每一行员工的平均工资。在输出中，我们可以看到 AverageSalary 列中 HR 和 IT 部门每一行的平均工资相同。

示例 2：使用 SUM() 窗口函数

SUM() 窗口函数用于查找每个分区的总值，并在每一行旁边显示总计。

查询

SELECT EmployeeID, Department, Salary,
SUM(Salary) OVER (PARTITION BY Department) AS TotalSalary
FROM Employees; 

输出

EmployeeID	部门	薪金	AverageSalary
3	HR	45000.00	139000.00
4	HR	48000.00	139000.00
6	HR	46000.00	139000.00
1	IT	60000.00	242000.00
2	IT	60000.00	242000.00
5	IT	70000.00	242000.00
7	IT	52000.00	242000.00

说明

在上述查询中，有一个聚合窗口函数，它计算每个部门的总工资。在输出中，我们可以看到 TotalSalary 列中每个部门的总工资。

示例 3：使用 MIN() 窗口函数

MIN() 窗口函数用于查找每个分区的最小值，并在结果集的每一行旁边显示该最小值。

查询

SELECT EmployeeID, Department, Salary,
MIN(Salary) OVER (PARTITION BY Department) AS MinSalary
FROM Employees; 

输出

EmployeeID	部门	薪金	MinSalary
3	HR	45000.00	45000.00
4	HR	48000.00	45000.00
6	HR	46000.00	45000.00
1	IT	60000.00	52000.00
2	IT	60000.00	52000.00
5	IT	70000.00	52000.00
7	IT	52000.00	52000.00

说明

在上述查询中，有一个聚合窗口函数，它计算每个部门的最低工资。在输出中，我们可以看到 MinSalary 列中每个部门的最低工资。

示例 4：使用 MAX() 窗口函数

MAX() 窗口函数用于查找每个分区的最大值，并在结果集的每一行旁边显示该最大值。

查询

SELECT EmployeeID, Department, Salary,
MAX(Salary) OVER (PARTITION BY Department) AS MaxSalary
FROM Employees;

输出

EmployeeID	部门	薪金	MaxSalary
3	HR	45000.00	48000.00
4	HR	48000.00	48000.00
6	HR	46000.00	48000.00
1	IT	60000.00	70000.00
2	IT	60000.00	70000.00
5	IT	70000.00	70000.00
7	IT	52000.00	70000.00

说明

在上述查询中，有一个聚合窗口函数，它计算每个部门的最高工资。在输出中，我们可以看到 MaxSalary 列中每个部门的最高工资。

示例 5：使用 COUNT() 窗口函数

COUNT() 窗口函数用于计算窗口中的行数，并在结果集的每一行旁边显示此计数。

查询

SELECT EmployeeID, Department, Salary,
COUNT(*) OVER (PARTITION BY Department) AS EmployeeCount
FROM Employees;

输出

EmployeeID	部门	薪金	EmployeeCount
3	HR	45000.00	3
4	HR	48000.00	3
6	HR	46000.00	3
1	IT	60000.00	4
2	IT	60000.00	4
5	IT	70000.00	4
7	IT	52000.00	4

说明

在上述查询中，有一个聚合窗口函数，它显示每个部门的员工人数。在输出中，我们可以看到 EmployeeCount 列中每个部门的员工人数。

值窗口函数

示例 1：使用 LEAD() 窗口函数

LEAD() 窗口函数用于查找分区内下一行的值，并在结果集的每一行旁边显示该值。

查询

SELECT EmployeeID, Department, Salary,
LEAD(Salary) OVER (PARTITION BY Department ORDER BY Salary DESC) AS NextSalary
FROM Employees;

输出

EmployeeID	部门	薪金	ENextSalary
4	HR	48000.00	46000.00
6	HR	46000.00	45000.00
3	HR	45000.00	NULL
5	IT	70000.00	60000.00
1	IT	60000.00	60000.00
2	IT	60000.00	52000.00
7	IT	52000.00	NULL

说明

在上述查询中，有一个聚合窗口函数，它返回下一个工资。在输出中，我们可以在每一行旁边观察到下一个工资。

示例 2：使用 LAG() 窗口函数

LAG() 窗口函数用于查找分区内上一行的值，并在结果集的每一行旁边显示该值。

查询

SELECT EmployeeID, Department, Salary,
LAG(Salary) OVER (PARTITION BY Department ORDER BY Salary DESC) AS PreviousSalary
FROM Employees;  

输出

EmployeeID	部门	薪金	PreviousSalary
4	HR	48000.00	NULL
6	HR	46000.00	48000.00
3	HR	45000.00	46000.00
5	IT	70000.00	NULL
1	IT	60000.00	70000.00
2	IT	60000.00	60000.00
7	IT	52000.00	60000.00

说明

在上述查询中，有一个聚合窗口函数，它返回上一个工资。在输出中，我们可以在每一行旁边观察到上一个工资。

示例 3：使用 FIRST_VALUE() 窗口函数

FIRST_VALUE() 窗口函数返回分区内的第一个值，并在结果集的每一行旁边显示该值。

查询

SELECT EmployeeID, Department, Salary,
FIRST_VALUE(Salary) OVER (PARTITION BY Department ORDER BY Salary DESC) AS FirstSalary
FROM Employees; 

输出

EmployeeID	部门	薪金	FirstSalary
4	HR	48000.00	48000.00
6	HR	46000.00	48000.00
3	HR	45000.00	48000.00
5	IT	70000.00	70000.00
1	IT	60000.00	70000.00
2	IT	60000.00	70000.00
7	IT	52000.00	70000.00

说明

在上述查询中，有一个聚合窗口函数，它返回上一个工资。在输出中，我们可以在每一行旁边观察到上一个工资。

示例 4：使用 LAST_VALUE() 窗口函数

LAST_VALUE() 返回窗口帧中的最后一个值。LAST_VALUE() 只考虑从第一行到当前行的范围。我们可能需要显式定义帧并在每一行旁边显示值。

查询

SELECT EmployeeID, Department, Salary,
LAST_VALUE(Salary) OVER (PARTITION BY Department ORDER BY Salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSalary
FROM Employees;  

输出

EmployeeID	部门	薪金	LastSalary
4	HR	48000.00	45000.00
6	HR	46000.00	45000.00
3	HR	45000.00	45000.00
5	IT	70000.00	52000.00
1	IT	60000.00	52000.00
2	IT	60000.00	52000.00
7	IT	52000.00	52000.00

说明

在上述查询中，有一个聚合窗口函数，它给出下一个工资。我们可能需要显式指定使用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 的帧，以便帧从分区的第一行跨越到最后一行，并且 LAST_VALUE() 函数返回最后一个值。我们可以在结果集的每一行中观察到下一个工资。

SQL 窗口函数的优点

保留行级详细信息： 它允许执行聚合，而不会像 GROUP BY 那样折叠原始行。它在结果中显示聚合值和非聚合值。

可重用表达式： 它允许在同一个 SELECT 语句中应用多个窗口函数。

启用复杂分析： 它轻松计算复杂计算，例如运行总计、移动平均值、排名等。这些复杂计算可以在单个查询中完成，否则它们需要多个步骤、子查询等。

高度灵活： 它与 PARTITION BY、ORDER BY 和帧子句一起使用，以控制每个计算的数据。

高效报告： 它简化了生成仪表板、报告和分页报告，而无需使用多个联接或子查询。

提高了可读性： 它简化了逻辑，减少了在许多情况下使用子查询和复杂联接的需要。

SQL 窗口函数的缺点

性能开销： 如果未正确索引或优化，它在大型数据集上可能会更慢。

复杂性和可读性： 窗口函数的语法很复杂，因为查询会变得很长且难以维护。理解复杂的查询变得困难，这种复杂性会影响代码可读性。

内存使用增加： 与常规 SQL 语句相比，SQL 窗口函数占用更多内存，因此在处理大型数据集时会消耗更多空间。

资源密集型操作： SQL 窗口函数需要额外的处理，这会导致对大量计算资源的需求，尤其是在大型数据集上。

调试挑战： 与简单的 SQL 语句相比，由于多个窗口函数，调试复杂查询很困难。

仅限于特定 RDBMS： 较旧的数据库版本可能不支持或功能有限，这意味着并非所有数据库都可以使用 SQL 窗口函数。

结论

SQL 窗口函数对于高级数据分析非常重要，它在处理分区数据时提供了灵活性。我们可以通过使用 OVER、PARTITION BY 和 ORDER BY 子句来执行复杂的计算，例如聚合计算、累积总计和排名，同时保留行级数据。我们已经理解了各种类型的窗口函数，它们为跨不同类型数据集的数据操作提供了灵活的方法。

常见问题解答 (FAQs)

1. RANK() 和 ROW_NUMBER() 有什么区别？

ROW_NUMBER	RANK()
它用于为每一行分配一个从 1 开始的唯一编号。	它为每一行分配一个从 1 开始的唯一编号，但它可以为具有重复值的行分配相同的排名。
它为排序列中的重复值提供唯一的编号。	它为排序列中的所有重复行提供相同的编号。

以下是 RANK() 和 ROW_NUMBER() 之间的区别

2. 我应该何时使用 PARTITION BY 子句？

您可以使用 PARTITION BY 子句在执行窗口操作之前根据指定列将行分成组。它允许使用窗口函数（例如 RANK()、SUM() 等）对每个组执行计算。

3. 我可以在没有 PARTITION BY 的情况下使用窗口函数吗？

是的，您可以在没有 PARTITION BY 子句的情况下使用窗口函数。窗口函数将整个结果集视为单个分区进行操作。

4. 聚合函数和窗口函数有什么区别？

聚合函数	窗口函数
这些函数对一组行进行操作并返回单个行。	这些函数对一组行进行操作，而不会将结果折叠成单个行。
它使用 GROUP BY 子句定义一组行。	它使用 OVER 子句定义一组行。
与 GROUP BY 子句一起使用时，它会减少结果集中的行数。	它保留所有原始行，并向结果集中的每一行添加额外的分析信息。

5. 所有数据库都支持窗口函数吗？

不，并非所有数据库都支持窗口函数。一些较旧的或专门的数据库系统可能不支持窗口函数，但大多数现代关系数据库都支持窗口函数，例如 PostgreSQL、SQL Server、MySQL (v8+)、Oracle 和 SQLite。

6. 我可以在一个查询中使用多个窗口函数吗？

当然。您可以在一个查询中使用多个窗口函数。每个窗口函数都包含自己的 OVER 子句，允许为每个函数进行不同的分区和排序。

示例： 一个查询可以使用一个窗口函数计算运行总和，并使用另一个窗口函数计算移动平均值。所有这些函数都可以在同一个 SELECT 语句中通过单个查询完成。

下一个主题Coalesce SQL

← 上一个下一个 →

SQL 窗口函数

SQL 中的窗口函数是什么？

参数

SQL 窗口函数的类型

1. 排名窗口函数

2. 聚合窗口函数

3. 值窗口函数

SQL 窗口函数示例

排名窗口函数

示例 1：使用 RANK() 窗口函数

示例 2：使用 DENSE_RANK() 窗口函数

示例 3：使用 ROW_NUMBER() 窗口函数

示例 4：使用 NTILE(n) 窗口函数

注意：如果行无法均匀拆分，例如 HR 的 3 行分成 2 组，那么 NTILE() 会将额外的行分配到前面的组中，因此组 1 可能比组 2 拥有更多的行。

聚合窗口函数

示例 1：使用 AVG() 窗口函数

示例 2：使用 SUM() 窗口函数

示例 3：使用 MIN() 窗口函数

示例 4：使用 MAX() 窗口函数

示例 5：使用 COUNT() 窗口函数

值窗口函数

示例 1：使用 LEAD() 窗口函数

示例 2：使用 LAG() 窗口函数

示例 3：使用 FIRST_VALUE() 窗口函数

示例 4：使用 LAST_VALUE() 窗口函数

SQL 窗口函数的优点

SQL 窗口函数的缺点

结论

常见问题解答 (FAQs)

联系信息

关注我们

教程

面试题

在线编译器

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

SQL 教程

SQL 数据库

SQL 表

SQL Select

SQL 子句

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL 键

Interview

SQL 多选题

SQL 注入

区别

PL/SQL 教程

其他

SQL 字符串函数

测验

SQL 窗口函数

SQL 中的窗口函数是什么？

参数

SQL 窗口函数的类型

1. 排名窗口函数

2. 聚合窗口函数

3. 值窗口函数

SQL 窗口函数示例

排名窗口函数

示例 1：使用 RANK() 窗口函数

示例 2：使用 DENSE_RANK() 窗口函数

示例 3：使用 ROW_NUMBER() 窗口函数

示例 4：使用 NTILE(n) 窗口函数

注意：如果行无法均匀拆分，例如 HR 的 3 行分成 2 组，那么 NTILE() 会将额外的行分配到前面的组中，因此组 1 可能比组 2 拥有更多的行。

聚合窗口函数

示例 1：使用 AVG() 窗口函数

示例 2：使用 SUM() 窗口函数

示例 3：使用 MIN() 窗口函数

示例 4：使用 MAX() 窗口函数

示例 5：使用 COUNT() 窗口函数

值窗口函数

示例 1：使用 LEAD() 窗口函数

示例 2：使用 LAG() 窗口函数

示例 3：使用 FIRST_VALUE() 窗口函数

示例 4：使用 LAST_VALUE() 窗口函数

SQL 窗口函数的优点

SQL 窗口函数的缺点

结论

常见问题解答 (FAQs)

相关帖子

SQL 中的 DIV 函数

如何在 SQL 中使用 ORDER BY

SQL 中的 MOD 函数

SQL 中的 COT 函数

如何使用 SQL 中的 DEFAULT

12 Codd 规则

CTE SQL

SQL 中的规范化

SQL 字符串连接

SQL 注释

订阅 Tpoint Tech