MySQL高级函数之：`RANK()` 和 `DENSE_RANK()`：其在排名时的跳跃与连续性区别。

MySQL高级函数：RANK() 和 DENSE_RANK() – 排名中的跳跃与连续性

各位朋友，大家好！今天我们来深入探讨MySQL中的两个窗口函数：RANK() 和 DENSE_RANK()。这两个函数都用于对数据进行排名，但在处理相同值时的行为有所不同，这导致它们在排名结果中呈现出跳跃性或连续性的差异。理解这些差异对于在SQL查询中获得准确和有意义的排名至关重要。

一、窗口函数概述

在深入研究RANK()和DENSE_RANK()之前，我们先简单回顾一下窗口函数的概念。窗口函数允许我们对与当前行相关的行集合（称为窗口）执行计算，而无需使用GROUP BY子句。这意味着我们可以访问其他行的数据，同时保留当前行的详细信息。

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

function_name() OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list [ASC | DESC]]
)

• function_name(): 要执行的窗口函数，例如RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), AVG() 等。
• PARTITION BY column_list: 将结果集划分为多个分区。窗口函数将分别应用于每个分区。
• ORDER BY column_list: 定义每个分区内行的顺序。这对于排名函数至关重要。

二、RANK() 函数：跳跃式排名

RANK()函数为结果集中的每一行分配一个排名。如果存在相同的行（基于ORDER BY子句），它们将获得相同的排名。然而，下一个排名将会跳过被共享的排名数量。换句话说，如果两行并列第二，则下一行的排名将是第四，而不是第三。

让我们通过一个例子来说明这一点。假设我们有一个名为 employees 的表，其中包含员工的姓名（name）和薪水（salary）。

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (name, salary) VALUES
('Alice', 60000.00),
('Bob', 75000.00),
('Charlie', 75000.00),
('David', 80000.00),
('Eve', 90000.00),
('Frank', 90000.00),
('Grace', 90000.00),
('Henry', 100000.00);

现在，我们使用RANK()函数根据薪水对员工进行排名：

SELECT
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM
    employees;

查询结果如下：

name	salary	salary_rank
Henry	100000.00	1
Eve	90000.00	2
Frank	90000.00	2
Grace	90000.00	2
David	80000.00	5
Bob	75000.00	6
Charlie	75000.00	6
Alice	60000.00	8

可以看到，Eve、Frank和Grace的薪水相同，都被赋予了排名2。但是，由于有三个人共享排名2，下一个排名跳到了5，而不是3。

三、DENSE_RANK() 函数：连续式排名

与RANK()函数不同，DENSE_RANK()函数在处理相同值时不会跳过排名。它会为每个不同的值分配一个连续的排名。这意味着即使存在并列，下一个排名仍然是紧随其后的下一个可用排名。

让我们再次使用employees表，这次使用DENSE_RANK()函数：

SELECT
    name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_dense_rank
FROM
    employees;

查询结果如下：

name	salary	salary_dense_rank
Henry	100000.00	1
Eve	90000.00	2
Frank	90000.00	2
Grace	90000.00	2
David	80000.00	3
Bob	75000.00	4
Charlie	75000.00	4
Alice	60000.00	5

可以看到，Eve、Frank和Grace的薪水相同，都被赋予了排名2。由于使用了DENSE_RANK()，下一个排名是3，而不是跳跃的5。

四、RANK() vs. DENSE_RANK()：关键区别总结

特性	RANK()	DENSE_RANK()
排名方式	跳跃式排名	连续式排名
并列处理	相同值共享相同排名，下一个排名跳过并列数量	相同值共享相同排名，下一个排名紧随其后
排名数量	排名数量可能大于总行数	排名数量等于不同值的数量，小于等于总行数

五、实际应用场景

了解RANK()和DENSE_RANK()的区别后，我们就可以根据具体需求选择合适的函数。

• RANK() 的应用场景：

  • 体育比赛排名： 假设一个比赛中有多人并列第一名，那么RANK()可以正确反映出下一个名次的跳跃，例如，如果三人并列第一，那么下一个名次将是第四名。
  • 奖金分配： 如果奖金只奖励前几名，并且并列名次会共享奖金，那么RANK()可以用于确定奖金范围，虽然实际获奖人数可能超过预定人数。

• DENSE_RANK() 的应用场景：

  • 网页搜索结果排名： 搜索引擎希望呈现连续的排名，即使某些搜索结果的相关性相同。使用DENSE_RANK()可以确保排名是连续的，不会出现跳跃。
  • 学术论文引用排名： 按照论文的引用次数进行排名时，可能希望看到连续的排名，以便了解论文在学术界的影响力。
  • 电商网站商品分类排名： 根据销售额对商品进行排名时，如果希望看到每个分类下的连续排名，可以使用DENSE_RANK()。

六、结合 PARTITION BY 子句的使用

RANK()和DENSE_RANK()函数通常与PARTITION BY子句结合使用，以便在不同的分区内进行排名。例如，我们可以根据部门对员工的薪水进行排名。

SELECT
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS department_salary_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS department_salary_dense_rank
FROM
    employees_with_department;  --  假设我们有一个名为 employees_with_department 的表，包含 department 列

在这个例子中，RANK()和DENSE_RANK()函数将分别应用于每个部门，从而得到每个部门内员工的薪水排名。

为了演示，我们先创建并填充employees_with_department表：

CREATE TABLE employees_with_department (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    salary DECIMAL(10, 2),
    department VARCHAR(255)
);

INSERT INTO employees_with_department (name, salary, department) VALUES
('Alice', 60000.00, 'Sales'),
('Bob', 75000.00, 'Sales'),
('Charlie', 75000.00, 'Sales'),
('David', 80000.00, 'Marketing'),
('Eve', 90000.00, 'Marketing'),
('Frank', 90000.00, 'Marketing'),
('Grace', 90000.00, 'HR'),
('Henry', 100000.00, 'HR'),
('Ivy', 85000.00, 'HR');

然后执行上面的查询：

SELECT
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS department_salary_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS department_salary_dense_rank
FROM
    employees_with_department;

查询结果如下：

department	name	salary	department_salary_rank	department_salary_dense_rank
HR	Henry	100000.00	1	1
HR	Ivy	85000.00	2	2
HR	Grace	90000.00	3	3
Marketing	Eve	90000.00	1	1
Marketing	Frank	90000.00	1	1
Marketing	David	80000.00	3	2
Sales	Bob	75000.00	1	1
Sales	Charlie	75000.00	1	1
Sales	Alice	60000.00	3	2

可以看到，每个部门内的员工都根据薪水进行了排名，并且RANK()和DENSE_RANK()在每个部门内的行为都与我们之前的讨论一致。

七、注意事项

• RANK()和DENSE_RANK()函数只能在SELECT语句中使用，并且通常在OVER()子句中使用。
• ORDER BY子句在OVER()子句中是强制性的，因为它定义了排名的顺序。
• PARTITION BY子句是可选的，它可以将结果集划分为多个分区，以便在每个分区内进行排名.
• 如果在没有PARTITION BY子句的情况下使用RANK()或DENSE_RANK()，则整个结果集将被视为一个分区。
• 在选择使用RANK()还是DENSE_RANK()时，请仔细考虑您的具体需求，并选择最能满足您需求的函数。理解跳跃式排名和连续式排名的区别至关重要。

八、更多窗口函数

除了RANK()和DENSE_RANK()之外，MySQL还提供了其他有用的窗口函数，包括：

• ROW_NUMBER(): 为结果集中的每一行分配一个唯一的序列号，从1开始。即使存在相同的行，ROW_NUMBER()也会为它们分配不同的序列号。
• NTILE(n): 将结果集划分为n个大致相等的部分，并为每个部分分配一个桶号。
• LAG(column, n, default_value): 访问当前行之前第n行的column值。如果不存在这样的行，则返回default_value。
• LEAD(column, n, default_value): 访问当前行之后第n行的column值。如果不存在这样的行，则返回default_value。
• 聚合函数 (SUM, AVG, MIN, MAX, COUNT): 聚合函数也可以用作窗口函数，以便在窗口内计算聚合值。

掌握这些窗口函数可以大大提高SQL查询的灵活性和表达能力。

九、总结：选择合适的排名方式

RANK()和DENSE_RANK()是强大的窗口函数，用于对数据进行排名。 RANK()产生跳跃式排名，而DENSE_RANK()产生连续式排名。 根据你的具体需求，选择合适的排名方式至关重要，可以更好地反映数据的特征，实现更准确的分析。