SQL Server 窗口函数

2025年3月17日 | 阅读 10 分钟

我们都知道常规的聚合函数可以对表进行计算并与 GROUP BY 子句一起工作。然而，只有一小部分 SQL 用户使用窗口函数，这些函数对一组行进行操作，并为每一行显示一个聚合值。本文将详细讨论 SQL Server 中的窗口函数。

什么是窗口函数？

窗口函数用于根据一组行执行聚合计算，并为每组返回多行。窗口一词代表函数将操作的行集。此函数执行计算的方式与聚合函数相同。与对整个表进行操作的聚合函数不同，窗口函数不会返回一个合并为单行的结果。这意味着窗口函数作用于一组行，并为每一行返回一个总值。因此，每一行都保留其独特的身份。

下面的图示解释了 SQL Server 中聚合函数和窗口函数的区别

窗口函数类型

SQL Server 主要将窗口函数分为三种类型

聚合窗口函数： 这些函数作用于多行，此类函数的示例包括 SUM()、MAX()、MIN()、AVG()、COUNT() 等。
排名窗口函数： 这些函数为表中的每个分区行进行排名。此类函数的示例包括 RANK()、DENSE_RANK()、ROW_NUMBER()、NTILE() 等。
值窗口函数： 这些函数由幂级数局部表示。此类函数的示例包括 LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE() 等。

语法

以下是使用窗口函数的基本语法

window_function_name([ALL] expression)   
OVER (  
    [partition_defintion]  
    [order_definition] 
) 

参数解释

让我们理解上述语法中使用的参数

window_function： 指示您的窗口函数的名称。

ALL： 这是一个可选关键字，用于计算所有值，包括重复项。我们不能在窗口函数中使用 DISTINCT 关键字。

Expression： 这是窗口函数作用的列或表达式的名称。换句话说，它是我们为其计算聚合值的列名。

OVER： 它为聚合函数指定窗口子句。它主要包含两个表达式 partition by 和 order by，并且即使没有表达式，它也始终具有开括号和闭括号。

PARTITION BY： 此子句将行划分为分区，然后在每个分区上操作窗口函数。在这里，我们需要在 PARTITION BY 子句后为分区提供列。如果要指定多个列，必须用逗号分隔它们。如果未指定此子句，SQL Server 将对整个表进行分组，并相应地聚合值。

ORDER BY： 用于指定每个分区内行的顺序。如果未定义此子句，SQL Server 将使用整个表的 ORDER BY。

示例

让我们通过一个例子来理解窗口函数的概念。首先，我们将使用以下语句创建一个名为“product_sales”的表

CREATE TABLE Product_Sales(  
    Emp_Name VARCHAR(45) NOT NULL,  
    Year INT NOT NULL,
	Country VARCHAR(45) NOT NULL,  
    Prod_name VARCHAR(45) NOT NULL,  
    Sales DECIMAL(12,2) NOT NULL,  
    PRIMARY KEY(Emp_Name, Year)  
); 

接下来，我们将使用下面的 INSERT 语句向该表中填充记录

INSERT INTO Product_Sales(Emp_Name, Year, Country, Prod_name, Sales)  
VALUES('Mike Johnson', 2017, 'Britain', 'Laptop', 10000),  
('Mike Johnson', 2018, 'Britain', 'Laptop', 15000),  
('Mike Johnson', 2019, 'Britain', 'TV', 20000),  
('Mary Greenspan', 2017, 'Australia', 'Computer', 15000),  
('Mary Greenspan', 2018, 'Australia', 'Computer', 10000),  
('Mary Greenspan', 2019, 'Australia', 'TV', 20000),  
('Nancy Jackson', 2017, 'Canada', 'Mobile', 20000),  
('Nancy Jackson', 2018, 'Canada', 'Calculator', 1500),  
('Nancy Jackson', 2019, 'Canada', 'Mobile', 25000);  

我们可以使用 SELECT 语句验证插入的记录。我们将看到以下输出

现在我们将使用此表演示所有窗口函数。

聚合窗口函数

SUM()

这是一个聚合函数，用于计算指定字段的总和，如果未指定任何分组，则为指定组或整个表。在这里，我们将通过两种方式检查此函数：常规聚合函数或窗口聚合函数。

下面的语句解释了常规聚合函数，该函数按国家/地区添加订单金额

SELECT Country, SUM(Sales) AS total_amount
FROM Product_Sales GROUP BY Country;

执行该语句，我们看到此函数将多行分组为单个输出行。它导致单个行丢失其身份。

下面的语句解释了窗口聚合函数，该函数维护行的身份。它还为每一行显示聚合值。

SELECT Emp_Name, Year, Country, Prod_name, Sales, SUM(Sales) 
OVER(PARTITION BY Country) as grand_total 
FROM Product_Sales; 

执行查询将返回以下输出。在这里，我们可以看到它聚合了每个国家/地区的数据，并显示了每个国家/地区的总销售额。它还为总销售额插入了另一个名为 grand_total 的列，以便每一行都保留其身份。

AVG()

此函数返回指定列的平均值。它与窗口函数的工作方式完全相同。

下面的示例将生成每个国家/地区和每年的平均销售额。在这里，我们通过在分区列表中指定多个字段来指定多个平均值。

SELECT Emp_Name, Year, Country, Prod_name, Sales, AVG(Sales) 
OVER(PARTITION BY Country, YEAR(Year)) as avg_sales_amount 
FROM Product_Sales; 

执行该语句将返回以下输出，其中我们可以看到，平均而言，我们从澳大利亚获得了 15000 的销售额。

MIN()

此函数返回指定组的最小值。当未定义组时，它将返回整个表的最小值。

以下示例将返回每个国家/地区的最小销售额

SELECT Emp_Name, Year, Country, Prod_name, Sales, MIN(Sales) 
OVER(PARTITION BY Country) AS minimum_sales_amount 
FROM Product_Sales;

执行查询将产生以下输出，其中我们可以看到每个国家/地区的最小销售额

MAX()

此函数返回指定组的最大值。当未定义组时，它将返回整个表的最大值。

以下示例将返回每个国家/地区的最高销售额

SELECT Emp_Name, Year, Country, Prod_name, Sales, MAX(Sales) 
OVER(PARTITION BY Country) AS minimum_sales_amount 
FROM Product_Sales;

执行查询将产生以下输出，其中我们可以看到每个国家/地区的最高销售额

COUNT()

count 函数将返回表中或组中存在的行数或记录总数。常规聚合函数使用 DISTINCT 关键字来避免计算重复行。但窗口 count 函数不支持此关键字。如果我们将此关键字与窗口函数一起使用，SQL Server 将抛出错误。

假设我们想查看有多少员工在 2018 年订购了产品。我们不能直接计算所有员工，因为同一员工在同一年订购了多个产品。

例如,

COUNT(emp_name) 将产生不正确的结果，因为它也会计算重复项。COUNT(DISTINCT emp_name) 将产生正确的结果，因为它总是只计算每个员工一次。

此语句成功执行，因为它是一个常规聚合函数

SELECT Country, COUNT(DISTINCT Emp_name) As number_of_employees
FROM Product_Sales
GROUP BY Country;

此语句将产生错误，因为它是一个窗口聚合函数

SELECT Country, COUNT(DISTINCT Emp_name) 
OVER(PARTITION BY Country) As number_of_employees
FROM Product_Sales;

这是错误

下面的语句将使用窗口 count 函数返回每个国家/地区的总产品销售额

SELECT Emp_Name, Year, Country, Prod_name, Sales, COUNT(Prod_name) 
OVER(PARTITION BY Country) As total_product
FROM Product_Sales;

这是结果：

排名窗口函数

排名函数对定义列中的值进行排名并根据其排名进行分类。SQL Server 支持的排名函数如下

RANK()、DENSE_RANK()、ROW_NUMBER() 和 NTILE()。让我们基于包含以下数据的“rank_demo”表详细讨论每个函数

RANK()

它用于为表中的每一行生成唯一的排名，基于指定的值。如果此函数获得两个具有相同值的记录，它将为这两条记录分配相同的排名，并跳过下一个排名。例如，如果排名 2 有两个相同的 [值]，则 rank 函数为这两条记录提供相同的排名 2，并跳过下一个排名 3。现在，下一个排名将被指定为排名 4。

下面的语句通过根据城市分配编号来解释 RANK() 函数

SELECT first_name, last_name, city,   
RANK () OVER (ORDER BY city) AS Rank_No   
FROM rank_demo; 

此查询返回以下输出，我们在其中看到相同的排名（2）被分配给两个具有相同城市名称的相同记录。排名的下一个数字将是其前一个排名加上重复数字的数量，即 4。

DENSE_RANK()

它的工作方式与 RANK() 函数相同，只是它不跳过任何排名。它总是按连续顺序分配排名。这意味着当找到两条记录相等时，此函数将为这两条记录分配相同的排名，并且下一个排名是下一个连续编号。

下面的查询实际解释了此函数，用于根据城市为每一行分配排名编号

SELECT first_name, last_name, city,   
DENSE_RANK() OVER (ORDER BY city) AS Rank_No   
FROM rank_demo; 

此查询将返回以下输出，我们在其中可以看到重复值具有相同的排名，并且下一个记录被赋予下一个排名，而不会跳过排名值。

ROW_NUMBER()

它用于为分区内的每条记录分配唯一的顺序编号。它总是从一开始，然后增加一，直到完成分区中的所有记录。当一个分区排名完成并转到下一个分区时，它将重置。

不带 PARTITION BY 的 ROW_NUMBER() 示例

下面的查询根据城市为每一行分配编号

SELECT first_name, last_name, city,   
ROW_NUMBER() OVER (ORDER BY city) AS Rank_No   
FROM rank_demo;

它会返回以下输出

SQL Server Window Functions

带 PARTITION BY 的 ROW_NUMBER() 示例

下面的语句根据城市对表进行分区，这意味着每个城市的行号都会重置，然后重新从 1 开始。它还按 first_name 列对记录进行排序。

SELECT first_name, last_name, city,   
ROW_NUMBER() OVER (PARTITION BY city ORDER BY first_name) AS Rank_No   
FROM rank_demo;

它返回以下输出

NTILE()

此窗口函数将行分配到预定义的（N）个近似相等的分组中。每个行组根据定义的条件分配一个排名，并且编号从第一个组开始。它使我们能够确定特定行属于哪个百分位数（或四分位数或其他细分）。这意味着如果我们有 20 条记录并希望根据特定的值字段将它们分成五个四分位数，我们可以轻松做到，并查看每个四分位数中有多少行。

下面的语句将根据 city 列将表分成 3 个四分位数

SELECT first_name, last_name, city,   
NTILE(3) OVER ( ORDER BY city) AS Rank_No   
FROM rank_demo;

执行该语句将返回以下输出，我们在其中看到每个组有三个四分位数

PERCENT_RANK()

此函数评估结果集分区中行的百分位数排名（相对排名）。结果介于 0 和 1 之间。如果找到 NULL 值，它将其视为可能的最低值。

此函数使用以下公式为每条记录评估排名

其中，rank 表示 rank() 函数返回的每行的编号，total_rows 是分区中找到的总行数。

以下示例将按国家/地区名称的顺序计算每行的排名值

SELECT Year, Prod_name, Country, Sales,     
PERCENT_RANK() OVER(PARTITION BY Year ORDER BY Country) AS my_rank   
FROM Product_Sales;  

执行该语句将返回预期的输出

值窗口函数

SQL Server 使用此函数在表中获取第一个、最后一个、前一个和下一个值。它主要包含这些函数：LAG()、LEAD()、FIRST_VALUE() 和 LAST_VALUE()。

LEAD 和 LAG 函数

LEAD 和 LAG 函数用于从当前行在其分区内获取指定行的前一个和后一个值。

让我们以上面的 product_sales 表为例来演示这些函数。以下示例返回每个员工的销售额和下一个销售额明细。它首先根据年份拆分结果集，然后根据 country 列对每个分区进行排序。之后，我们必须对每个分区使用 LEAD() 函数来获取下一个销售额明细。

SELECT Year, Prod_name, Country, Sales,     
LEAD(Sales,1) OVER (PARTITION BY Year ORDER BY Country) AS Next_Sale    
FROM Product_Sales;  

执行该语句将显示预期的结果

以下示例返回每个员工的销售额和前一个销售额明细。它首先根据年份拆分结果集，然后根据 country 列对每个分区进行排序。之后，我们必须对每个分区使用 LAD() 函数来获取前一个销售额明细。

SELECT Year, Prod_name, Country, Sales,     
LAG(Sales, 1) OVER (PARTITION BY Year ORDER BY Country) AS Previous_Sale    
FROM Product_Sales;

执行该语句将显示预期的结果

FIRST_VALUE() 和 LAST_VALUE()

这些函数用于在表或分区（如果指定了 PARTITION BY 子句）中查找第一个和最后一个记录。这里我们应该注意到，这些函数必须使用 ORDER BY 子句。让我们通过实际示例了解这些函数在 SQL Server 中的工作方式。

以下示例将查找给定表中山每个国家/地区的第一笔和最后一笔销售额

SELECT Year, Prod_name, Country, Sales,     
FIRST_VALUE(Sales) OVER(PARTITION BY Country ORDER BY Country) first_sale,
LAST_VALUE(Sales) OVER(PARTITION BY Country ORDER BY Country) last_sale    
FROM Product_Sales; 

执行查询将显示预期的结果，如下所示

结论

本文将解释 SQL Server 中使用的所有窗口函数，这些函数作用于一组行并为每一行返回一个聚合值。

下一主题SQL Server 日期函数

SQL Server 窗口函数

什么是窗口函数？

窗口函数类型

示例

聚合窗口函数

排名窗口函数

值窗口函数

联系信息

关注我们

教程

面试题

在线编译器

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 Server 教程

数据库操作

SQL Server 数据库

聚合函数

SQL Server 运算符

SQL Server 子句

SQL Server 键

SQL Server 进阶

杂项

服务

面试题

SQL Server 窗口函数

什么是窗口函数？

窗口函数类型

示例

聚合窗口函数

排名窗口函数

值窗口函数

相关帖子

SQL Server 别名

SQL Server CAST

SQL Server 中的触发器

SQL Server 中的存储过程

SQL Server CONVERT

SQL Server CONCAT_WS 函数

SQL Server 重命名数据库

SQL Server 复制表

SS DATEDIFF 函数

SQL Server IF ELSE

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器