MySQL中窗口函數之Rank Over

一、什麼是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中的一種強大的窗口函數，它可以幫助我們輕鬆地對於同一組數據進行排序並進行排名。