用法介绍MySQL Rank函数|极客笔记

用法介绍MySQL Rank函数

1. 简介

MySQL中的RANK()函数是一种窗口函数，用于计算表中数据的排名。它能够根据给定的排序方式（例如升序或降序）为每个行分配一个排名。

在实际应用中，RANK()函数常用于计算学生成绩排名、销售业绩排名等需要对数据进行排名的场景。本文将详细介绍RANK()函数的用法及实例，帮助读者更好地理解和应用该函数。

2. 语法

RANK()函数的语法如下：

RANK() OVER (
    [PARTITION BY partition_expr1, partition_expr2, ...]
    ORDER BY sort_expression [ASC | DESC], ...
)

其中，RANK()函数与OVER()子句一起使用。OVER()子句用于指定窗口的分析范围，可以通过PARTITION BY子句进行数据分区，通过ORDER BY子句进行排序。

RANK()函数不需要指定聚合函数，它会在窗口范围内为每个行计算排名值。

3. 示例

为了更好地理解和应用RANK()函数，下面我们将通过一些示例来进行详细介绍。

3.1 基本用法

首先，我们创建一个名为grades的表，该表包含学生的姓名和对应的分数：

CREATE TABLE grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50),
    score INT
);

INSERT INTO grades (student_name, score) VALUES ('Alice', 90);
INSERT INTO grades (student_name, score) VALUES ('Bob', 80);
INSERT INTO grades (student_name, score) VALUES ('Charlie', 95);
INSERT INTO grades (student_name, score) VALUES ('David', 85);
INSERT INTO grades (student_name, score) VALUES ('Emily', 90);

现在，我们需要计算学生的分数排名：

SELECT student_name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM grades;

执行以上SQL语句后，将得到以下结果：

+--------------+-------+------+
| student_name | score | rank |
+--------------+-------+------+
| Charlie      |    95 |    1 |
| Alice        |    90 |    2 |
| Emily        |    90 |    2 |
| David        |    85 |    4 |
| Bob          |    80 |    5 |
+--------------+-------+------+

通过RANK() OVER (ORDER BY score DESC)，我们按照分数的降序对学生进行排名。可以看到，Charlie在分数最高的位置，Alice和Emily并列排名第二，David排名第四，Bob排名第五。

3.2 分区排名

在某些情况下，我们可能需要在不同的分区内计算排名。例如，我们要按照不同的班级计算学生的排名。

假设我们有一个名为class_grades的表，包含了学生的姓名、班级和成绩：

CREATE TABLE class_grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class VARCHAR(10),
    student_name VARCHAR(50),
    score INT
);

INSERT INTO class_grades (class, student_name, score) VALUES ('Class A', 'Alice', 90);
INSERT INTO class_grades (class, student_name, score) VALUES ('Class A', 'Bob', 80);
INSERT INTO class_grades (class, student_name, score) VALUES ('Class A', 'Charlie', 95);
INSERT INTO class_grades (class, student_name, score) VALUES ('Class B', 'David', 85);
INSERT INTO class_grades (class, student_name, score) VALUES ('Class B', 'Emily', 90);

现在，我们要计算每个班级内学生的分数排名：

SELECT class, student_name, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank
FROM class_grades;

执行以上SQL语句后，将得到以下结果：

+---------+--------------+-------+------+
| class   | student_name | score | rank |
+---------+--------------+-------+------+
| Class A | Charlie      |    95 |    1 |
| Class A | Alice        |    90 |    2 |
| Class A | Bob          |    80 |    3 |
| Class B | Emily        |    90 |    1 |
| Class B | David        |    85 |    2 |
+---------+--------------+-------+------+

通过RANK() OVER (PARTITION BY class ORDER BY score DESC)，我们按照班级和分数的降序对学生进行排名。可以看到，Class A班级中，Charlie排名第一，Alice排名第二，Bob排名第三；Class B班级中，Emily排名第一，David排名第二。

3.3 平局处理

当有多个行具有相同的排名时，RANK()函数的默认行为是跳过接下来的排名。如果想要占用相同的排名位置，可以使用DENSE_RANK()函数。

假设我们有一个名为sales的表，包含了销售员的姓名和对应的销售额：

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salesman_name VARCHAR(50),
    sales_amount INT
);

INSERT INTO sales (salesman_name, sales_amount) VALUES ('Alice', 5000);
INSERT INTO sales (salesman_name, sales_amount) VALUES ('Bob', 3000);
INSERT INTO sales (salesman_name, sales_amount) VALUES ('Charlie', 5000);
INSERT INTO sales (salesman_name, sales_amount) VALUES ('David', 4000);

现在，我们要计算销售员的销售额排名：

SELECT salesman_name, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM sales;

执行以上SQL语句后，将得到以下结果：

+---------------+--------------+------+
| salesman_name | sales_amount | rank |
+---------------+--------------+------+
| Alice         |         5000 |    1 |
| Charlie       |         5000 |    1 |
| David         |         4000 |    3 |
| Bob           |         3000 |    4 |
+---------------+--------------+------+

可以看到，Alice和Charlie销售额都是5000，并列排名第一。接下来的排名没有是第二，而是第三。

如果我们想要占用相同的排名位置，可以使用DENSE_RANK()函数：

SELECT salesman_name, sales_amount, DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM sales;

执行以上SQL语句后，将得到以下结果：

+---------------+--------------+------+
| salesman_name | sales_amount | rank |
+---------------+--------------+------+
| Alice         |         5000 |    1 |
| Charlie       |         5000 |    1 |
| David         |         4000 |    2 |
| Bob           |         3000 |    3 |
+---------------+--------------+------+

通过DENSE_RANK() OVER (ORDER BY sales_amount DESC)，我们可以看到，Alice和Charlie的销售额仍然是5000，但他们都占用了排名第一的位置，接下来的排名是第二和第三。

3.4 只返回前几名

有时候我们只希望返回排名前几名的结果，可以使用TOP或LIMIT子句来实现。

下面以使用LIMIT子句为例，返回销售额排名前三的销售员：

SELECT salesman_name, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM sales
LIMIT 3;

执行以上SQL语句后，将得到以下结果：

+---------------+--------------+------+
| salesman_name | sales_amount | rank |
+---------------+--------------+------+
| Alice         |         5000 |    1 |
| Charlie       |         5000 |    1 |
| David         |         4000 |    3 |
+---------------+--------------+------+

可以看到，我们只返回了销售额排名前三的结果。