日韩精品中文字幕视频一区,成人99国产精品,91精品国产高清久久久久久91

前言
窗口函數的格式
函數（Function）的類型
開窗函數over()
窗口函數使用
ROW_NUMBER()
RANK()與DENSE_RANK()
LEAD()與LAG()
FIRST_VALUE()與LAST_VALUE()
NTILE()
MAX()、MIN()、AVG()、SUM()與COUNT()
窗口從句的使用
窗口從句進階

前言

MySQL從8.0版本開始支持窗口函數了，窗口函數又名開窗函數，屬于分析函數的一種。用于解決復雜報表統計需求的功能強大的函數。窗口函數用于計算基于組（GROUP BY）的某種聚合值，它和聚合函數的不同之處是：窗口函數可以在分組之后的返回多行結果，而聚合函數對于每個組只返回一行。開窗函數指定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變化而變化。

窗口函數經常會在leetCode的題目中使用到

窗口函數的格式

Function() over(partition by query_patition_clause
order by order_by_clause Window_clause )

函數（Function）的類型

不是所有的函數（Function）都支持開窗函數。目前支持的窗口函數可結合的函數有：

排名函數 ROW_NUMBER()；
排名函數 RANK() 和 DENSE_RANK()；
錯行函數 lead()、lag()；
取值函數 First_value()和last_value()；
分箱函數 NTILE()；
統計函數，也就是我們常用的聚合函數 MAX()、MIN()、AVG()、SUM()、COUNT()

開窗函數over()

我們在Function函數之后需要跟上一個開窗函數over()，over()函數參數包括了三個子句（分組子句，排序子句和窗口子句），根據實際需求選擇子句：

partition by query_patition_clause：即分組，通過query_patition_clause進行分組，一般是表中的某一個字段，所以可以把partition by 看作與GROUP BY 具有相同功能的語法。
order by order_by_clause：即排序，通過order_by_clause 進行排序，一般是在分組（partition by）之后再進行排序，如此一來，就是在組內進行排序。如果沒有前面的分組子句（partition by），那么就是全部數據進行排序。和普通MySQL中的查詢語句一樣，排序從句也支持ASC和DESC的用法。
Window_clause：窗口從句，它是排序之后的功能擴展，它標識了在排序之后的一個范圍，它的格式是：
```
rows | range between start_expr and end_expr
```

其中rows和range為二選其一：

rows是物理范圍，即根據order by子句排序后，取的前N行及后N行的數據計算（與當前行的值無關，只與排序后的行號相關）；
range是邏輯范圍，根據order by子句排序后，指定當前行對應值的范圍取值，行數不固定，只要行值在范圍內，對應行都包含在內

between…and...用來指定范圍的起始點和終結點，start_expr為起始點，end_expr為終結點

Start_expr為起始點，起始點有下面幾種選項：

unbounded preceding：指明窗口開始于分組的第一行，以排序之后的第一行為起點；
current row：以當前行為起點；
n preceding：以當前行的前面第n行為起點；
n following：以當前行的后面第n行為起點；

end_expr為終結點，終結點有下面幾種選項：

unbounded following：以排序之后的最后一行為終點；
current row：以當前行為終點；
n preceding：以當前行的前面第n行為終點；
n following：以當前行的后面第n行為終點；

窗口函數使用

使用一個具體的實例來說明窗口函數使用方法，首先創建一個測試表，有字段id，name和sale，借用實際生活中的例子，假設一個公司有銷售部門（id）為1和2，每個部門內有若干個成員（name），每個成員有自己的銷售業績（sale），然后就可以使用一些函數來做統計，首先創建測試表test，并且只對一個分組（id=1）進行分析

create table test(id int,name varchar(10),sale int);
insert into test values(1,'aaa',100);
insert into test values(1,'bbb',200);
insert into test values(1,'ccc',200);
insert into test values(1,'ddd',300);
insert into test values(2,'eee',400);
insert into test values(2,'fff',200);

表中的數據為：

mysql> select * from test;
+------+------+------+
| id | name | sale |
+------+------+------+
| 1 | aaa | 100 |
| 1 | bbb | 200 |
| 1 | ccc | 200 |
| 1 | ddd | 300 |
| 2 | eee | 400 |
| 2 | fff | 200 |
+------+------+------+

ROW_NUMBER()

row_number() over(partition by col1 order by col2)

row_number函數根據字段col1進行分組，在分組內部根據字段col2進行排序，而此函數計算的值就表示每組內部排序后的順序編號（組內的排序是連續且唯一的），例如：

mysql> #對id進行分組，同一個組內的數據再根據sale進行排序，這個排序序號是唯一并且連續的
mysql> select t.*,row_number() over(partition by id order by sale) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 1 |
| 1 | bbb | 200 | 2 |
| 1 | ccc | 200 | 3 |
| 1 | ddd | 300 | 4 |
| 2 | fff | 200 | 1 |
| 2 | eee | 400 | 2 |
+------+------+------+-------+
6 rows in set (0.00 sec)

mysql> #當沒有partition by分組從句時，將視全部記錄為一個分組
mysql> select t.*,row_number() over(order by sale) as rank1 from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 1 |
| 1 | bbb | 200 | 2 |
| 1 | ccc | 200 | 3 |
| 2 | fff | 200 | 4 |
| 1 | ddd | 300 | 5 |
| 2 | eee | 400 | 6 |
+------+------+------+-------+
6 rows in set (0.00 sec)

RANK()與DENSE_RANK()

rank() over(partition by col1 order by col2)

rank函數根據字段col1進行分組，在分組內部根據字段col2進行跳躍排序，有相同的排名時，相同排名的數據有相同的序號，排序序號不連續；

dense_rank() over(partition by col1 order by col2)

dense_rank函數根據字段col1進行分組，在分組內部根據字段col2進行連續排序，有相同的排名時，相同排名的數據有相同的序號，但是排序序號連續，rank函數和dense_rank函數的區別看例子：

mysql> #對id進行分組，分組后根據sale排序
mysql> #可以發現sale相同時有相同的序號，并且由于id=1的分組中沒有排名第3的序號造成排序不連續
mysql> select t.*,rank() over(partition by id order by sale) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 1 |
| 1 | bbb | 200 | 2 |
| 1 | ccc | 200 | 2 |
| 1 | ddd | 300 | 4 |
| 2 | fff | 200 | 1 |
| 2 | eee | 400 | 2 |
+------+------+------+-------+
6 rows in set (0.00 sec)

mysql> #沒有分組，只根據sale排序，sale相同時有相同的序號，沒有排名3和4造成排序不連續
mysql> select t.*,rank() over(order by sale) as rank1 from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 1 |
| 1 | bbb | 200 | 2 |
| 1 | ccc | 200 | 2 |
| 2 | fff | 200 | 2 |
| 1 | ddd | 300 | 5 |
| 2 | eee | 400 | 6 |
+------+------+------+-------+
6 rows in set (0.00 sec)

以上是rank函數的用法，再看dense_rank函數

mysql> #對id進行分組，分組后根據sale排序
mysql> #可以發現sale相同時有相同的序號，但是整個排序序號是連續的
mysql> select t.*,dense_rank() over(partition by id order by sale) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 1 |
| 1 | bbb | 200 | 2 |
| 1 | ccc | 200 | 2 |
| 1 | ddd | 300 | 3 |
| 2 | fff | 200 | 1 |
| 2 | eee | 400 | 2 |
+------+------+------+-------+
6 rows in set (0.00 sec)

mysql> #沒有分組，只根據sale排序，sale相同時有相同的序號，整個排序序號是連續的
mysql> select t.*,dense_rank() over(order by sale) as rank1 from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 1 |
| 1 | bbb | 200 | 2 |
| 1 | ccc | 200 | 2 |
| 2 | fff | 200 | 2 |
| 1 | ddd | 300 | 3 |
| 2 | eee | 400 | 4 |
+------+------+------+-------+
6 rows in set (0.00 sec)

到這里小結一下，row_number函數，rank函數和dense_rank函數都是一種排名函數，他們有以下區別：

row_number是沒有重復的一種排序，即使對于兩行相同的數據，也會根據查詢到的順序進行排名；而rank函數和dense_rank函數對相同的數據會有一個相同的次序；
rank函數的排序是可能不連續的，dense_rank函數的排序是連續的

LEAD()與LAG()

lead函數與lag函數是兩個偏移量函數，主要用于查找當前行字段的上一個值或者下一個值。lead函數是向下取值，lag函數是向上取值，如果向上取值或向下取值沒有數據的時候顯示為NULL，這兩個函數的格式為：

lead(EXPR,<OFFSET>,<DEFAULT>) over（partition by col1 order by col2）

lag(EXPR,<OFFSET>,<DEFAULT>) over（partition by col1 order by col2）

其中：

EXPR通常是直接是列名，也可以是從其他行返回的表達式；
OFFSET是默認為1，表示在當前分區內基于當前行的偏移行數；
DEFAULT是在OFFSET指定的偏移行數超出了分組的范圍時(因為默認會返回null)，可以通過設置這個字段來返回一個默認值來替代null。

看具體例子，下面是lead函數和lag函數的基本用法，參數只有目標字段，則OFFSET偏移量默認為1，DEFAULT默認為NULL

mysql> #為每一行數據的下一行數據進行開窗，如果該行沒有下一行數據，則顯示為NULL
mysql> select t.*,lead(sale) over(partition by id order by sale) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 200 | <--下一行的sale值為200，開窗結果為200
| 1 | bbb | 200 | 200 | <--下一行的sale值為200，開窗結果為200
| 1 | ccc | 200 | 300 | <--下一行的sale值為300，開窗結果為300
| 1 | ddd | 300 | NULL | <--已經是最后一行，沒有下一行數據，開窗結果為NULL
| 2 | fff | 200 | 400 |
| 2 | eee | 400 | NULL |
+------+------+------+-------+
6 rows in set (0.00 sec)


mysql> #為每一行數據的上一行數據進行開窗，如果該行沒有上一行數據，則顯示為NULL
mysql> select t.*,lag(sale) over(partition by id order by sale) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | NULL | <--當前行為第一行，沒有上一行數據，開窗結果為NULL
| 1 | bbb | 200 | 100 | <--上一行的sale值為100，開窗結果為100
| 1 | ccc | 200 | 200 | <--上一行的sale值為200，開窗結果為200
| 1 | ddd | 300 | 200 | <--上一行的sale值為200，開窗結果為200
| 2 | fff | 200 | NULL |
| 2 | eee | 400 | 200 |
+------+------+------+-------+
6 rows in set (0.00 sec)

將OFFSET偏移量設置為2，即可以查到當前行的后面第2行的數據，如果當前行的往下數2行沒有數據，則會顯示NULL，看例子：

mysql> select t.*,lead(sale,2) over(partition by id order by sale) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 200 | <--下2行的sale值為200，開窗結果為200
| 1 | bbb | 200 | 300 | <--下2行的sale值為300，開窗結果為300
| 1 | ccc | 200 | NULL | <--已經是倒數第2行，沒有下2行的數據，開窗結果為NULL
| 1 | ddd | 300 | NULL | <--已經是最后一行，沒有下2行的數據，開窗結果為NULL
| 2 | fff | 200 | NULL |
| 2 | eee | 400 | NULL |
+------+------+------+-------+
6 rows in set (0.00 sec)

將OFFSET偏移量設置為2，同時將DEFAULT設置為"Empty",如果當前行的往下數2行沒有數據，則會顯示"Empty"，即把默認顯示的NULL換成我們自定義的顯示內容，看例子：

mysql> select t.*,lead(sale,2,"Empty") over(partition by id order by sale) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 200 |
| 1 | bbb | 200 | 300 |
| 1 | ccc | 200 | Empty | <--已經是倒數第2行，沒有下2行的數據，開窗結果為"Empty"
| 1 | ddd | 300 | Empty | <--已經是最后一行，沒有下2行的數據，開窗結果為"Empty"
| 2 | fff | 200 | Empty |
| 2 | eee | 400 | Empty |
+------+------+------+-------+
6 rows in set (0.00 sec)

DEFAULT內容也可以顯示其它字段的信息，例如有這個場景：如果下面行沒有數據，則顯示它自己這一行，只要把DEFAULT換成sale字段即可，可以自作嘗試

這里需要指出的是lead函數和lag函數中三個參數的順序是固定的，即第一個參數EXPR，一般為某一個字段或者其它表達式；第二個參數是偏移量，第三個參數是顯示的默認值，例如，我們只傳入一個參數

mysql> #存在下一行數據顯示為Exist，不存在下一行數據則顯示NULL，這個NULL是默認的
mysql> select t.*,lead("Exist") over(partition by id order by sale) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | Exist | <--下一行的數據存在，開窗結果為"Exist"
| 1 | bbb | 200 | Exist | <--下一行的數據存在，開窗結果為"Exist"
| 1 | ccc | 200 | Exist | <--下一行的數據存在，開窗結果為"Exist"
| 1 | ddd | 300 | NULL | <--已經是最后一行，沒有下一行數據，開窗結果為NULL
| 2 | fff | 200 | Exist |
| 2 | eee | 400 | NULL |
+------+------+------+-------+
6 rows in set (0.00 sec)


mysql> #存在下一行數據顯示為Exist，不存在下一行數據則顯示Empty
mysql> select t.*,lead("Exist",1,"Empty") over(partition by id order by sale)
    -> as rank1 from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | Exist | <--下一行的數據存在，開窗結果為"Exist"
| 1 | bbb | 200 | Exist | <--下一行的數據存在，開窗結果為"Exist"
| 1 | ccc | 200 | Exist | <--下一行的數據存在，開窗結果為"Exist"
| 1 | ddd | 300 | Empty | <--已經是最后一行，沒有下一行數據，開窗結果為"Empty"
| 2 | fff | 200 | Exist |
| 2 | eee | 400 | Empty |
+------+------+------+-------+
6 rows in set (0.00 sec)

FIRST_VALUE()與LAST_VALUE()

first_value( EXPR ) over( partition by col1 order by col2 )

last_value( EXPR ) over( partition by col1 order by col2 )

其中EXPR通常是直接是列名，也可以是從其他行返回的表達式，根據字段col1進行分組，在分組內部根據字段col2進行排序，first_value函數返回一組排序值后的第一個值，last_value返回一組排序值后的最后一個值

mysql> #first_value函數查看每一個分組的第一個值
mysql> select t.*,first_value(sale) over(partition by id) as rank1 from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 100 | <--分組的第一個值為100，開窗結果100
| 1 | bbb | 200 | 100 | <--分組的第一個值為100，開窗結果100
| 1 | ccc | 200 | 100 | <--分組的第一個值為100，開窗結果100
| 1 | ddd | 300 | 100 | <--分組的第一個值為100，開窗結果100
| 2 | eee | 400 | 400 |
| 2 | fff | 200 | 400 |
+------+------+------+-------+
6 rows in set (0.00 sec)


mysql> #對id進行分組，同一個組內的數據再根據sale進行排序，查看每一個分組的第一個值
mysql> select t.*,first_value(sale) over(partition by id order by sale)
    -> as rank1 from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 100 | <--分組排序之后的第一個值為100，開窗結果100
| 1 | bbb | 200 | 100 | <--分組排序之后的第一個值為100，開窗結果100
| 1 | ccc | 200 | 100 | <--分組排序之后的第一個值為100，開窗結果100
| 1 | ddd | 300 | 100 | <--分組排序之后的第一個值為100，開窗結果100
| 2 | fff | 200 | 200 |
| 2 | eee | 400 | 200 |
+------+------+------+-------+
6 rows in set (0.00 sec)


mysql> #last_value函數查看每一個分組的最后一個值
mysql> select t.*,last_value(sale) over(partition by id) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 300 | <--分組排序之后的最后一個值為300，開窗結果300
| 1 | bbb | 200 | 300 | <--分組排序之后的最后一個值為300，開窗結果300
| 1 | ccc | 200 | 300 | <--分組排序之后的最后一個值為300，開窗結果300
| 1 | ddd | 300 | 300 | <--分組排序之后的最后一個值為300，開窗結果300
| 2 | eee | 400 | 200 |
| 2 | fff | 200 | 200 |
+------+------+------+-------+
6 rows in set (0.00 sec)

如果你使用下列代碼進行分組并排序之后，查詢最后一個值，那么得到的結果可能會和你想象中的不一樣

mysql> #對id進行分組，同一個組內的數據再根據sale進行排序，查看每一個分組的最后一個值
mysql> #但是你發現id=1的組每一行顯示的不是300，id=2的分組每一行顯示的不是400
mysql> select t.*,last_value(sale) over(partition by id order by sale) as rank1
    -> from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 100 |
| 1 | bbb | 200 | 200 |
| 1 | ccc | 200 | 200 |
| 1 | ddd | 300 | 300 |
| 2 | fff | 200 | 200 |
| 2 | eee | 400 | 400 |
+------+------+------+-------+
6 rows in set (0.00 sec)

不要急~你使用的語法沒有錯誤，邏輯也沒有錯誤，這種理想偏差來自last_value函數的默認語法，因為在開窗函數over()中除了分組和排序，還有一個窗口的從句，在經過排序之后，使用last_value函數生效的范圍是第一行至當前行，在上面的例子id=1分組中，每一行顯示的所謂最后一個值last value來自第一行到當前行這個范圍內的最后一個，這里，我們僅對id=1組逐行分析，id=2分組同理可證，希望對你能理解上面代碼為什么會出現這種結果能夠有所幫助

查詢到第1行sale=100，只有當前一行，最后一個值只有100，開窗結果為100；
查詢到第2行sale=100，200兩個數據，最后一個值是200，開窗結果為200；
查詢到第3行sale=100，200，200三個數據，最后一個值是200，開窗結果為200；
查詢到四行sale=100，200，200，300四個數據，最后一個值是300，開窗結果為300，至此id=1的分組查詢完畢

這里還是需要注意：窗口從句有一個默認的規則，就和上面分析的一樣，是從排序之后第一行到當前行的范圍，這個規則是可以自己定義的，而且非常靈活，我會在最后會詳細介紹窗口從句的用法

NTILE()

NTILE函數對一個數據分區中的有序結果集進行劃分，舉一個生活中的例子，我們想要把一些雞蛋放入若干個籃子中，每個籃子可以看成一個組，然后為每個籃子分配一個唯一的組編號，這個組里面就有一些雞蛋。我們假設籃子的編號可以反映放在內部雞蛋的體積大小，例如編號較大的籃子里面放著一些體積較大的雞蛋，編號較小的籃子則放著體積較小的雞蛋，現在，因為體積特別大的雞蛋和特別小的雞蛋不適合放入規定范圍包裝盒內進行出售，所以要進行篩選，在進行分組之后，我們只需要拎出合適范圍的帶有編號的籃子就能拿到我們想要的雞蛋

NTILE函數在統計分析中是很有用的。例如，如果想移除異常值,我們可以將它們分組到頂部或底部的“桶”中，然后在統計分析的時候將這些值排除。在統計信息收集可以使用NTILE函數來計算直方圖信息邊界。在統計學術語中，NTILE函數創建等寬直方圖信息。其語法如下：

ntile(ntile_num） OVER ( partition by col1 order by col2 )

ntile_num是一個整數，用于創建“桶”的數量，即分組的數量，不能小于等于0。其次需要注意的是，在over函數內，盡量要有排序ORDER BY子句

這里因為我平時用不到NTILE函數，如果統計分析學需要的同學，可以自己再去深度研究一下，因為我這個案例中數據量太小，發揮不了NTILE函數的作用，簡單說明用法：

mysql> 給所有數據分配四個桶
mysql> select t.*,ntile(4) over(partition by id order by sale) as rank1 from test as t;
+------+------+------+-------+
| id | name | sale | rank1 |
+------+------+------+-------+
| 1 | aaa | 100 | 1 |
| 1 | bbb | 200 | 2 |
| 1 | ccc | 200 | 3 |
| 1 | ddd | 300 | 4 |
| 2 | fff | 200 | 1 |
| 2 | eee | 400 | 2 |
+------+------+------+-------+
6 rows in set (0.00 sec)

MAX()、MIN()、AVG()、SUM()與COUNT()

我們知道聚合函數的語法是一樣的，可以實現不一樣的統計功能

max(EXPR) over(partition by col1 order by col2)
min(EXPR) over(partition by col1 order by col2)
avg(EXPR) over(partition by col1 order by col2)
sum(EXPR) over(partition by col1 order by col2)
count(EXPR) over(partition by col1 order by col2)

為了測試聚合函數，我這里使用另一個測試表，而且在下面的例子中，我先用max函數求最大值為例，因為大家都知道聚合函數五兄弟用法是一模一樣的

mysql> create table test( id int, val int );
mysql> insert into test values(1,1),(1,2),(1,3),(1,4),(1,5),(2,6),
    -> (2,7),(2,8),(2,9),(1,3),(1,5);
mysql> select * from test;
+------+------+
| id | val |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 6 |
| 2 | 7 |
| 2 | 8 |
| 2 | 9 |
| 1 | 3 |
| 1 | 5 |
+------+------+
11 rows in set (0.00 sec)
只有分組，沒有排序，顯示分組的最大值


mysql> select t.*,max(val) over(partition by id) as MAX from test as t;
+------+------+------+
| id | val | MAX |
+------+------+------+
| 1 | 1 | 5 |
| 1 | 2 | 5 |
| 1 | 3 | 5 |
| 1 | 4 | 5 |
| 1 | 5 | 5 |
| 1 | 3 | 5 |
| 1 | 5 | 5 |
| 2 | 6 | 9 |
| 2 | 7 | 9 |
| 2 | 8 | 9 |
| 2 | 9 | 9 |
+------+------+------+
11 rows in set (0.00 sec)

如果既有分組也有排序，那么排序之后的開窗函數是默認排序之后第一行數據到當前行（邏輯層面）的最大值，那么可想而知，既然已經排序了，那么當前行肯定是最大值，就會出現下面的現象，我會在表的旁邊加上注釋

mysql> select t.*,max(val) over(partition by id order by val) as MAX
    -> from test as t;
+------+------+------+
| id | val | MAX |
+------+------+------+
| 1 | 1 | 1 | <--第1行的最大值是1，所以顯示1
| 1 | 2 | 2 | <--前面2行的最大值是2，所以顯示2
| 1 | 3 | 3 | <--前面3行的最大值是3，所以顯示3
| 1 | 3 | 3 | <--前面4行的最大值是3，所以顯示3
| 1 | 4 | 4 | <--前面5行的最大值是4，所以顯示4
| 1 | 5 | 5 | <--前面6行的最大值是5，所以顯示5
| 1 | 5 | 5 | <--前面7行的最大值是5，所以顯示5
| 2 | 6 | 6 |
| 2 | 7 | 7 |
| 2 | 8 | 8 |
| 2 | 9 | 9 |
+------+------+------+
11 rows in set (0.00 sec)

其實，在上面這個代碼中，完整的顯示是這樣的：

mysql> select t.*,max(val) over(partition by id order by val range between unbounded preceding and current row)
    -> as MAX from test as t;
+------+------+------+
| id | val | MAX |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 2 | 2 |
| 1 | 3 | 3 |
| 1 | 3 | 3 |
| 1 | 4 | 4 |
| 1 | 5 | 5 |
| 1 | 5 | 5 |
| 2 | 6 | 6 |
| 2 | 7 | 7 |
| 2 | 8 | 8 |
| 2 | 9 | 9 |
+------+------+------+
11 rows in set (0.00 sec)

其中代碼

range between unbounded preceding and current row

是排序之后的默認窗口從句，它表示了一個范圍，通過between...and...指定一個范圍，unbounded preceding表示排序之后的第一行，current row表示當前行。

其中range是邏輯層面的范圍，邏輯范圍意思是排序之后把具有相同的值看成同一行，例如上面第3、4行有兩個相同的值val=3，那么會把第三行和第三行看成同一行，所以range與排序之后的行號是沒有關系的，取定的范圍和字段值有關；

與之相對應的是rows物理范圍，物理范圍就是嚴格根據排序之后的行號所確定的，例如：

rows between unbounded preceding and current row

現在你可以回開頭再仔細研究窗口從句的用法了，我們一起來看一個例子幫助你理解窗口子句的用法：

mysql> select t.*,max(val) over(partition by id order by val rows between unbounded preceding and unbounded following) as MAX 
    -> from test as t;
+------+------+------+
| id | val | MAX |
+------+------+------+
| 1 | 1 | 5 |
| 1 | 2 | 5 |
| 1 | 3 | 5 |
| 1 | 3 | 5 |
| 1 | 4 | 5 |
| 1 | 5 | 5 |
| 1 | 5 | 5 |
| 2 | 6 | 9 |
| 2 | 7 | 9 |
| 2 | 8 | 9 |
| 2 | 9 | 9 |
+------+------+------+
11 rows in set (0.00 sec)

在這里我們用了

rows between unbounded preceding and unbounded following

rows是物理范圍，只和排序之后的行號有關，和當前行的數值無關，between...and...圈示了一個范圍，unbounded preceding表示排序之后的第一行，unbounded following表示排序之后的最后一行，因此得到上面的結果，就是可以取得每個分組從第一行開始到最后一行之間這個范圍的最大值

接下來，我會用幾個具體例子來更好的說明窗口從句的使用

窗口從句的使用

學完聚合函數之后，就可以研究窗口子句的使用方法了，這里我們還是使用上面那個表test，換用sum函數來學進行說明，示例一，只使用分組，沒有排序：

mysql> #分組之后沒有排序，就沒有默認的窗口子句，得到的結果是每一組的最大值
mysql> select t.*,sum(val) over(partition by id) as SUM from test as t;
+------+------+------+
| id | val | SUM |
+------+------+------+
| 1 | 1 | 23 |
| 1 | 2 | 23 |
| 1 | 3 | 23 |
| 1 | 4 | 23 |
| 1 | 5 | 23 |
| 1 | 3 | 23 |
| 1 | 5 | 23 |
| 2 | 6 | 30 |
| 2 | 7 | 30 |
| 2 | 8 | 30 |
| 2 | 9 | 30 |
+------+------+------+
11 rows in set (0.00 sec)

示例二，同時使用分組和排序：

mysql> #分組并且排序
mysql> #排序如果沒有窗口子句會有一個默認的規則，即range between unbounded preceding and current row
mysql> select t.*,sum(val) over(partition by id order by val)
    -> as SUM from test as t;
+------+------+------+
| id | val | SUM |
+------+------+------+
| 1 | 1 | 1 | <--計算前1行的和，開窗結果為1
| 1 | 2 | 3 | <--計算前2行的和，開窗結果為3
| 1 | 3 | 9 | <--計算前3行的和，由于是range邏輯范圍，相同的val看作同一行，所以和為1+2+3+3=9
| 1 | 3 | 9 | <--計算前4行的和，該行和第三行同屬于一行，所以和為9，開窗結果為9
| 1 | 4 | 13 | <--計算前5行的和，開窗結果為13
| 1 | 5 | 23 | <--計算前6行的和，由于是range邏輯范圍，相同的val看作同一行，所以和為23
| 1 | 5 | 23 | <--計算前7行的和，該行和第6行同屬于一行，所以和為23，開窗結果為23
| 2 | 6 | 6 |
| 2 | 7 | 13 |
| 2 | 8 | 21 |
| 2 | 9 | 30 |
+------+------+------+
11 rows in set (0.00 sec)

有興趣的同學可以證明示例二的正確性，在排序之后手動添加窗口子句，一定會得到相同的結果：

mysql> #得到和上面一樣的結果Orz
mysql> select t.*,sum(val) over(partition by id order by val range between unbounded preceding and current row)
    -> as SUM from test as t;
+------+------+------+
| id | val | SUM |
+------+------+------+
| 1 | 1 | 1 |
| 1 | 2 | 3 |
| 1 | 3 | 9 |
| 1 | 3 | 9 |
| 1 | 4 | 13 |
| 1 | 5 | 23 |
| 1 | 5 | 23 |
| 2 | 6 | 6 |
| 2 | 7 | 13 |
| 2 | 8 | 21 |
| 2 | 9 | 30 |
+------+------+------+
11 rows in set (0.00 sec)

示例三，同時使用了分組和排序，但是窗口從句使用物理范圍rows：

mysql> select t.*,sum(val) over(partition by id order by val rows between unbounded preceding and current row)
    -> as SUM from test as t;
+------+------+------+
| id | val | SUM |
+------+------+------+
| 1 | 1 | 1 | <--計算前1行的和，開窗結果為1
| 1 | 2 | 3 | <--計算前2行的和，開窗結果為3
| 1 | 3 | 6 | <--計算前3行的和，開窗結果為1+2+3=6
| 1 | 3 | 9 | <--計算前4行的和，開窗結果為1+2+3+3=9
| 1 | 4 | 13 | <--計算前5行的和，開窗結果為1+2+3+3+4=13
| 1 | 5 | 18 | <--計算前6行的和，開窗結果為1+2+3+3+4+5=18
| 1 | 5 | 23 | <--計算前7行的和，開窗結果為1+2+3+3+4+5+5=23
| 2 | 6 | 6 |
| 2 | 7 | 13 |
| 2 | 8 | 21 |
| 2 | 9 | 30 |
+------+------+------+
11 rows in set (0.00 sec)

rows是物理范圍，聚合函數的生效范圍是嚴格根據行號來的，這種用法也更好解釋，但是實際生活中可能使用邏輯范圍range應用更廣泛，舉一個實際的栗子來說明：班級內相同成績的學生是有相同的名次的，那么老師在計算平均分的時候肯定是用邏輯范圍進行相加再求平均值，不可能具有相同的分數的若干個同學中只取了一個

窗口從句進階

希望通過上面三個例子能幫助你初步了解什么是窗口從句及其使用語法，到這里你可能會想，為什么范圍總是要從第一行開始呢？可不可以自己自定義一個范圍呢，答案是可以的，而且可以是任意范圍，例如：

mysql> #使用rows物理范圍
mysql> #使用1 preceding表示當前行的前一行作為起點
mysql> #使用1 following表示當前行的后一行作為終點
mysql> select t.*,max(val) over(partition by id order by val rows between 1 preceding and 1 following)
    -> as MAX from test as t;
+------+------+------+
| id | val | MAX |
+------+------+------+
| 1 | 1 | 2 | <--前一行NULL、當前行1、后一行2，比較而得的最大值，開窗結果為2
| 1 | 2 | 3 | <--前一行1、當前行2、后一行3，比較而得的最大值，開窗結果為3
| 1 | 3 | 3 | <--前一行2、當前行3、后一行3，比較而得的最大值，開窗結果為3
| 1 | 3 | 4 | <--前一行3、當前行3、后一行4，比較而得的最大值，開窗結果為4
| 1 | 4 | 5 | <--前一行3、當前行4、后一行5，比較而得的最大值，開窗結果為5
| 1 | 5 | 5 | <--前一行4、當前行5、后一行5，比較而得的最大值，開窗結果為5
| 1 | 5 | 5 | <--前一行5、當前行5、后一行NULL，比較而得的最大值，開窗結果為5
| 2 | 6 | 7 |
| 2 | 7 | 8 |
| 2 | 8 | 9 |
| 2 | 9 | 9 |
+------+------+------+
11 rows in set (0.00 sec)

再來試試使用range邏輯范圍，會產生什么奇妙的結果，這次我們使用sum函數

mysql> #使用range邏輯范圍
mysql> #使用1 preceding表示當前行的前一行作為起點
mysql> #使用1 following表示當前行的后一行作為終點
mysql> select t.*,sum(val) over(partition by id order by val range between 1 preceding and 1 following)
    -> as SUM from test as t;
+------+------+------+
| id | val | SUM |
+------+------+------+
| 1 | 1 | 3 | <--前一行NULL、當前行1、后一行2，1+2=3
| 1 | 2 | 9 | <--前一行1、當前行2、后一行有2個相同的值，邏輯上規定為同一行的3，1+2+3+3=9
| 1 | 3 | 12 | <--前一行2、當前行有2個相同的值，邏輯上規定為同一行的3、后一行4，2+3+3+4=12
| 1 | 3 | 12 | <--前一行2、當前行有2個相同的值，邏輯上規定為同一行的3、后一行4，2+3+3+4=12
| 1 | 4 | 20 | <--前一行有2個相同的值，邏輯上規定為同一行的3、當前行4、后一行有2個相同的值，邏輯上規定為同一行的5，3+3+4+5+5=20
| 1 | 5 | 14 | <--前一行4、當前行有2個相同的值，邏輯上規定為同一行的5、后一行NULL，4+5+5=14
| 1 | 5 | 14 | <--前一行4、當前行有2個相同的值，邏輯上規定為同一行的5、后一行NULL，4+5+5=14
| 2 | 6 | 13 |
| 2 | 7 | 21 |
| 2 | 8 | 24 |
| 2 | 9 | 17 |
+------+------+------+
11 rows in set (0.00 sec)

現在你就徹底弄清楚了邏輯范圍range和物理范圍rows的區別了~

歡迎大家討論補充，如有不對或者哪里有描述不準確或歧義的地方，敬請指正，感謝~~

作者：五四青年

來源：https://zhuanlan.zhihu.com/p/514345120

亚洲视频二区_亚洲欧洲日本天天堂在线观看_日韩一区二区在线观看_中文字幕不卡一区

SQL中的開窗函數

前言

數獨大挑戰2018-06-03

答題星2018-06-03

全階人生考試2018-06-03

運動步數有氧達人2018-06-03

每日養生app2018-06-03

體育訓練成績評定2018-06-03