Mysql排名窗口函数 RANK(),DENSE_RANK()

Mysql排名窗口函数 RANK DENSE_RANK

      • 先准备基础数据
      • DENSE_RANK()
      • RANK()
      • 总结：

先准备基础数据

创建一个students表，主键id，字段score

create table students
(
    id    bigint not null
        primary key,
    score double null
);

-- 插入几条数据
INSERT INTO `students`(`id`, `score`) VALUES (1, 10);
INSERT INTO `students`(`id`, `score`) VALUES (2, 30);
INSERT INTO `students`(`id`, `score`) VALUES (3, 20);
INSERT INTO `students`(`id`, `score`) VALUES (4, 20);
INSERT INTO `students`(`id`, `score`) VALUES (5, 50);
INSERT INTO `students`(`id`, `score`) VALUES (6, 30);
INSERT INTO `students`(`id`, `score`) VALUES (7, 50);
INSERT INTO `students`(`id`, `score`) VALUES (8, 90);
INSERT INTO `students`(`id`, `score`) VALUES (9, 50);

最终表数据如下:
在这里插入图片描述

DENSE_RANK()

DENSE_RANK()是一个窗口函数，用于根据指定排序规则为每一行分配一个连续的排名值，即使有相同的排名也不会出现间隙。也就是说，如果有两行数据具有相同等级，则它们都会得到相同的排名，并且下一行的排名将紧跟在最高相同排名之后。
语法结构如下：

DENSE_RANK() OVER (
    [PARTITION BY column1, column2, ... ]
    ORDER BY order_column1 ASC|DESC, order_column2 ASC|DESC, ...
)

  • PARTITION BY：可选子句，用于将数据集分割成多个分区，在每个分区内部独立计算排名。

  • ORDER BY：指定用于确定排名顺序的列或表达式。默认是升序排列，也可以指定为降序排列。

例如示例中的students表，包含学生的id和scores字段，想基于分数对所有学生进行连续排名：

SELECT
	DENSE_RANK() OVER ( ORDER BY score DESC ) custom_rank,
	id,
	score 
FROM
	students

查询结果：
在这里插入图片描述

这样就返回每名学生的ID、分数以及基于分数从高到低的连续排名。如果两个或更多的学生得分相同，他们将获得相同的排名，且下一个不同分数的学生的排名不会因为之前的并列而跳跃。

RANK()

MySQL中的RANK()函数是一个窗口函数，用于在一组行（通常是在PARTITION BY子句定义的分区中）中为每一行分配一个唯一的排名值。该排名基于指定的排序条件，对于相同排序值的行，RANK()函数会赋予相同的排名，并且下一个不同的值将跳过相同的排名数，形成非连续的排名。

具体语法结构如下：

SELECT 
    student_id, class, score,
    RANK() OVER (
        PARTITION BY class
        ORDER BY score DESC
    ) as rank_in_class
FROM 
    student_scores;

  • PARTITION BY（可选）：用于将数据分割成多个分区，在每个分区内部进行独立排名。

  • ORDER BY：用于确定在每个分区内部如何排序行，这将决定排名的依据。

  • RANK()：窗口函数，它会根据ORDER BY子句定义的顺序给每行分配一个唯一的排名。

以students表为例，想查询每个学生的分数进行降序排名，可以这样写查询：

SELECT
	RANK() OVER ( ORDER BY score DESC ) custom_rank,
	id,
	score 
FROM
	students

查询结果：
在这里插入图片描述
在这个例子中，RANK()函数将会为每个班级内的学生按分数从高到低分配一个排名，如果有分数相同的学生，则他们的排名也会相同，但后续学生的排名将会跳跃（比如如果第一名有两个，则第二名将是第三名）。

总结：

RANK() 和 DENSE_RANK() 都是窗口函数，都是用于在一组数据内按照指定排序条件为每一行提供一个排名。但它们之间的关键区别在于当存在相等排序值时，后续排名的处理方式：

  1. RANK() 函数

  • 当有多行具有相同的排序值时，RANK() 函数会给这些行赋予相同的排名，但会导致后续行的排名出现跳跃。

  • 例如，上方示例中又3个50分，之后才是30分，RANK() 将分别为它们分配排名2和5，跳过了排名 3，4。

  1. DENSE_RANK() 函数

  • 同样当有多行具有相同的排序值时，DENSE_RANK() 函数也会赋予相同的排名，但不同的是，它不会跳过任何排名，而是让后续行的排名紧随其后。

  • 对于上述相同的例子，如果使用 DENSE_RANK()，则三行成绩将分别得到排名1和3没有排名间的跳跃。

总结起来：

  • RANK() 会产生非连续的排名（即有空缺的排名）。

  • DENSE_RANK() 则始终保持排名的连续性，即使在相同值的情况下也不会产生排名空缺。