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

窗口函数是在不聚合行的前提下，基于“窗口”内相关行对每行数据进行计算的强大工具，其核心是OVER()子句定义的窗口范围。与传统聚合函数（如SUM、AVG配合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等就是这类函数中的佼佼者，它们让复杂的数据分析变得前所未有的简单和高效。

解决方案

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

OVER()

GROUP BY

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

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

这里的

PARTITION BY

ORDER BY

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

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

SUM()

AVG()

COUNT()

GROUP BY

GROUP BY department_id

SUM(sales)

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

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

1. 保留细节，增加上下文： 你不需要为了聚合而丢失原始数据。这是它最大的魅力。
2. 简化复杂查询： 以前需要通过子查询、自连接甚至多次查询才能实现的功能，现在一个窗口函数就能搞定。比如，找出每个部门销售额最高的员工，没有窗口函数的话，你可能需要先找出每个部门的最高销售额，再连接回原表找出对应的员工。有了

   RANK()

   登录后复制
   或

   ROW_NUMBER()

   登录后复制
   ，这变得异常简单。
3. 性能优化： 很多情况下，数据库引擎能更高效地处理窗口函数，因为它只需要一次数据扫描和排序，而复杂的子查询或自连接可能会导致多次扫描和连接操作。当然，这也不是绝对的，具体还得看查询优化器和数据量。

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

如果用传统聚合：

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

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

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

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

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

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

ROW_NUMBER()

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

ROW_NUMBER

• 获取每个分组的“第N个”记录： 比如，每个用户最新的订单，或者每个产品线的第一个销售记录。
• 去重： 当你有多条完全相同的记录，或者想保留某个分组中符合特定条件的“唯一”记录时，可以先用

  ROW_NUMBER()

  登录后复制
  排序，然后只保留

  rn = 1

  登录后复制
  的记录。
• 分页： 在某些分页逻辑中，

  ROW_NUMBER()

  登录后复制
  结合子查询或CTE可以很方便地实现。

示例：

阿里云-虚拟数字人

阿里云-虚拟数字人是什么？ ...

2

查看详情

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

RANK()

RANK()

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

示例：

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

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

rank = 2

rank

4

DENSE_RANK()

DENSE_RANK()

RANK()

RANK()

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

示例：

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分析能力的“核武器”。

高级应用示例：

1. 计算累计总和 (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

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

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

   SELECT
       sale_date,
       daily_sales,
       AVG(daily_sales) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_moving_avg
   FROM
       daily_sales_report;

   登录后复制

   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

   登录后复制
   表示窗口包含当前行和它之前的6行。

3. 比较当前行与前/后一行 (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

   登录后复制
   。

4. 查找每个分组的最高/最低值 (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等详解的详细内容，更多请关注php中文网其它相关文章！