MySQL 排名函数

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

它们始终与 OVER() 一起使用
它们根据 ORDER BY 为每一行赋予一个排名
它们会按照顺序为每一行赋予一个排名
它们始终为每个新分区的行赋予一个排名，以一为起始

注意：需要注意的是，MySQL自8.0版本起提供了对排名和窗口函数的支持。

MySQL 支持以下三种类型的排名函数：

稠密排名
排名
百分位排名

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

MySQL 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()函数如何工作。因此，首先创建一个包含以下数据的表：

表：employees

MySQL 排名函数

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

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

执行以上语句后，我们将获得以下输出：

MySQL 排名函数

示例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 排名函数

MySQL的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子句适用于每个分区，根据一个或多个列名对行进行排序。

让我们拿一个之前创建的表，并通过不同的示例来查看MySQL中rank()函数的工作方式。

表：employees

MySQL 排名函数

示例 1

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

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

以上查询将产生以下输出：

MySQL 排名函数

示例2

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

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

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

MySQL 排名函数

MySQL 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

MySQL 排名函数

示例 1

此语句使用percent_rank()函数来计算每行按照marks列排序的排名值。

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

以上查询将会产生以下输出：

MySQL 排名函数

看看上面的公式如何工作，请考虑以下查询：

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;

它将产生以下输出：

MySQL 排名函数