如何使用窗口函数（如 ROW_NUMBER、RANK）？

[muskanislam99]

洞悉数据之窗：驾驭 SQL 窗口函数 ROW_NUMBER 和 RANK
在结构化查询语言（SQL）的强大工具箱中，窗口函数是一类特殊而强大的函数，它们能够跨越与当前行相关的**一组行（窗口）**执行计算，而不会像聚合函数那样导致行折叠。ROW_NUMBER() 和 RANK() 是两种最常用且功能强大的窗口函数，它们为结果集中的每一行分配一个基于指定排序的序号，从而实现复杂的排名和序列生成。理解和掌握这两种函数，能够极大地提升我们分析和呈现数据的能力。

窗口函数的概念与语法

与传统的聚合函数（如 COUNT(), SUM(), AVG()）不同，窗口函数不会将多行数据聚合为单行输出。相反，它们会为查询结果集的每一行计算一个值，这个值的计算基于与当前行相关的“窗口”中的数据。窗口的定义由 OVER() 子句指定。

窗口函数的基本语法如下：

SQL

window_function(arguments) OVER (
[PARTITION BY column1, column2, ...]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
[frame_clause]
)
window_function(arguments): 这是要使用的窗口函数，例如 ROW_NUMBER() 或 RANK()，以及传递给它的参数（如果需要）。
OVER(): 这个子句定义了窗口的规范。
PARTITION BY column1, column2, ... (可选): 这个子句将结果集划分为多个分区（窗口），窗口函数将在每个分区内独立执行。类似于 GROUP BY，但不会折叠行。
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ... (可选): 这个子句定义了每个分区内行的排序方式，窗口函数通常基于这个排序来计算结果。
frame_clause (可选): 这个子句定义了当前窗口的框架，即 BC 数据台湾 与当前行相关的行的子集。例如，可以指定计算基于当前行及其前一行或后几行的平均值。ROW_NUMBER() 和 RANK() 通常不需要显式的 frame_clause。
ROW_NUMBER()：生成唯一的连续序号

ROW_NUMBER() 函数用于为结果集（或每个分区内）的每一行分配一个唯一的、连续的整数序号，从 1 开始，并按照 OVER() 子句中 ORDER BY 指定的排序顺序进行编号。即使排序字段的值相同，ROW_NUMBER() 也会分配不同的序号。

示例：

假设我们有一个 employees 表，包含 employee_id, employee_name, 和 salary 列。要按照工资降序为每个员工分配一个排名序号：

SQL

SELECT
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_num
FROM
employees;
如果我们需要按照部门进行排名：

SQL

SELECT
department_id,
employee_name,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_within_dept
FROM
employees;
在这个例子中，PARTITION BY department_id 将员工按照部门分组，然后 ROW_NUMBER() 在每个部门内部按照工资降序分配唯一的排名序号。

RANK()：生成带并列的序号

RANK() 函数也用于为结果集（或每个分区内）的每一行分配一个排名序号，但与 ROW_NUMBER() 不同的是，RANK() 会为具有相同排序值的行分配相同的序号，并且下一个序号会跳过相应的名次数。

示例：

继续使用 employees 表，按照工资降序为每个员工分配排名，允许并列排名：

SQL

SELECT
employee_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM
employees;
如果存在多个工资相同的员工，他们将获得相同的 rank_num，而下一个不同的工资将获得跳过相应名次数的序号。例如，如果有两位员工工资并列第一，他们的 rank_num 都是 1，而下一个工资较低的员工的 rank_num 将是 3。

同样，结合 PARTITION BY 可以在每个分区内进行并列排名：

SQL

SELECT
department_id,
employee_name,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_within_dept
FROM
employees;
ROW_NUMBER() vs RANK() 的选择

选择使用 ROW_NUMBER() 还是 RANK() 取决于具体的排名需求：

如果需要为每一行分配一个唯一的、连续的序号，即使排序值相同，也需要区分开来，那么应该使用 ROW_NUMBER()。例如，获取每个分组的前 N 条记录。
如果需要按照排序值进行排名，并且允许相同的值具有相同的排名，同时希望后续的排名能够反映出并列的情况（跳过相应的名次数），那么应该使用 RANK()。例如，在体育比赛中显示选手名次。
实际应用场景

获取每个分类下销量最高的前 N 个产品。
为每个用户的操作记录添加序号，方便追踪。
在排行榜中显示用户的名次，允许并列。
在每个部门内找到工资最高的几名员工。
生成报表时，为每一行添加唯一的行号。
总结

ROW_NUMBER() 和 RANK() 是 SQL 窗口函数中用于生成序号和排名的重要工具。通过 OVER() 子句灵活地定义分区和排序规则，我们可以实现各种复杂的排名和序列生成需求，而无需使用传统的子查询或临时表，使得 SQL 查询更加简洁和高效。理解它们之间的区别以及适用场景，能够帮助我们更深入地分析和呈现数据，从而挖掘出更有价值的信息。