SQL窗口函数的魔法：ROW_NUMBER()、RANK()与DENSE_RANK()的奇妙世界

SQL窗口函数的魔法：ROW_NUMBER()、RANK()与DENSE_RANK()的奇妙世界

在SQL的世界里，窗口函数如同魔法师手中的法杖，能够以惊人的效率处理复杂的数据计算。今天，我们将深入探讨其中的三位明星：ROW_NUMBER()、RANK()和DENSE_RANK()。这些函数不仅能够帮助你轻松实现数据排名，还能让你的SQL查询更加高效和优雅。

前置知识

在开始我们的魔法之旅前，你需要了解一些基础知识：

1. SQL基础：熟悉基本的SQL查询语法，如SELECT、FROM、WHERE等。
2. 窗口函数概念：了解窗口函数的基本概念，知道它们与普通聚合函数的区别。

窗口函数的魔力

窗口函数（Window Functions）是一种能够在查询结果集中执行复杂计算的强大工具。它们与普通聚合函数的区别在于，窗口函数不会将多行数据合并成一行，而是为每一行生成一个结果。

ROW_NUMBER()：行号的赋予者

ROW_NUMBER()函数为结果集的分区中的每一行分配一个唯一的行号，行号从1开始，依次递增。

示例代码

SELECT 
    id, 
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM 
    Scores;

解释

• ROW_NUMBER() OVER (ORDER BY score DESC)：按score降序排列，并为每一行分配一个行号。

RANK()：排名的守护者

RANK()函数为结果集的分区中的每一行分配一个排名。如果两个或多个行具有相同的值，它们将获得相同的排名，并且下一个排名将跳过相应的数字。

示例代码

SELECT 
    id, 
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM 
    Scores;

解释

• RANK() OVER (ORDER BY score DESC)：按score降序排列，并为每一行分配一个排名。如果存在并列，排名将跳过相应的数字。

DENSE_RANK()：连续排名的使者

DENSE_RANK()函数与RANK()类似，但它生成的排名是连续的，即使存在并列，下一个排名也不会跳过数字。

示例代码

SELECT 
    id, 
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM 
    Scores;

解释

• DENSE_RANK() OVER (ORDER BY score DESC)：按score降序排列，并为每一行分配一个连续的排名。

实战演练

假设我们有一个名为Scores的表，结构如下：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+

我们希望对分数进行排名，并比较三种窗口函数的效果。

示例数据

+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

查询结果

ROW_NUMBER()

+----+-------+---------+
| id | score | row_num |
+----+-------+---------+
| 3  | 4.00  | 1       |
| 5  | 4.00  | 2       |
| 4  | 3.85  | 3       |
| 2  | 3.65  | 4       |
| 6  | 3.65  | 5       |
| 1  | 3.50  | 6       |
+----+-------+---------+

RANK()

+----+-------+------+
| id | score | rank |
+----+-------+------+
| 3  | 4.00  | 1    |
| 5  | 4.00  | 1    |
| 4  | 3.85  | 3    |
| 2  | 3.65  | 4    |
| 6  | 3.65  | 4    |
| 1  | 3.50  | 6    |
+----+-------+------+

DENSE_RANK()

+----+-------+----------+
| id | score | dense_rank |
+----+-------+----------+
| 3  | 4.00  | 1         |
| 5  | 4.00  | 1         |
| 4  | 3.85  | 2         |
| 2  | 3.65  | 3         |
| 6  | 3.65  | 3         |
| 1  | 3.50  | 4         |
+----+-------+----------+

总结

通过本文的探索，我们深入了解了ROW_NUMBER()、RANK()和DENSE_RANK()这三个窗口函数的魔力。它们各自有着独特的用途和特点，能够帮助我们高效地处理数据排名问题。无论你是SQL新手还是老手，掌握这些窗口函数都将使你的数据处理能力更上一层楼。

希望这篇博客能够激发你对SQL窗口函数的兴趣，并在实际工作中为你带来便利。如果你有任何问题或需要进一步的解释，请随时留言。让我们一起在SQL的魔法世界中探索更多可能！