【MySQL】窗口函數詳解（概念+練習+實戰）

文章目錄

前言
1. SQL窗口函數
- 1.1 窗口函數概念
- 1.2 窗口函數語法
- 1.3 常見窗口函數
- - 1.3.1 聚合窗口函數
  - 1.3.2 專用窗口函數
- 1.4 窗口函數性能比較
2. LeetCode 例題
- 2.1 LeetCode SQL 178：分數排名
- 2.2 LeetCode SQL 184：最高工資
- 2.3 LeetCode SQL 185：前三工資
3. 項目實戰
- 3.1 需求描述
- 3.2 SQL 實戰
4. 補充與總結
- 4.1 `ROWS BETWEEN`子句常見關鍵字含義
- 4.2 如何理解窗口函數的“窗口”？
- 4.3 總結
參考資料

牛逼的兄弟兩個月前教了我一招…

前言

2023年12月下旬，廣東終于冷了！回想直到12月15那天，依然穿著短袖上班，吹著風扇空調睡覺… 哈哈，這是截至發文時的一些感受與題外話。天氣是冷了，但心中依然熱情似火，一是工作業務上又有稍微復雜的業務，有挑戰；二是雖然有挑戰，但想起牛逼的兄弟@CaptinKoo兩個月前教了我一招：SQL窗口函數，業務難題迎刃而解！趁著這次解決難題的熱度，將本次學到的窗口函數知識點以及項目實戰記錄下來，供各位分享。

我個人學習窗口函數主要有兩個用處：一是對現有SQL知識的拓展，二是能使用窗口函數對一些特定場景做SQL簡化，解決復雜問題。

但在正式開始之前，得事先說明一個前提：
前提

窗口函數是 Mysql 8 的新特性。本文的學習與演示，都基于Mysql 8
學習窗口函數，建議有一定的SQL基礎

學習目標

學習并了解SQL窗口函數相關概念
能使用SQL窗口函數解決部分業務場景題目，項目實戰
若實際業務用得少，那上述知識了解一下即可，建議收藏本文，用到的時候可以翻出來參考

下面我們開始！

1. SQL窗口函數

這一小節我們介紹窗口函數的一些概念。

1.1 窗口函數概念

概念
窗口函數，也叫OLAP函數（Online Anallytical Processing，聯機分析處理），可以對數據庫數據進行實時分析處理。

窗口函數在MySQL 8中引入，是Mysql 8的新特性。是一種主要用于數據分析、特定字段分組等的一種特殊的函數。

常見使用場景

數據分析，如排名、排序、分組統計、計算、前后值比較等
對某些分組場景簡化SQL，提升效率
常用于子查詢，將一些復雜條件簡化

1.2 窗口函數語法

窗口函數的語法如下：

窗口函數([參數]) OVER ([PARTITION BY <分組列>] [ORDER BY <排序列 ASC/DESC>][ROWS BETWEEN 開始行 AND 結束行]
)

PARTITION BY 子句用于指定分組列，關鍵字：PARTITION BY 。
ORDER BY 子句用于指定排序列，關鍵字ORDER BY 。
ROWS BETWEEN 子句用于指定窗口的范圍，關鍵字ROWS BETWEEN 即[開始行]、[結束行]（這部分在“補充與總結”小節中作補充詳細說明）。

其中，ROWS BETWEEN 子句在實際中可能用得相對少一些，因此有部分參考資料的語法描述省略了ROWS BETWEEN 子句，主要側重于PARTITION BY分組與ORDER BY排序：

窗口函數([參數]) OVER ([PARTITION BY <分組列>] [ORDER BY <排序列 ASC/DESC>]

也正因此，本文將ROWS BETWEEN 子句相關關鍵字知識點將會以補充的形式說明，而側重常用窗口函數的學習與練習，側重PARTITION BY 子句與ORDER BY子句的使用。

語法舉例，設有Order表，查詢銷售數量總和及其當前行前兩行和后兩行的銷售數量總和：

SELECT product_id, order_date, quantity,SUM(quantity) OVER (PARTITION BY product_id ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS sum_surrounding_quantities
FROM orders

這個例子暫時看不懂個沒關系，接下來，我們會詳細介紹常見窗口函數，并在介紹的過程中舉例。之后，上述例子就很好理解了。

1.3 常見窗口函數

本小節介紹常見窗口函數。

若要跟著本文進行練習，則可以參考著創建如下表，本文的例子均基于下表：

設計一個銷售數據表。該表包含以下字段：

id ：銷售記錄的唯一標識符（主鍵）
product ：產品名稱
category ：產品類別
sale_date ：銷售日期
quantity ：銷售數量
revenue ：銷售收入

以下是創建表的DDL以及插入模擬數據的DML

CREATE TABLE sales (id INT PRIMARY KEY,product VARCHAR(50),category VARCHAR(50),sale_date DATE,quantity INT,revenue DECIMAL(10, 2)
);INSERT INTO sales (id, product, category, sale_date, quantity, revenue)
VALUES(1, 'Product A', 'Category 1', '2022-01-01', 10, 100.00),(2, 'Product B', 'Category 1', '2022-01-01', 5, 50.00),(3, 'Product A', 'Category 2', '2022-01-02', 8, 80.00),(4, 'Product B', 'Category 2', '2022-01-02', 3, 30.00),(5, 'Product A', 'Category 1', '2022-01-03', 12, 120.00),(6, 'Product B', 'Category 1', '2022-01-03', 7, 70.00),(7, 'Product A', 'Category 2', '2022-01-04', 6, 60.00),(8, 'Product B', 'Category 2', '2022-01-04', 4, 40.00);

好的，準備工作完成，下面我們一邊學習具體窗口函數并練習吧！

1.3.1 聚合窗口函數

許多窗口函數的教程，通常將常用的窗口函數分為兩大類：聚合窗口函數與專用窗口函數。聚合窗口函數的函數名與普通常用聚合函數一致，功能也一致。從使用的角度來講，與普通聚合函數的區別在于提供了窗口函數的專屬子句，來使得數據的分析與獲取更簡便。主要有如下幾個：

函數名

作用

SUM

求和

AVG

求平均值

COUNT

求數量

MAX

求最大值

MIN

求最小值

區別
這個例子演示與普通聚合函數的區別。設我們要求使用一條查詢語句，在sales表每行最后一列都加上這一行的產品類別 category的平均銷售收入revenue，并且以category順序排序，即如下圖所示：

普通聚合函數的一種解法：

SELECT
t1.*,
t2.avg_revenue FROM sales t1
LEFT JOIN (
SELECT category, AVG(revenue) AS avg_revenue
FROM sales
GROUP BY category
) t2 ON t1.category = t2.category ORDER BY t1.category
聚合窗口函數:

SELECT
sales.*,
AVG( revenue ) OVER ( PARTITION BY category ) AS avg_revenue
FROM
sales

這么一對比，窗口聚合函數簡單不少！

1.3.2 專用窗口函數

常見的專用窗口函數

函數名

分類

說明

RANK

排序函數

類似于排名，并列的結果序號可以重復，序號不連續

DENSE_RANK

排序函數

類似于排名，并列的結果序號可以重復，序號連續

ROW_NUMBER

排序函數

對該分組下的所有結果作一個排序，基于該分組給一個行數

PERCENT_RANK

分布函數

每行按照公式 (rank-1) / (rows-1) 進行計算

CUME_DIST

分布函數

分組內小于、等于當前 rank 值的行數 / 分組內總行數

練習
分別對上述表格常見的專用窗口函數進行調用，查看結果。

SELECT *,RANK() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_rank`,DENSE_RANK() OVER(PARTITION BY category ORDER BY product DESC) AS `product_dense_rank`,ROW_NUMBER() OVER(PARTITION BY category ORDER BY product DESC) AS `product_row_number`,PERCENT_RANK() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_percent_rank`,CUME_DIST() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_cume_dist`
FROM sales

至于其它專用窗口函數，請讀者自行查閱其它資料做拓展。

1.4 窗口函數性能比較

通過對上面我們對窗口函數的學習與練習，我們一來明白了窗口函數的相關概念、常見窗口函數的使用以及這些窗口函數的作用與效果。也通過窗口函數與一般函數子查詢作了一個簡單的對比，體現了窗口函數在一些特定需求的強大。那么既然窗口函數如此強大，那么窗口函數的性能對比傳統函數、傳統子查詢與分組的性能相比如何呢？

窗口函數的性能和其它SQL語句一樣，受數據量大小、分區復雜度情況等影響。同等數量級的一般情況下：

窗口函數本身內嵌分組，相當于把條件先篩了一遍，可減少部分子查詢。減少的子查詢部分相當于降低了子查詢本身的連接消耗。
窗口函數窗口大小限制，可減少部分行數結果返回消耗。
窗口函數可用于子查詢，簡化部分語句。但又因為用在了子查詢，還是有一定連接開銷。
窗口聚合函數在窗口函數原有分區、排序的基礎上增加了聚合，且因不會影響行數的關系，比原有分組行數要多，其開銷比一般聚合函數開銷要大一些，因此窗口聚合函數一般情況下會比普通聚合函數性能差一些。

當然，上述只是理論上的性能初步分析，實際還得視具體的情況而定。
至于窗口函數優化方案，可以以影響窗口函數性能的原因為切入點由因到果進行優化，例如縮小窗口大小限制。篇幅有限，不作詳解。詳情可參考文末推薦的優秀參考文章。

2. LeetCode 例題

上一小節，我們學習了 SQL 窗口函數的概念，從本小節開始，就是做題練習與實戰了！

接下來要列舉例題，是 @CaptinKoo 兩個月前教我們窗口函數時提供的練習題。讓我們跟隨 @CaptinKoo 老師的腳步，進行窗口函數練習吧！

2.1 LeetCode SQL 178：分數排名

題目鏈接
LeetCode-SQL178分數排名鏈接

題目描述

題解
根據題目描述，我們得知，返回結果序號可重復，連續，因此我們使用DENSE_RANK()函數。

SELECT score,DENSE_RANK() OVER(ORDER BY score DESC) AS `rank`
FROM Scores 
ORDER BY score DESC

2.2 LeetCode SQL 184：最高工資

題目鏈接
LeetCode-SQL184部門工資最高的員工

題目描述

題解
根據描述，我們可以通過 RANK 窗口函數對 Employee 表進行排序，獲取 rank 值為1 的員工并關聯到部門表。

SELECT d.name AS Department,e.name AS Employee,e.salary AS Salary
FROM (SELECT name, salary,departmentId, RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS `rank`FROM Employee
) e 
LEFT JOIN Department d ON e.departmentId = d.id
WHERE e.`rank` = 1

2.3 LeetCode SQL 185：前三工資

題目鏈接
LeetCode-SQL185部門工資前三高的所有員工

題目描述

題解
有了上面兩道題的解題練習，這道題也迎刃而解：

SELECT d.name AS Department,e.name AS Employee,e.salary AS Salary
FROM (SELECT name, salary,departmentId, DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS `rank`FROM Employee
) e 
LEFT JOIN Department d ON e.departmentId = d.id
WHERE e.`rank` <= 3

太棒了！我們一下就完成了三道包括中等、困難難度的LeetCode題目，接下來，我們可以將我們學習并練習過的知識點用于項目實戰了！

3. 項目實戰

本小節是我個人用窗口函數解決實際工作問題的實戰記錄。涉及的表、字段均已做描述更換，脫敏處理。

3.1 需求描述

已知用戶訂單評價表order_evaluate有如下字段：

字段名

類型

說明

bigint

評價表主鍵id

evaluate

text

評價內容

user_id

bigint

用戶id

update_time

datetime

更新時間

order_id

bigint

訂單id

其中，每個訂單可以有多個評價，每個評價都可以修改。業務需要，需要獲取當前用戶所有訂單最近一次評價內容，并返回訂單id、最近一次評價的內容。

3.2 SQL 實戰

此次實戰業務需要根據update_time獲取最近一次評論并根據order_id進行分組。

在尚未系統學習窗口函數時，我們第一時間會想到的是傳統子查詢。

但畢竟實際業務遠比這里的脫敏描述要復雜，一時間難以實現，于是我第一時間回顧了@CaptinKoo大佬教我的窗口函數并解決：

# 畢竟是Demo，忽略 user_id 條件，實際業務會補充齊全其它條件
SELECTt1.order_id,evaluate
FROMorder_evaluate t1INNER JOIN ( SELECT id, order_id, ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY update_time DESC ) AS row_num FROM order_evaluate) t2 ON t1.id = t2.id 
WHEREt2.row_num = 1

這條SQL是通過ROW_NUM()函數將工單評價表根據工單分組，更新時間倒序并給它一個行序號。行序號1的就是我們要求的結果。

你能想到用傳統子查詢實現相同功能的SQL嗎？