MySQL 排名函数

MySQL 使用排名函数，允许我们对数据库中分区的每一行进行排名。排名函数也是 MySQL 中窗口函数的子部分。MySQL 中的排名函数可以与以下子句一起使用：

• 他们总是使用OVER()
• 他们根据ORDER BY为每一行分配一个排名
• 他们按顺序为每一行分配一个等级。
• 他们总是为行分配一个等级，从每个新分区的一个开始。

注意：需要注意的是 MySQL 从 8.0 版本开始支持排名和窗口函数。 

MySQL支持以下三种排序函数：

1. Dense Rank
2. Rank
3. Percent Rank

现在，我们将详细讨论每个排名函数：

一、MySQL dense_rank() 函数

dense_rank() 函数是一个为分区或结果集中的每一行分配一个排名的函数，没有任何间隙。行的排名总是按连续顺序分配（从前一行增加一个）。有时您会在值之间获得平局，然后 dense_rank 将为其分配相同的排名，其下一个排名将是其下一个连续数字。

以下是 dense_rank() 的语法：

SELECT column_name   
DENSE_RANK() OVER (  
    PARTITION BY expression  
    ORDER BY expression [ASC|DESC])  
AS 'my_rank' FROM table_name;  

在上述语法中，PARTITION BY 子句对 FROM 子句返回的结果集进行分区，然后对每个分区应用dense_rank 函数。接下来，ORDER BY 子句适用于每个分区以指定行的顺序。

示例 1

让我们了解 MySQL 的 dense_rank() 函数是如何工作的。因此，首先，创建一个包含以下数据的表：

employee表：

此语句使用 dense_rank() 函数为每一行分配排名值。

SELECT emp_id, emp_name, city, emp_age,  
DENSE_RANK() OVER (ORDER BY emp_age) dens_rank  
FROM employees;  

执行上述语句后，我们会得到如下输出：

示例 2

让我们看另一个将结果集划分为分区的示例。以下语句使用 dense_rank() 函数为每一行分配值，并使用emp_age将结果集划分为分区：

SELECT emp_id, emp_name, city, emp_age,  
DENSE_RANK() OVER (PARTITION BY emp_age ORDER BY city) dens_rank  
FROM employees;  

上述查询成功执行后，我们会得到如下输出：

二、MySQL rank() 函数

rank() 函数是一个为分区或结果集中有间隙的每一行分配排名的函数。行的排名总是不按连续顺序分配（即，从前一行增加一个）。有时您会在值之间获得平局，然后 rank() 函数将为它分配相同的排名，下一个排名值将是它的前一个排名加上一些重复的数字。

以下是 rank() 的语法：

SELECT column_name   
RANK() OVER (  
    PARTITION BY expression  
    ORDER BY expression [ASC|DESC])  
AS 'my_rank' FROM table_name;  

在上述语法中，PARTITION BY 子句对FROM 子句返回的结果集进行分区, 然后 rank() 函数应用于每个分区，并在分区边界跨越其他分区时重新初始化。接下来，ORDER BY 子句应用于每个分区，以根据一个或多个列名称对行进行排序。

employee表：

示例 1

此语句使用 rank() 函数为每一行分配排名值。

SELECT emp_id, emp_name, city, emp_age,  
RANK() OVER (ORDER BY emp_age) my_rank  
FROM employees;  

上面的查询将给出以下输出：

示例 2

让我们看另一个将结果集划分为分区的示例。以下语句使用 rank() 函数为每一行分配值，并使用emp_age将结果集划分为分区，并根据emp_id对它们进行排序：

SELECT *,  
RANK() OVER (PARTITION BY emp_age ORDER BY emp_id) my_rank  
FROM employees;  

执行上面的语句，我们会得到如下输出：

三、MySQL percent_rank() 函数

percent_rank() 函数是一个计算分区或结果集中的行的百分等级（相对等级）的函数。此函数从 0 到 1 之间的值范围内返回一个数字。

以下是 percent_rank() 的语法：

SELECT column_name   
PERCENT_RANK() OVER (  
    PARTITION BY expression  
    ORDER BY expression [ASC|DESC])  
AS 'my_rank' FROM table_name;  

对于指定的行，此函数使用以下公式计算排名：

(rank-1) / ( total_rows-1)  

参数说明：

rank：是rank()函数返回的每一行的排名。

total_rows：它表示分区中存在的总行数。

注意：这是为了确保当您使用此功能时，您必须使用 ORDER BY 子句。否则，所有行都被视为重复并分配相同的等级，即 1。

让我们创建一个包含以下数据的表“students”，并查看 MySQL 中 percent_rank() 函数的工作情况。

students表：

示例 1

此语句使用 percent_rank() 函数计算每个行 order by 标记列的排名值。

SELECT stud_id, stud_name, subject, marks,  
PERCENT_RANK() OVER (PARTITION BY subject ORDER BY marks) my_rank  
FROM students;  

上面的查询将给出以下输出：

要查看上述公式的工作原理，请考虑以下查询：

SELECT stud_id, stud_name, subject, marks, rank()   
OVER ( partition by subject order by marks )-1   
AS 'rank-1', count(*) over (partition by subject)-1  
AS 'total_rows-1',   
PERCENT_RANK() OVER (PARTITION BY subject ORDER BY marks) my_rank  
FROM students;  

它将给出以下输出：