SQL中的窗口函数是什么？RANK、ROW_NUMBER等详解

发布时间：2025-09-04

点击量：

窗口函数是在不聚合行的前提下，基于“窗口”内相关行对每行数据进行计算的强大工具，其核心是OVER()子句定义的窗口范围。与传统聚合函数（如SUM、*G配合GROUP BY）不同，窗口函数保留原始数据的每一行，同时为每行生成一个基于窗口计算的新值，适用于需保留细节并进行复杂分析的场景。典型结构为：函数(表达式) OVER ([PARTITION BY 列名] [ORDER BY 列名])，其中PARTITION BY将数据分组，ORDER BY确定窗口内行的顺序。常见排名函数包括ROW_NUMBER()（唯一连续编号，无并列）、RANK()（并列后排名跳跃，如1,2,2,4）和DENSE_RANK()（并列后排名连续，如1,2,2,3），选择依据业务对并列的处理需求。窗口函数广泛应用于累计求和、移动平均、前后行比较（LAG/LEAD）、分组极值获取等高级分析场景，显著简化复杂查询，减少子查询与连接操作，提升可读性和执行效率。但其性能受排序开销、内存使用、索引支持和窗口框架影响较大，尤其在大数据集上需合理设计索引、优化分区与排序逻辑，避免不必要的开销。

sql中的窗口函数是什么？rank、row_number等详解

SQL中的窗口函数，简单来说，就是一种在不聚合行的情况下，对与当前行相关的行集执行计算的强大工具。它允许你在每行数据上，根据一个“窗口”内的其他行来计算一个值，比如排名、累计总和或者移动平均。RANK、ROW_NUMBER等就是这类函数中的佼佼者，它们让复杂的数据分析变得前所未有的简单和高效。

解决方案

在我看来，理解窗口函数的关键在于那个

OVER()

子句。它定义了你的“窗口”——也就是哪些行会参与到当前行的计算中。不像传统的

GROUP BY

会把多行数据折叠成一行聚合结果，窗口函数在计算完成后，依然会返回原始数据集的每一行，只是多了一个基于窗口计算出来的新列。这对于需要保留原始数据细节，同时又想进行复杂分析的场景来说，简直是神来之笔。

一个典型的窗口函数结构是这样的：

窗口函数(表达式) OVER ([PARTITION BY 列名] [ORDER BY 列名 [ASC|DESC]])

这里的

PARTITION BY

是可选的，它将数据集分成独立的组（或称“分区”），每个分区内部独立进行窗口计算。这就像是你把一个大班的学生按班级分组，然后每个班级内部再进行排名。而

ORDER BY

则定义了窗口内行的排序顺序，这对于像排名、累积求和这类依赖顺序的计算至关重要。

窗口函数与传统聚合函数有何本质区别，为何选择它们？

这真的是一个非常核心的问题，也是很多人初学时会感到困惑的地方。说白了，传统聚合函数（比如

SUM()

*G()

COUNT()

配合

GROUP BY

）的目的是“汇总”。它们把一组行压缩成一个单一的摘要值。比如，你想知道每个部门的总销售额，

GROUP BY department_id

然后

SUM(sales)

，结果就是每个部门一行数据，显示总销售额。原始的每笔销售记录就看不到了。

但窗口函数则完全不同。它们执行的是“行级计算”。它们在计算时确实会考虑一组行（那个“窗口”），但最终结果是为每一行都生成一个值。这意味着你既能看到每笔交易的详细信息，又能在这笔交易旁边看到它在某个特定分组（比如同部门）中的排名，或者它到目前为止的累计销售额。

为什么选择它们？原因很多，但最主要的有以下几点：

保留细节，增加上下文： 你不需要为了聚合而丢失原始数据。这是它最大的魅力。
简化复杂查询： 以前需要通过子查询、自连接甚至多次查询才能实现的功能，现在一个窗口函数就能搞定。比如，找出每个部门销售额最高的员工，没有窗口函数的话，你可能需要先找出每个部门的最高销售额，再连接回原表找出对应的员工。有了
```
RANK()
```
或
```
ROW_NUMBER()
```
，这变得异常简单。
性能优化： 很多情况下，数据库引擎能更高效地处理窗口函数，因为它只需要一次数据扫描和排序，而复杂的子查询或自连接可能会导致多次扫描和连接操作。当然，这也不是绝对的，具体还得看查询优化器和数据量。

举个例子，假设我们有销售数据： | 订单ID | 部门ID | 销售额 | |---|---|---| | 1 | A | 100 | | 2 | A | 150 | | 3 | B | 200 | | 4 | A | 50 |

如果用传统聚合：

SELECT 部门ID, SUM(销售额) FROM 销售表 GROUP BY 部门ID;

结果： | 部门ID | SUM(销售额) | |---|---| | A | 300 | | B | 200 |

如果用窗口函数计算部门内累计销售额：

SELECT 订单ID, 部门ID, 销售额, SUM(销售额) OVER (PARTITION BY 部门ID ORDER BY 订单ID) AS 部门累计销售额 FROM 销售表;

结果： | 订单ID | 部门ID | 销售额 | 部门累计销售额 | |---|---|---|---| | 1 | A | 100 | 100 | | 2 | A | 150 | 250 | | 4 | A | 50 | 300 | | 3 | B | 200 | 200 |

看，每一行都还在，但又多了一个有用的分析字段。

深入理解RANK()、DENSE_RANK()和ROW_NUMBER()：何时使用它们？

这三个函数是窗口函数家族中最常用的“排名”函数，但它们处理“并列”情况的方式各不相同，因此适用场景也不同。我常常觉得，理解它们的核心就在于你如何看待并列名次。

ROW_NUMBER()

这个函数是最直接的。它为分区中的每一行分配一个唯一的、连续的序号，从1开始。 特点： 绝不会出现并列。即使两行在排序条件上完全相同，它们也会得到不同的

ROW_NUMBER

。至于哪一行先得到较小的数字，取决于数据库内部的物理存储顺序或者未指定的排序规则。 何时使用：

获取每个分组的“第N个”记录： 比如，每个用户最新的订单，或者每个产品线的第一个销售记录。
去重： 当你有多条完全相同的记录，或者想保留某个分组中符合特定条件的“唯一”记录时，可以先用
```
ROW_NUMBER()
```
排序，然后只保留
```
rn = 1
```
的记录。
分页： 在某些分页逻辑中，
```
ROW_NUMBER()
```
结合子查询或CTE可以很方便地实现。

示例：

AI Surge Cloud

低代码数据分析平台，帮助企业快速交付深度数据

87 查看详情 AI Surge Cloud

SELECT
    product_id,
    sale_date,
    sale_amount,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date DESC) AS rn
FROM
    sales_data;

这会给每个产品的销售记录按日期倒序编号，最新的销售记录

rn

为1。

RANK()

RANK()

函数为分区中的行分配排名。如果有多行在排序条件上具有相同的值，它们会得到相同的排名。但请注意，下一个不同的值会跳过相应数量的排名。 特点： 有并列，且并列后的排名会有“跳跃”。比如，1, 2, 2, 4（如果两个并列是第2名，那么下一个名次就是第4名）。 何时使用：

标准竞争排名： 比如，学生考试成绩排名，如果两个人并列第二，下一个人的名次就是第四。
找出前N名（允许并列）： 当你想要前N名，并且并列的也算在内，但又希望名次有跳跃感时。

示例：

SELECT
    student_id,
    score,
    RANK() OVER (ORDER BY score DESC) AS student_rank
FROM
    exam_results;

如果两个学生都考了90分，他们可能都得到

rank = 2

，而下一个学生如果考88分，他的

rank

就会是

。

DENSE_RANK()

DENSE_RANK()

函数也为分区中的行分配排名，与

RANK()

类似，并列的行会得到相同的排名。但它与

RANK()

的关键区别在于，并列后的排名是连续的，不会有跳跃。 特点： 有并列，但并列后的排名是“紧密的”，没有跳跃。比如，1, 2, 2, 3。 何时使用：

紧凑型排名： 当你希望排名是连续的，即使有并列，下一个名次也紧随其后时。比如，奖牌榜，金牌、银牌、铜牌，即使有多个并列银牌，下一枚依然是铜牌，而不是跳过。
分组分层： 将数据按某个指标分成几个等级，每个等级对应一个连续的数字。

示例：

SELECT
    product_category,
    sales_amount,
    DENSE_RANK() OVER (PARTITION BY product_category ORDER BY sales_amount DESC) AS category_sales_rank
FROM
    product_sales;

这会给每个产品类别内的销售额进行排名。如果两个产品销售额并列第一，它们都得到

rank = 1

，下一个销售额的产品就会得到

rank = 2

。

总的来说，选择哪个函数取决于你对“并列”的业务理解和排名需求的精确定义。

窗口函数在实际业务场景中的高级应用与性能考量

窗口函数远不止排名这么简单，它们在实际业务中有着极其广泛且强大的应用，有时候我甚至觉得它们是SQL分析能力的“核武器”。

高级应用示例：

计算累计总和 (Running Totals)： 比如，计算每天的累计销售额。

SELECT
    sale_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM
    daily_sales_report;

这里的

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

定义了窗口框架，表示从分区开始到当前行。

计算移动平均 (Moving Averages)： 比如，计算过去7天的平均销售额，用于趋势分析。

SELECT
    sale_date,
    daily_sales,
    *G(daily_sales) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_moving_*g
FROM
    daily_sales_report;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

表示窗口包含当前行和它之前的6行。

比较当前行与前/后一行 (LAG/LEAD)： 比如，计算相邻两次交易之间的时间间隔，或者与前一天的销售额进行比较。

SELECT
    order_id,
    customer_id,
    order_date,
    LAG(order_date, 1, NULL) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_date,
    DATEDIFF(day, LAG(order_date, 1, NULL) OVER (PARTITION BY customer_id ORDER BY order_date), order_date) AS days_since_last_order
FROM
    customer_orders;

LAG(order_date, 1, NULL)

获取前一行（偏移量为1）的

order_date

，如果没有前一行则返回

NULL

。

查找每个分组的最高/最低值 (FIRST_VALUE/LAST_VALUE)： 比如，找出每个部门销售额最高的员工姓名。

SELECT
    department_id,
    employee_name,
    sales_amount,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department_id ORDER BY sales_amount DESC) AS top_seller_in_dept
FROM
    employee_sales;

性能考量：

窗口函数虽然强大，但并非没有代价。它们在处理大数据集时，可能会带来显著的性能开销。

排序成本：
```
OVER()
```
子句中的
```
ORDER BY
```
操作是性能瓶颈的主要来源。数据库需要对数据进行排序才能执行窗口计算。如果
```
PARTITION BY
```
子句将数据分成大量小分区，或者分区内的数据量巨大，排序的开销就会很高。
内存消耗： 尤其是在
```
ORDER BY
```
子句中没有
```
PARTITION BY
```
，或者
```
PARTITION BY
```
只分成了少数几个大分区时，整个数据集可能需要在内存中进行排序，这会消耗大量内存。如果数据量超出内存，数据库会使用磁盘进行溢出排序，导致I/O操作增加，性能急剧下降。
索引利用： 确保
```
PARTITION BY
```
和
```
ORDER BY
```
子句中使用的列有合适的索引。这将大大加速数据的分区和排序过程。一个复合索引，例如
```
(partition_column, order_column)
```
，通常效果最佳。
窗口框架的选择：
```
ROWS
```
或
```
RANGE
```
子句定义了窗口的范围。如果窗口范围很小（比如
```
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
```
），数据库可能能更有效地处理。而
```
UNBOUNDED PRECEDING
```
或
```
UNBOUNDED FOLLOWING
```
则意味着窗口可能包含整个分区，计算量更大。
避免不必要的窗口函数： 如果一个简单的
```
GROUP BY
```
或子查询就能满足需求，就不要强行使用窗口函数。虽然它们很酷，但不是万能药。

在实际项目中，我通常会先用窗口函数写出逻辑清晰的查询，然后在测试环境用真实数据量进行性能测试。如果发现性能瓶颈，我会检查索引、调整窗口框架，甚至考虑是否可以通过分阶段处理（比如先聚合部分数据，再应用窗口函数）来优化。性能优化是一个迭代的过程，没有一劳永逸的解决方案。

以上就是SQL中的窗口函数是什么？RANK、ROW_NUMBER等详解的详细内容，更多请关注其它相关文章！

上一篇：什么是SQL注入的编码绕过？如何实现安全的编码验证

下一篇：SQL注入的测试方法有哪些？安全测试的最佳实践

SQL中的窗口函数是什么？RANK、ROW_NUMBER等详解

发布时间：2025-09-04

点击量：

解决方案

窗口函数与传统聚合函数有何本质区别，为何选择它们？

深入理解RANK()、DENSE_RANK()和ROW_NUMBER()：何时使用它们？

ROW_NUMBER()

RANK()

DENSE_RANK()

窗口函数在实际业务场景中的高级应用与性能考量

高级应用示例：

性能考量：

返回

4008988990