《MySQL 入門教程》第 22 篇 窗口函數

文章來源：https://blog.csdn.net/horses/article/details/108226125

原文作者：不剪髮的Tony老師

來源平臺：CSDN

窗口函數（Window Function）是 MySQL 8.0 新增的一個重要的功能，可以為數據分析提供強大的支持，例如計算分組排名、累積求和、同比/環比增長率等。本篇我們就來了解一下 MySQL 中窗口函數的語法和各種窗口函數的作用。

另外，這裡有一份 SQL 窗口函數速查表；歡迎下載保存，以便不時之需。

22.1 窗口函數概述

在第 12 篇中我們學習了常見的聚合函數，包括 AVG、COUNT、MAX、MIN、SUM 以及 GROUP_CONCAT。聚合函數的作用就是對一組數據行進行彙總計算，並且返回單個分析結果。

窗口函數和聚合函數類似之處在於它也是對一組數據進行分析；但是，窗口函數不是將一組數據彙總為單個結果；而是針對查詢中的每一行數據，基於和它相關的一組數據計算出一個結果。下圖演示了聚合函數和窗口函數的區別：

窗口函數在其他數據庫中也叫做分析函數（Analytic Function），或者聯機分析處理（OLAP）函數。

為了便於理解，我們可以比較一下聚合函數和窗口函數的結果。以下示例分別將 COUNT 作為聚合函數和窗口函數，計算員工的人數：

<code>SELECT count(*)
FROM employee;
count(*)|
--------|
      25|

SELECT emp_id, emp_name, count(*) OVER ()
FROM employee;
emp_id|emp_name |count(*) OVER ()|
------|---------|----------------|
    13|關興      |              25|
    11|關平      |              25|
     2|關羽      |              25|
     1|劉備      |              25|
    16|周倉      |              25|
     8|孫丫鬟    |              25|
.../<code>

聚合函數（COUNT(*)）通常也可以作為窗口函數（COUNT(*) OVER()），區別在於後者包含了OVER關鍵字；空括號表示將所有數據作為整體進行分析，所以得到的數值和聚合函數一樣。查詢結果中，聚合函數只返回了一個彙總結果，而窗口函數為每一個員工都返回了一個結果。

22.2 窗口函數的定義

窗口函數與其他函數的語法區別主要在於OVER子句，接下來我們介紹它的語法。窗口函數的定義如下：

<code>window_function ( expr ) OVER (
    PARTITION BY ...
    ORDER BY ...
    frame_clause
)/<code>

其中，window_function 是窗口函數的名稱；expr 是參數，有些函數不需要參數；OVER子句包含三個選項：分區（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）。

22.2.1 分區選項（PARTITION BY）

PARTITION BY選項用於將數據行拆分成多個分區（組），窗口函數基於每一行數據所在的組進行計算並返回結果，它的作用類似於GROUP BY分組。如果省略了 PARTITION BY，所有的數據作為一個組進行計算，上文中的示例就是如此。

以下示例按照不同的部門分別統計員工的月薪合計：

<code>SELECT emp_name "姓名", salary "月薪", dept_id "部門編號",
       sum(salary) OVER (PARTITION BY dept_id) AS "部門月薪合計"
FROM employee;/<code>

其中，OVER 子句中的 PARTITION BY 選項表示按照部門進行分區；因此，SUM 函數按照部門分別統計月薪的合計值。該語句的結果如下（只顯示了前 3 個部門的結果）：

前 3 行數據的部門編號都為 1，因此該部門的月薪合計為 30000 + 26000 + 24000 = 80000；其他部門的數據也採用同樣的方式進行計算。

SQL 標準要求 PARTITION BY 之後只能使用字段名，不過 MySQL 允許指定表達式。另外，我們也可以在 PARTITION BY 之後指定多個分組字段，例如同時按照部門和性別進行分組分析。

22.2.2 排序選項（ORDER BY）

OVER 子句中的ORDER BY選項用於指定分區內的排序方式，與 ORDER BY 子句的作用類似，通常用於數據的排名分析。以下示例用於計算每個員工在部門內的月薪排名：

<code>SELECT emp_name "姓名", salary "月薪", dept_id "部門編號",
       rank() OVER (
         PARTITION BY dept_id
         ORDER BY salary DESC 
       ) AS "部門排名"
FROM employee;/<code>

其中，PARTITION BY 選項表示按照部門進行分區；ORDER BY 選項指定在分區內按照月薪從高到低進行排序；RANK 函數用於計算名次，該函數將會在下文中進行介紹。

該語句的結果如下（只顯示了前 3 個部門的結果）：

前 3 行數據的部門編號都為 1；“劉備”的月薪最高，在部門內排名第一；“關羽”排名第二；“張飛”排名第三。其他部門的數據採用同樣的方式進行計算。

ORDER BY 選項用於指定分區內數據的排序，排序字段數據相同的行是對等行（peer）。如果省略 ORDER BY ，分區內的數據不進行排序，不按照固定順序處理， 而且所有數據都是對等行。

22.2.3 窗口選項（frame_clause）

frame_clause選項用於在當前分區內指定一個計算窗口，也就是一個與當前行相關的數據子集。指定了窗口之後，分析函數不再基於分區進行計算，而是基於窗口內的數據進行計算。窗口會隨著當前處理的數據行而移動，例如：

• 定義一個從分區開始到當前數據行結束的窗口，可以計算截止到每一行的累計總值。
• 定義一個從當前行之前 N 行數據到當前行之後 N 行數據的窗口，可以計算移動平均值。

具體來說，窗口大小的常用選項如下：

<code>{ ROWS | RANGE } frame_start
{ ROWS | RANGE } BETWEEN frame_start AND frame_end/<code>

其中，ROWS表示以行為單位指定窗口的偏移量，RANGE表示以數值（例如 30 分鐘）為單位指定窗口的偏移量。frame_start 和 frame_end 分別表示窗口的開始行和結束行，它們的可能取值如下：

<code>CURRENT ROW
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
expr PRECEDING
expr FOLLOWING/<code>

frame_start 和 frame_end 的具體意義如下：

• CURRENT ROW：對於 ROWS 方式，代表了當前行；對於 RANGE，代表了當前行的所有對等行。
• UNBOUNDED PRECEDING：代表了分區中的第一行。
• UNBOUNDED FOLLOWING：代表了分區中的最後一行。
• expr PRECEDING：對於 ROWS 方式，代表了當前行之前的第 expr 行；對於 RANGE，代表了等於當前行的值減去 expr 的所有行；如果當前行的值為 NULL，代表了當前行的所有對等行。
• expr FOLLOWING：對於 ROWS 方式，代表了當前行之後的第 expr 行；對於 RANGE，代表了等於當前行的值加上 expr 的所有行；如果當前行的值為 NULL，代表了當前行的所有對等行。

如果只有 frame_start，默認以當前行作為窗口的結束。如果同時指定了兩者，frame_start 不能晚於 frame_end，例如 BETWEEN 1 FOLLOWING AND 1 PRECEDING 就是一個無效的窗口。下圖可以方便我們理解這些選項的含義：

CURRENT ROW表示當前正在處理的行；其他的行可以使用相對當前行的位置表示。需要注意，窗口的大小不會超出當前分區的範圍。

以下示例按照部門統計員工的累計月薪值：

<code>SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪",
       sum(salary) OVER (
         PARTITION BY e.dept_id
         ORDER BY e.emp_id
         ROWS UNBOUNDED PRECEDING
       ) AS "部門累計月薪"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id);/<code>

其中，PARTITION BY 選項表示按照部門進行分區；ORDER BY 選項表示按照工號進行排序；窗口子句 ROWS UNBOUNDED PRECEDING 指定窗口從分區的第一行開始，默認到當前行結束；因此 SUM 函數計算的是部門內累計到當前員工為止的月薪合計。該查詢的結果如下（只顯示了前 3 個部門的結果）：

對於“行政管理部”，第一個員工的月薪為 30000，累計也是 30000；第二個員工的月薪為 26000，累計為 30000 + 26000 = 56000；依此類推，直到統計完該部門的所有員工；然後開始統計下一個部門的數據。

22.2.4 命名窗口

窗口函數的 OVER 子句除了直接定義三種選項之外，還可以使用一個預定義的窗口變量進行定義。窗口變量使用WINDOW子句進行定義，語法位於 HAVING 和 ORDER BY 之間。

<code>window_function(expr) OVER window_name

WINDOW window_name AS (PARTITION BY ... ORDER BY ... frame_clause)
WINDOW window_name AS (other_window_name)/<code>

如果查詢中多個窗口函數的 OVER 子句相同，利用 WINDOW 子句定義一個窗口變量，然後在多個 OVER 子句中使用該變量可以簡化查詢語句。例如：

<code>SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪",
       sum(salary) OVER w AS "部門累計月薪",
       count(*) OVER w AS "部門累計人數"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
WINDOW w AS (
     PARTITION BY e.dept_id
     ORDER BY e.emp_id
     ROWS UNBOUNDED PRECEDING
);/<code>

接下來我們介紹一些常見的窗口函數和示例。

22.3 常用窗口函數

常見的窗口函數可以分為以下幾類：聚合窗口函數、排名窗口函數以及取值窗口函數。

窗口函數只能出現在 SELECT 列表和 ORDER BY 子句中，查詢語句的處理順序依次為 FROM、WHERE、GROUP BY、聚合函數、HAVING、窗口函數、SELECT DISTINCT、ORDER BY、LIMIT。

22.3.1 聚合窗口函數

常用的聚合函數，例如 AVG、SUM、COUNT 等，也可以作為窗口函數使用。上文我們已經列舉了一些聚合窗口函數的示例，再來看一個使用 AVG 函數計算移動平均值的例子：

<code>SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪",
       avg(salary) OVER (
         PARTITION BY e.dept_id
         ORDER BY e.salary
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS "移動平均月薪"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id);
部門名稱  |姓名  |月薪     |移動平均月薪  |
--------|------|--------|------------|
行政管理部|張飛  |24000.00|25000.000000|
行政管理部|關羽  |26000.00|26666.666667|
行政管理部|劉備  |30000.00|28000.000000|
人力資源部|魏延  | 8000.00| 8250.000000|
人力資源部|黃忠  | 8500.00|13833.333333|
人力資源部|諸葛亮|25000.00|16750.000000|
.../<code>

其中，PARTITION BY 選項表示按照部門進行分區；ORDER BY 選項表示按照月薪從低到高進行排序；窗口子句 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指定窗口從當前行的前一行開始，到當前行的下一行結束；因此該函數計算的是每個部門內員工與其前後各一個員工的平均月薪值。

移動平均值通常用於處理時間序列的數據。例如，廠房的溫度檢測器獲取了每秒鐘的溫度，我們可以使用以下窗口計算前五分鐘內的平均溫度：

<code>avg(temperature) OVER (ORDER BY ts RANGE BETWEEN interval '5 minute' PRECEDING AND CURRENT ROW)/<code>

22.3.2 排名窗口函數

排名窗口函數用於對數據進行分組排名。常見的排名窗口函數包括：

• ROW_NUMBER，為分區中的每行數據分配一個序列號，序列號從 1 開始分配。
• RANK，計算每行數據在其分區中的名次；如果存在名次相同的數據，後續的排名將會產生跳躍。
• DENSE_RANK，計算每行數據在其分區中的名次；即使存在名次相同的數據，後續的排名也是連續的值。
• PERCENT_RANK，以百分比的形式顯示每行數據在其分區中的名次；如果存在名次相同的數據，後續的排名將會產生跳躍。
• CUME_DIST，計算每行數據在其分區內的累積分佈，也就是該行數據及其之前的數據的比率；取值範圍大於 0 並且小於等於 1。
• NTILE，將分區內的數據分為 N 等份，為每行數據計算其所在的位置。

排名窗口函數不支持動態的窗口大小（frame_clause），而是以當前分區作為分析的窗口。以下示例按照部門分組，並計算每個員工在其部門中的月薪排名，分別使用了 4 個不同的排名函數：

<code>SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪",
       ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "row_number",
       RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "rank",
       DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "dense_rank",
       PERCENT_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "percent_rank"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id);
部門名稱  |姓名 |月薪     |row_number|rank|dense_rank|percent_rank       |
--------|-----|--------|----------|----|----------|-------------------|
行政管理部|劉備 |30000.00|         1|   1|         1|                0.0|
行政管理部|關羽 |26000.00|         2|   2|         2|                0.5|
行政管理部|張飛 |24000.00|         3|   3|         3|                1.0|
...
研發部   |趙雲 |15000.00|         1|   1|         1|                0.0|
研發部   |周倉 | 8000.00|         2|   2|         2|              0.125|
研發部   |關興 | 7000.00|         3|   3|         3|               0.25|
研發部   |關平 | 6800.00|         4|   4|         4|              0.375|
研發部   |趙氏 | 6600.00|         5|   5|         5|                0.5|
研發部   |廖化 | 6500.00|         6|   6|         6|              0.625|
研發部   |張苞 | 6500.00|         7|   6|         6|              0.625|
研發部   |趙統 | 6000.00|         8|   8|         7|              0.875|
研發部   |馬岱 | 5800.00|         9|   9|         8|                1.0|
.../<code> 

其中，4 個函數的 OVER 子句完全相同；PARTITION BY 表示按照部門進行分區；ORDER BY 表示按照月薪從高到低進行排序。我們以結果中的“研發部”為例進行分析：

• ROW_NUMBER 函數為每個員工分配了一個連續的數字編號，可以看作是一種排名。其中“廖化”和“張苞”的月薪相同，但是編號不同；
• RANK 函數為每個員工指定了一個名次，其中“廖化”和“張苞”的名次都是 6。在他們之後的“趙統”的名次為 8，產生了跳躍；
• DENSE_RANK 函數為每個員工指定了一個名次，其中“廖化”和“張苞”的名次都是 6。在他們之後的“趙統”的名次為 7，名次是連續值；
• PERCENT_RANK 函數按照百分比指定名次，取值位於 0 到 1 之間。其中“趙統”的百分比排名為 0.875，也產生了跳躍。

以下語句演示了 CUME_DIST 和 NTILE 函數的作用：

<code>SELECT emp_name AS "姓名", salary AS "月薪",
       CUME_DIST() OVER (ORDER BY salary DESC) AS "累積佔比",
       NTILE(5) OVER (ORDER BY salary DESC) AS "相對位置"
FROM employee;
姓名  |月薪    |累積佔比|相對位置|
-----|--------|------|----|
劉備  |30000.00|  0.04|   1|
關羽  |26000.00|  0.08|   1|
諸葛亮|25000.00|  0.12|   1|
張飛  |24000.00|  0.16|   1|
趙雲  |15000.00|   0.2|   1|
孫尚香|12000.00|  0.24|   2|
...
糜竺  | 4300.00|  0.84|   5|
黃權  | 4200.00|  0.88|   5|
龐統  | 4100.00|  0.92|   5|
蔣琬  | 4000.00|   1.0|   5|
鄧芝  | 4000.00|   1.0|   5|/<code>

其中，OVER 子句沒有指定分區選項，因此所有的員工作為一個整體進行分析；ORDER BY 按照月薪從高到低進行排序。從結果可以看出，20% 的員工月薪大於等於 15000；或者說，月薪 15000 意味著在公司中的排名屬於最高的 20%。NTILE 將員工按照月薪從高到低分成了 5 個組，相對位置為 1 的員工是月薪最高的 20% 員工。

22.3.3 取值窗口函數

取值窗口函數用於返回指定位置上的數據。常見的取值窗口函數包括：

• FIRST_VALUE，返回窗口內第一行的數據。
• LAST_VALUE，返回窗口內最後一行的數據。
• NTH_VALUE，返回窗口內第 N 行的數據。
• LAG，返回分區中當前行之前的第 N 行的數據。
• LEAD，返回分區中當前行之後第 N 行的數據。

其中，LAG 和 LEAD 函數不支持動態的窗口大小（frame_clause），而是以當前分區作為分析的窗口。

以下語句使用 FIRST_VALUE、LAST_VALUE 以及 NTH 函數分別獲取每個部門內部月薪最高、月薪最低以及月薪第二高的員工：

<code>SELECT d.dept_name "部門名稱", e.emp_name "姓名", e.salary "月薪",
       first_value(salary) OVER w "最高月薪",
       last_value(salary) OVER w "最低月薪",
       nth_value(salary, 2) OVER w "第二高月薪"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
WINDOW w AS (
     PARTITION BY e.dept_id 
     ORDER BY salary DESC 
     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY e.dept_id, salary DESC;
部門名稱 |姓名  |月薪     |最高月薪 |最低月薪 |第二高月薪|
--------|-----|--------|--------|--------|--------|
行政管理部|劉備 |30000.00|30000.00|24000.00|26000.00|
行政管理部|關羽 |26000.00|30000.00|24000.00|26000.00|
行政管理部|張飛 |24000.00|30000.00|24000.00|26000.00|
人力資源部|諸葛亮|25000.00|25000.00| 8000.00| 8500.00|
人力資源部|黃忠 | 8500.00|25000.00| 8000.00| 8500.00|
人力資源部|魏延 | 8000.00|25000.00| 8000.00| 8500.00|
.../<code> 

以上三個函數的默認窗口是從當前分區的第一行到當前行，所以我們在OVER子句中將窗口設置為整個分區。

LAG 和 LEAD 函數可以用於計算銷量數據的環比/同比增長。我們創建一個新的數據表：sales_monthly，它記錄了不同產品按月統計的銷售金額。以下語句統計不同產品每個月的環比增長率：

<code>SELECT product AS "產品", ym "年月", amount "銷量",
       (amount - LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym))/
       LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym) * 100 AS "環比增長率（%）"
FROM sales_monthly
ORDER BY product, ym;
產品|年月   |銷量    |環比增長率（%）|
---|------|--------|---------|
桔子|201801|10154.00|         |
桔子|201802|10183.00| 0.285602|
桔子|201803|10245.00| 0.608858|
桔子|201804|10325.00| 0.780869|
桔子|201805|10465.00| 1.355932|
桔子|201806|10505.00| 0.382226|
桔子|201807|10578.00| 0.694907|
桔子|201808|10680.00| 0.964265|
桔子|201809|10788.00| 1.011236|
桔子|201810|10838.00| 0.463478|
桔子|201811|10942.00| 0.959587|
桔子|201812|10988.00| 0.420398|
桔子|201901|11099.00| 1.010193|
桔子|201902|11181.00| 0.738805|
桔子|201903|11302.00| 1.082193|
桔子|201904|11327.00| 0.221200|
桔子|201905|11423.00| 0.847532|
桔子|201906|11524.00| 0.884181|
.../<code>

其中，LAG(amount, 1) 表示獲取上一期的銷量；PARTITION BY 表示按照產品分區；ORDER BY 表示按照月份進行排序；當前月份的銷量減去上個月的銷量，再除以上個月的銷量，就是環比增長率。

想一想，怎麼計算同比增長率呢？同比增長是指本期數據與上一年度或歷史同期相比的增長。例如，2019 年 6 月的銷量與 2018 年 6 月的銷量相比增加的部分。

對了，在這裡說一下，我目前是在職Java開發，如果你現在正在學習Java，瞭解Java，渴望成為一名合格的Java開發工程師，在入門學習Java的過程當中缺乏基礎入門的視頻教程，可以關注並私信我：01。獲取。我這裡有最新的Java基礎全套視頻教程。