MySQL：MySQL分组排序函数rank()、row_number()、dense_rank()与partition by结合使用

一、前言

  在 MySQL 中，虽然标准的 SQL 函数 RANK(), ROW_NUMBER(), 和 DENSE_RANK() 是 SQL 标准的一部分，但早期的 MySQL 版本并不直接支持这些窗口函数。然而，从 MySQL 8.0 开始，这些函数被引入以支持窗口函数（也称为分析函数或 OLAP 函数）。

二、示例

以下是这三个函数的简要说明和示例：

1.ROW_NUMBER()

• 为结果集的每一行分配一个唯一的序号。
• 即使两行有相同的值，它们也会被赋予不同的序号。

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) as rn  
FROM students;

这将按分数降序排列学生，并为每个学生分配一个唯一的序号。

2. RANK()

• 为结果集的每一行分配一个排名。
• 如果两行有相同的值，则它们具有相同的排名，并且下一个排名的数字会跳过这些重复的值。

SELECT name, score, RANK() OVER (ORDER BY score DESC) as rnk  
FROM students;

假设有两个学生都有 90 分的最高分，则它们的排名都是 1，而下一个学生的排名将是 3（跳过了 2）。

3. DENSE_RANK()

• 与 RANK() 类似，但它不会跳过任何排名数字。
• 如果两行有相同的值，则它们具有相同的排名，但下一个排名的数字会紧接着前一个排名的数字。

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) as drnk  
FROM students;

使用上面的示例，如果两个学生都有 90 分的最高分，则它们的排名都是 1，而下一个学生的排名将是 2（没有跳过 2）。

  在所有这些示例中，OVER (ORDER BY …) 子句定义了如何对结果集进行排序以确定排名或序号。你可以根据需要更改排序顺序（例如，按升序排序而不是降序排序）。

4. PARTITION BY

  PARTITION BY子句允许你将一个表（或查询结果集）的数据按照指定的列或表达式进行逻辑分组，这些逻辑分组被称为“分区”。每个分区内的数据在物理存储上可能是独立的，但逻辑上它们仍然属于同一张表。

  RANK(), ROW_NUMBER(), 和 DENSE_RANK() 窗口函数可以与 PARTITION BY 子句结合使用，以在每个分区内单独计算排名。

SELECT name, score, class,  
       ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) as row_num  
FROM students;

在这个例子中，我们按 class 分区，并在每个班级内按 score 降序为学生分配唯一的行号。

注意：

• 使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。
• 可以这样：rank() over(partition by class order by score desc nulls last)

总结：

• 排名函数必须有 OVER 子句。

• 排名函数必须有包含 ORDER BY 的 OVER 子句。

• 分组内从1开始排序。

• rank中空值是最大的，要用 nulls last进行调整。