MySQL RANK函数

时间:2019-11-20 08:52:28  来源:igfitidea点击:

简介:在本教程中,您将学习MySQL RANK()函数以及如何应用该函数将排名分配给结果集分区中的每一行。

请注意,自8.0版开始,MySQL就一直支持RANK()函数和其他窗口函数

MySQL RANK()函数简介

RANK()函数为结果集分区中的每一行分配一个等级。
行的等级由一个加上它之前的等级数指定。

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

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

使用以下语法:

  • 首先,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;

以下语句使用RANK()函数为t表中结果集的每一行分配一个等级:

SELECT
    val,
    RANK() OVER (
        ORDER BY val
    ) my_rank
FROM
    t;

这是输出:

如您所见,第二行和第三行具有相同的联系,因此它们获得相同的排名2。

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

MySQL RANK()函数示例

让我们使用在窗口功能教程中创建的销售表进行演示。

如果尚未创建销售表,则此脚本为:

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;

下图显示了销售表的数据:

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

SELECT
    sales_employee,
    fiscal_year,
    sale,
    RANK() OVER (PARTITION BY
                     fiscal_year
                 ORDER BY
                     sale DESC
                ) sales_rank
FROM
    sales;

在此示例中:

  • 首先,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;

这是输出:

在此示例中:

  • 首先,我们使用通用表表达式(CTE)来获取订单号,订单年份和排名。
    为了按每年的订单值对订单进行排名,我们使用了RANK()函数,该功能按订单年对行进行划分,并按降序对订单值进行排序。

  • 然后,我们仅选择排名小于或等于3的订单。

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