MySQL RANK Function

摘要：在本教程中，您将了解 MySQL RANK()函数以及如何应用它为结果集分区内的每一行分配排名。

请注意，MySQL 从 8.0 版本开始支持RANK()函数和其他窗口函数

MySQL RANK()函数简介

RANK()函数为结果集分区内的每一行分配一个排名。行的排名由一加上其之前的排名数指定。

下面显示了RANK()函数的语法：

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
Code language: SQL (Structured Query Language) (sql)

在这个语法中：

• 首先， PARTITION BY子句将结果集划分为多个分区。 RANK()函数在分区内执行，并在跨越分区边界时重新初始化。
• 其次， ORDER BY子句按一个或多个列或表达式对分区内的行进行排序。

与ROW_NUMBER()函数不同， RANK()函数并不总是返回连续的整数。

假设我们有一个示例表如下：

CREATE TABLE t (
    val INT
);

INSERT INTO t(val)
VALUES(1),(2),(2),(3),(4),(4),(5);


SELECT * FROM t;
Code language: SQL (Structured Query Language) (sql)

以下语句使用RANK()函数为t表中结果集中的每一行分配排名：

SELECT
    val,
    RANK() OVER (
        ORDER BY val
    ) my_rank
FROM
    t;
Code language: SQL (Structured Query Language) (sql)

这是输出：

正如您所看到的，第二行和第三行具有相同的平局，因此它们获得相同的排名 2。

第四行的等级为 4，因为RANK()函数跳过等级 3。

MySQL RANK()函数示例

我们使用窗口函数教程中创建的sales表进行演示。

如果您尚未创建sales表，请使用以下脚本：

CREATE TABLE IF NOT EXISTS sales(
    sales_employee VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);
 
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);
 
SELECT * FROM sales;Code language: SQL (Structured Query Language) (sql)

下图是sales表的数据：

以下语句使用RANK()函数按每年的销售额对销售人员进行排名：

SELECT
    sales_employee,
    fiscal_year,
    sale,
    RANK() OVER (PARTITION BY
                     fiscal_year
                 ORDER BY
                     sale DESC
                ) sales_rank
FROM
    sales;
Code language: SQL (Structured Query Language) (sql)

在这个例子中：

• 首先， PARTITION BY子句将结果集按会计年度划分为多个分区。
• 然后， ORDER BY子句按销售额降序对销售员工进行排序。

MySQL RANK()函数和 CTE 示例

以下语句使用RANK()函数查找每年价值最高的前三个订单：

WITH order_values AS(
    SELECT 
        orderNumber, 
        YEAR(orderDate) order_year,
        quantityOrdered*priceEach AS order_value,
        RANK() OVER (
            PARTITION BY YEAR(orderDate)
            ORDER BY quantityOrdered*priceEach DESC
        ) order_value_rank
    FROM
        orders
    INNER JOIN orderDetails USING (orderNumber)
)
SELECT 
    * 
FROM 
    order_values
WHERE 
    order_value_rank <=3;
Code language: SQL (Structured Query Language) (sql)

这是输出：

在这个例子中：

• 首先，我们使用公共表表达式(CTE) 来获取订单号、订单年份和排名。为了按每年的订单值对订单进行排名，我们使用了RANK()函数，该函数按订单年份对行进行分区，并按降序对订单值进行排序。
• 然后，我们只选择排名小于或等于三的订单。

在本教程中，您学习了如何使用 MySQL RANK()函数为结果集中的每一行分配排名。