了解 SQL 窗口函数中的 ROW_NUMBER()

一.介绍

在本文中，我们将使用示例详细探讨 ROW_NUMBER()。窗口函数是 SQL 中的一个强大功能，它允许您对与当前行相关的一组行执行计算。最常用的窗口函数之一是 ROW_NUMBER()。

二.什么是窗口函数？

让我们首先了解什么是窗口函数以及它们如何工作。

窗口函数对与当前行有某种关联的一组表行执行计算。它们类似于聚合函数，但与聚合函数不同的是，它们不会导致行分组为单个输出行。

现在，让我们建立一个示例表来了解 ROW_NUMBER() 窗口函数。

-- Create a table of students
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    class INT,
    marks INT
);

-- Insert sample data
INSERT INTO students (student_id, name, class, marks) VALUES
(1, 'Aarav Patel', 10, 85),
(2, 'Diya Sharma', 10, 92),
(3, 'Arjun Singh', 10, 78),
(4, 'Ananya Gupta', 11, 88),
(5, 'Rohan Verma', 11, 95),
(6, 'Ishaan Kumar', 11, 82),
(7, 'Lokendra Singh', 12, 91),
(8, 'Vihaan Chopra', 12, 89),
(9, 'Aisha Bose', 12, 94);

三.理解 ROW_NUMBER()

ROW_NUMBER() 是一个窗口函数，它为结果集分区内的每一行分配一个唯一的整数值。编号从 1 开始，并随着每行的增加而递增。

四.ROW_NUMBER() 的基本语法

ROW_NUMBER() OVER (
    [PARTITION BY col1, col2, ...]
    [ORDER BY col1, col2, ...]
)

让我们分解一下上面的语法：

• **PARTITION BY（可选）：**此子句将结果集划分为多个分区。ROW_NUMBER() 分别应用于每个分区。
• **ORDER BY（可选）：**指定在每个分区内分配行号的顺序。

1. 基本 ROW_NUMBER()

SELECT
    name,
    class,
    marks,
    ROW_NUMBER() OVER (ORDER BY marks DESC) AS rank
FROM
    students;

/*
Output:

name            class   marks   rank
Rohan Verma     11      95      1
Aisha Bose      12      94      2
Diya Sharma     10      92      3
Lokendra Singh  12      91      4
Vihaan Chopra   12      89      5
Ananya Gupta    11      88      6
Aarav Patel     10      85      7
Ishaan Kumar    11      82      8
Arjun Singh     10      78      9

*/

在上面的查询中，ROW_NUMBER() 为每一行分配一个唯一的编号，并按标记降序排列。

2. ROW_NUMBER() 与 PARTITION BY

现在，让我们对班级内的学生进行排名：

SELECT
    name,
    class,
    marks,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY marks DESC) AS class_rank
FROM
students;

/* Output:

name            class   marks   class_rank
Diya Sharma     10      92      1
Aarav Patel     10      85      2
Arjun Singh     10      78      3
Rohan Verma     11      95      1
Ananya Gupta    11      88      2
Ishaan Kumar    11      82      3
Aisha Bose      12      94      1
Lokendra Singh  12      91      2
Vihaan Chopra   12      89      3

*/

五.ROW_NUMBER() 的用途

1. **排名：**正如我们的示例所示，ROW_NUMBER() 非常适合创建排名。
2. **删除重复项：**您可以使用 ROW_NUMBER() 来识别和删除重复的行。
3. 分页： ROW_NUMBER() 可用于在 SQL 查询中实现分页。
4. **每组前 N 个：**您可以使用 ROW_NUMBER() 从数据集中的每个组中选择前 N 行。

六.概括

ROW_NUMBER() 是一个多功能窗口函数，可以显著增强您的 SQL 查询。通过了解如何将 ROW_NUMBER() 与 PARTITION BY 和 ORDER BY 子句结合使用，您可以轻松解决复杂的排名和分析问题。请记住，像 ROW_NUMBER() 这样的窗口函数对一组行进行操作，并为每行返回一个值。这使得它们对于需要对一组相关行执行计算的分析查询非常有用。