MySQL 視窗函數 - tw511教學網

1. 視窗函數概念和語法

視窗函數對一組查詢行執行類似聚合的操作。然而，聚合操作將查詢行分組到單個結果行，而視窗函數為每個查詢行產生一個結果:

函數求值發生的行稱為當前行
與發生函數求值的當前行相關的查詢行組成了當前行的視窗

相比之下，視窗操作不會將一組查詢行摺疊到單個輸出行。相反，它們為每一行生成一個結果。

SELECT 
    manufacturer, product, profit,
    SUM(profit) OVER() AS total_profit,
    SUM(profit) OVER(PARTITION BY manufacturer) AS manufacturer_profit
FROM sales;

查詢中的每個視窗操作都通過包含一個 OVER 子句來表示，該子句指定如何將查詢行劃分為組以供視窗函數處理：

第一個 OVER 子句是空的，它將整個查詢行集視為一個分割區。視窗函數因此產生一個全域性和，但對每一行都這樣做。
第二個 OVER 子句按 manufacturer 劃分行，產生每個分割區（每個manufacturer）的總和。該函數為每個分割區行生成此總和。

視窗函數只允許在查詢列表和 ORDER BY 子句中使用。

查詢結果行由 FROM 子句確定，在 WHERE、GROUP BY 和 HAVING 處理之後，視窗執行發生在 ORDER BY、LIMIT 和 SELECT DISTINCT 之前。

OVER子句被允許用於許多聚合函數，因此，這些聚合函數可以用作視窗函數或非視窗函數，具體取決於是否存在 OVER 子句：

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

MySQL還支援只能作為視窗函數使用的非聚合函數。對於這些，OVER子句是必須的

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

ROW_NUMBER() 它生成其分割區內每一行的行號。預設情況下，分割區行是無序的，行編號是不確定的。若要對分割區行進行排序，請在視窗定義中包含一個ORDER BY子句。下面的範例中，查詢使用無序分割區和有序分割區(row_num1和row_num2列)來說明省略和包含ORDER BY之間的區別:

SELECT 
    manufacturer, product, profit,
    ROW_NUMBER() OVER(PARTITION BY manufacturer) AS row_num1,
    ROW_NUMBER() OVER(PARTITION BY manufacturer ORDER BY profit) AS row_num2
FROM sales;

如前所述，要使用視窗函數（或將聚合函數視為視窗函數），需要在函數呼叫後包含OVER子句。OVER子句有兩種形式:

over_clause:
    {OVER (window_spec) | OVER window_name}

這兩種形式都定義了視窗函數應該如何處理查詢行。它們的區別在於視窗是直接在OVER子句中定義的，還是通過對查詢中其他地方定義的命名視窗的參照提供的:

在第一種情況下，視窗規範直接出現在 OVER 子句中的括號之間。
在第二種情況下，window_name 是由查詢中其他地方的 WINDOW 子句定義的視窗規範的名稱。

對於 OVER (window_spec) 語法，視窗規範有幾個部分，都是可選的：

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

如果 OVER() 為空，則視窗由所有查詢行組成，視窗函數使用所有行計算結果。否則，括號中的子句決定了使用哪些查詢行來計算函數結果，以及它們是如何分割區和排序的：

window_name: 由查詢中其他地方的window子句定義的視窗的名稱。如果window_name單獨出現在OVER子句中，則它完全定義了視窗。如果分割區、排序或分幀子句也給出了，它們會修改被命名視窗的解釋。
partition_clause: PARTITION BY 子句指示如何將查詢行分組。給定行的視窗函數結果基於包含該行的分割區的行。如果省略 PARTITION BY，則有一個由所有查詢行組成的分割區。
```
partition_clause:
    PARTITION BY expr [, expr] ...
```
order_clause: ORDER BY 子句指示如何對每個分割區中的行進行排序。根據 ORDER BY 子句相等的分割區行被視為對等。如果省略 ORDER BY，則分割區行是無序的，沒有隱含的處理順序，並且所有分割區行都是對等的。
```
order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
```

每個ORDER BY表示式後面可以有選擇地跟著ASC或DESC來表示排序方向。NULL 值首先進行升序排序，最後進行降序排序。

視窗定義中的 ORDER BY 適用於各個分割區。要將結果集作為一個整體進行排序，請在查詢頂層包含 ORDER BY。

frame_clause: frame是當前分割區的子集，frame子句指定如何定義該子集。

小結：

視窗，就是資料範圍，也可以理解為記錄集合，視窗函數就是在滿足某種條件的記錄集合上執行的特殊函數。即，應用在視窗內的函數。

靜態視窗：視窗大小是固定的，視窗內的每條記錄都要執行此函數
動態視窗：也叫滑動視窗，視窗大小是變化的

視窗函數有以下功能：

同時具有分組和排序的功能
不減少原表的行數

2. 視窗函數frame規範

一個frame是當前分割區的一個子集，frame子句指定如何定義這個子集。

frame是根據當前行確定的，這使得frame可以根據當前行在分割區中的位置在分割區中移動。

通過將一個frame定義為從分割區開始到當前行的所有行，我們可以計算每一行的執行總數。
通過將一個frame定義為在當前行的每一邊擴充套件N行，我們可以計算捲動平均。

下面的查詢演示瞭如何使用移動幀來計算每組按時間順序排列的值的總和，以及從當前行和緊隨其後的行計算的捲動平均值:

SELECT 
  manufacturer, `month`, profit, 
  SUM(profit) OVER(
	    PARTITION BY manufacturer 
	    ORDER BY `month` 
	    ROWS unbounded PRECEDING
	  ) AS running_total, 
  AVG(profit) OVER(
	    PARTITION BY manufacturer 
	    ORDER BY `month` 
	    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	  ) AS running_average 
FROM 
  sales;

frame 子句語法：

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

在沒有frame子句的情況下，預設frame取決於是否存在ORDER BY子句。

frame_units值表示當前行和幀行之間的關係型別：

ROWS: frame由開始行和結束行位置定義。偏移量是行號與當前行號之間的差異。
RANGE: frame由值範圍內的行定義。偏移量是行值與當前行值之間的差異。

frame_extend 表示frame的起始點和結束點。可以只指定frame的開始（在這種情況下，當前行隱式地是結束）或使用BETWEEN指定frame的兩個端點：

frame_extent:
    {frame_start | frame_between}

frame_between:
    BETWEEN frame_start AND frame_end

frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}

使用BETWEEN語法，frame_start不能發生在frame_end之後。

允許的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，則繫結為該行的對等體。

下面是一些有效expr PRECEDING 和expr FOLLOWING 範例：

10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING

在沒有frame子句的情況下，預設的frame取決於是否存在ORDER BY子句：

有ORDER BY：預設frame包括從分割區開始到當前行的行，包括當前行的所有對等點。與之等效的frame如下：
```
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
```
沒有ORDER BY：預設frame包括所有的分割區行（因為，如果沒有ORDER BY，所有的分割區行都是對等的）。與之等效的frame如下：
```
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
```

因為預設frame會根據是否存在ORDER BY而有所不同，所以向查詢新增ORDER BY以獲得確定性結果可能會更改結果。要獲得相同的結果，但按ORDER BY排序，無論ORDER BY是否存在，都要提供要使用的顯式frame規範。

3. 視窗函數應用

範例資料

序號函數

select
	name, subject, score,
	rank() over w as 'rank',
	dense_rank() over w as 'dense_rank',
	row_number() over w as 'row_number'
from
	student 
window w as (partition by subject order by score desc);

可以看到，row_number就是個序號，rank在處理並列情況的時候會佔用後面的序號，而dense_rank不會

同時，這個SQL中使用了命名視窗寫法

Top-N問題：每個類別中取前N條

這類問題可以套用這個模板

SELECT * FROM (SELECT *,row_number() over (PARTITION BY 姓名 ORDER BY 成績 DESC) AS ranking FROM test) AS tmp WHERE tmp.ranking <= N;

查詢每科第一名

select * from ( 
	select
		name, subject, score,
		dense_rank() over(partition by subject order by score desc) as 'rn'
	from
		student 
) tmp where tmp.rn = 1;

每科前三名

select * from ( 
	select
		name,
		subject,
		score,
		row_number() over(partition by subject order by score desc) as 'rn'
	from
		student 
) tmp where tmp.rn <= 3;

每科高於平均分數（寫法一）

select * from (
	select
		name, subject, score,
		avg(score) over(partition by subject) as 'avg_score'
	from
		student
) tmp where tmp.score > tmp.avg_score;

高於每科平均分數（寫法二）

select
	name, subject, score
from
	student s
where
	s.score > (select avg(score) from student s2 where s2.subject = s.subject) 
order by s.subject asc;

聚集函數作視窗函數

select
	name, subject, score,
	first_value(score) over(partition by subject order by score desc) as '單科最高分',
	max(score) over(partition by subject) as '科目最高分',
	min(score) over(partition by subject) as '科目最低分',
	avg(score) over(partition by subject) as '科目平均分',
	sum(score) over(partition by subject order by score desc rows between unbounded preceding and current row) as '總分',
	sum(score) over(partition by name) as '學生總分',
	count(subject) over (partition by name) as '參加的學科數'
from
	student order by subject;

假設90分算及格，求每個學生的及格率

select
	t1.name,
	t1.pass_num as '通過的科目數',
	t2.total_num as '參加的科目數',
	concat(round((t1.pass_num / t2.total_num) * 100, 2), '%') as '及格率'
from
	(select name, count(*) pass_num from student where score > 90 group by name) t1
left join (select name, count(*) total_num from student group by name) t2 
	on t1.name = t2.name;

最後，視窗函數只能在查詢或子查詢中使用，不能在UPDATE或DELETE語句中使用它們來更新行。