MySQL中窗口函数之Rank Over

发布时间：2023-05-21

一、什么是Rank Over

Rank Over是MySQL中的一种窗口函数，通过它可以实现对于同一组数据的排序并进行排名。它会返回一个排名整数值，以此来说明中间结果集的行在结果集中的排名。

二、使用方法

Rank Over函数的使用格式如下：

RANK() OVER ( 
PARTITION BY partition_expression1, partition_expression2,... 
ORDER BY sort_expression1 [ASC | DESC], sort_expression2 [ASC | DESC], ... 
)

其中PARTITION BY是可选的，如果不使用，那么默认认为是整个结果集作为一个分区进行排序。ORDER BY是必需的，至少需要一个排序表达式，第一个排序表达式可以使用ASC或DESC关键字指定排序顺序。如果有多个排序表达式，每个表达式可以指定不同的排序顺序。

三、应用案例

以下是一些常见的应用案例：

1. 按照销售额对商店进行排名

假设我们有一个商店销售数据的表格，包含了商店名称、销售量和销售日期三个字段：

CREATE TABLE `sales` ( 
`id` INT NOT NULL AUTO_INCREMENT, 
`store` VARCHAR(50) NOT NULL, 
`sales_volume` INT, 
`sales_date` DATE, 
PRIMARY KEY (`id`) 
)

现在我们想要对于每个商店按照销售量进行排名，可以使用以下代码实现：

SELECT store, sales_volume, 
RANK() OVER (PARTITION BY store ORDER BY sales_volume DESC) AS 'rank'
FROM sales;

这将会返回一个结果集，其中包含了每个商店的名称、销售量和排名：

store sales_volume rank
Apple 8000 1
Apple 7000 2
Google 5000 1
Google 4000 2
Google 3500 3
Amazon 10000 1

2. 按照产品销售额对代理商进行排名

假设我们有一个代理商销售数据的表格，包含了代理商名称、产品名称、销售量和销售日期四个字段：

CREATE TABLE `sales` ( 
`id` INT NOT NULL AUTO_INCREMENT, 
`agent` VARCHAR(50) NOT NULL, 
`product` VARCHAR(50) NOT NULL, 
`sales_volume` INT, 
`sales_date` DATE, 
PRIMARY KEY (`id`) 
)

现在我们想要对于每个代理商按照产品销售量进行排名，可以使用以下代码实现：

SELECT agent, product, sales_volume, 
RANK() OVER (PARTITION BY agent, product ORDER BY sales_volume DESC) AS 'rank'
FROM sales;

这将会返回一个结果集，其中包含了每个代理商、产品的名称、销售量和排名：

agent product sales_volume rank
A iPhone 3000 1
A iPhone 2000 2
A iPad 500 1
A iPad 400 2
B iPhone 5000 1
B iPhone 4000 2
B MacBook 1200 1

3. 计算平均数并进行排名

如果我们有一组数据，我们想要计算每个数与平均数的差值并进行排名，可以使用以下代码实现：

SELECT number, 
AVG(number) OVER () AS 'avg', 
RANK() OVER (ORDER BY number - AVG(number) OVER ()) AS 'rank'
FROM numbers

其中numbers是包含数字的表格。

四、总结

Rank Over是MySQL中的一种强大的窗口函数，它可以帮助我们轻松地对于同一组数据进行排序并进行排名。

推荐文章