超級無敵乾貨，第一時間送達！！！
作者：五四青年
來源：https://zhuanlan.zhihu.com/p/514345120FIRST_VALUE()與LAST_VALUE()MAX()、MIN()、AVG()、SUM()與COUNT()前言MySQL從8.0版本開始支持窗口函數了，窗口函數又名開窗函數，屬於分析函數的一種。用於解決複雜報表統計需求的功能強大的函數。窗口函數用於計算基於組（GROUP BY）的某種聚合值，它和聚合函數的不同之處是：窗口函數可以在分組之後的返回多行結果，而聚合函數對於每個組只返回一行。開窗函數指定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨着行的變化而變化。
窗口函數的格式
Function()over(partition by query_patition_clause order by order_by_clause Window_clause )不是所有的函數（Function）都支持開窗函數。目前支持的窗口函數可結合的函數有：排名函數 RANK() 和 DENSE_RANK()；取值函數 First_value()和last_value()；統計函數，也就是我們常用的聚合函數 MAX()、MIN()、AVG()、SUM()、COUNT()我們在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 | rangebetween start_expr andend_exprrows是物理範圍，即根據order by子句排序後，取的前N行及後N行的數據計算（與當前行的值無關，只與排序後的行號相關）；range是邏輯範圍，根據order by子句排序後，指定當前行對應值的範圍取值，行數不固定，只要行值在範圍內，對應行都包含在內between…and...用來指定範圍的起始點和終結點，start_expr為起始點，end_expr為終結點Start_expr為起始點，起始點有下面幾種選項：unbounded preceding：指明窗口開始於分組的第一行，以排序之後的第一行為起點；n preceding：以當前行的前面第n行為起點；n following：以當前行的後面第n行為起點；unbounded following：以排序之後的最後一行為終點；n preceding：以當前行的前面第n行為終點；n following：以當前行的後面第n行為終點；使用一個具體的實例來說明窗口函數使用方法，首先創建一個測試表，有字段id，name和sale，借用實際生活中的例子，假設一個公司有銷售部門（id）為1和2，每個部門內有若干個成員（name），每個成員有自己的銷售業績（sale），然後就可以使用一些函數來做統計，首先創建測試表test，並且只對一個分組（id=1）進行分析createtabletest(idint,namevarchar(10),sale int);insertintotestvalues(1,'aaa',100);insertintotestvalues(1,'bbb',200);insertintotestvalues(1,'ccc',200);insertintotestvalues(1,'ddd',300);insertintotestvalues(2,'eee',400);insertintotestvalues(2,'fff',200);表中的數據為：
mysql> select* fromtest;+------+------+------+| 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 bycol1 order bycol2)row_number函數根據字段col1進行分組，在分組內部根據字段col2進行排序，而此函數計算的值就表示每組內部排序後的順序編號（組內的排序是連續且唯一的），例如：mysql> #對id進行分組，同一個組內的數據再根據sale進行排序，這個排序序號是唯一併且連續的mysql> selectt.*,row_number() over(partitionbyidorderbysale) asrank1 -> fromtestast;+------+------+------+-------+| 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.00sec)mysql> #當沒有partitionby分組從句時，將視全部記錄為一個分組mysql> selectt.*,row_number() over(orderbysale) asrank1 fromtestast;+------+------+------+-------+| 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.00sec)RANK()與DENSE_RANK()
rank() over(partition bycol1 order bycol2)rank函數根據字段col1進行分組，在分組內部根據字段col2進行跳躍排序，有相同的排名時，相同排名的數據有相同的序號，排序序號不連續；dense_rank() over(partition bycol1 order bycol2)dense_rank函數根據字段col1進行分組，在分組內部根據字段col2進行連續排序，有相同的排名時，相同排名的數據有相同的序號，但是排序序號連續，rank函數和dense_rank函數的區別看例子：mysql> #對id進行分組，分組後根據sale排序mysql> #可以發現sale相同時有相同的序號，並且由於id=1的分組中沒有排名第3的序號造成排序不連續mysql> selectt.*,rank() over(partitionbyidorderbysale) asrank1-> fromtestast;+------+------+------+-------+| 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.00sec)mysql> #沒有分組，只根據sale排序，sale相同時有相同的序號，沒有排名3和4造成排序不連續mysql> selectt.*,rank() over(orderbysale) asrank1 fromtestast;+------+------+------+-------+| 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.00sec)以上是rank函數的用法，再看dense_rank函數mysql> #對id進行分組，分組後根據sale排序mysql> #可以發現sale相同時有相同的序號，但是整個排序序號是連續的mysql> selectt.*,dense_rank() over(partitionbyidorderbysale) asrank1 -> fromtestast;+------+------+------+-------+| 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.00sec)mysql> #沒有分組，只根據sale排序，sale相同時有相同的序號，整個排序序號是連續的mysql> selectt.*,dense_rank() over(orderbysale) asrank1 fromtestast;+------+------+------+-------+| 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.00sec)到這裡小結一下，row_number函數，rank函數和dense_rank函數都是一種排名函數，他們有以下區別：row_number是沒有重複的一種排序，即使對於兩行相同的數據，也會根據查詢到的順序進行排名；而rank函數和dense_rank函數對相同的數據會有一個相同的次序；rank函數的排序是可能不連續的，dense_rank函數的排序是連續的lead函數與lag函數是兩個偏移量函數，主要用於查找當前行字段的上一個值或者下一個值。lead函數是向下取值，lag函數是向上取值，如果向上取值或向下取值沒有數據的時候顯示為NULL，這兩個函數的格式為：lead(EXPR,<OFFSET>,<DEFAULT>) over（partition by col1 orderby col2）lag(EXPR,<OFFSET>,<DEFAULT>) over（partition by col1 orderby col2）其中：
EXPR通常是直接是列名，也可以是從其他行返回的表達式；OFFSET是默認為1，表示在當前分區內基於當前行的偏移行數；DEFAULT是在OFFSET指定的偏移行數超出了分組的範圍時(因為默認會返回null)，可以通過設置這個字段來返回一個默認值來替代null。看具體例子，下面是lead函數和lag函數的基本用法，參數只有目標字段，則OFFSET偏移量默認為1，DEFAULT默認為NULLmysql> #為每一行數據的下一行數據進行開窗，如果該行沒有下一行數據，則顯示為NULLmysql> selectt.*,lead(sale) over(partitionbyidorderbysale) asrank1 -> fromtestast;+------+------+------+-------+| 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.00sec)mysql> #為每一行數據的上一行數據進行開窗，如果該行沒有上一行數據，則顯示為NULLmysql> selectt.*,lag(sale) over(partitionbyidorderbysale) asrank1-> fromtestast;+------+------+------+-------+| 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.00sec)將OFFSET偏移量設置為2，即可以查到當前行的後面第2行的數據，如果當前行的往下數2行沒有數據，則會顯示NULL，看例子：mysql> selectt.*,lead(sale,2) over(partitionbyidorderbysale) asrank1-> fromtestast;+------+------+------+-------+| 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.00sec)將OFFSET偏移量設置為2，同時將DEFAULT設置為"Empty",如果當前行的往下數2行沒有數據，則會顯示"Empty"，即把默認顯示的NULL換成我們自定義的顯示內容，看例子：mysql> selectt.*,lead(sale,2,"Empty") over(partitionbyidorderbysale) asrank1-> fromtestast;+------+------+------+-------+| 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.00sec)DEFAULT內容也可以顯示其它字段的信息，例如有這個場景：如果下面行沒有數據，則顯示它自己這一行，只要把DEFAULT換成sale字段即可，可以自作嘗試這裡需要指出的是lead函數和lag函數中三個參數的順序是固定的，即第一個參數EXPR，一般為某一個字段或者其它表達式；第二個參數是偏移量，第三個參數是顯示的默認值，例如，我們只傳入一個參數mysql> #存在下一行數據顯示為Exist，不存在下一行數據則顯示NULL，這個NULL是默認的mysql> selectt.*,lead("Exist") over(partitionbyidorderbysale) asrank1 -> fromtestast;+------+------+------+-------+| 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.00sec)mysql> #存在下一行數據顯示為Exist，不存在下一行數據則顯示Emptymysql> selectt.*,lead("Exist",1,"Empty") over(partitionbyidorderbysale) -> asrank1 fromtestast;+------+------+------+-------+| 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.00sec)FIRST_VALUE()與LAST_VALUE()first_value( EXPR ) over( partition bycol1 order bycol2 )last_value( EXPR ) over( partition bycol1 order bycol2 )其中EXPR通常是直接是列名，也可以是從其他行返回的表達式，根據字段col1進行分組，在分組內部根據字段col2進行排序，first_value函數返回一組排序值後的第一個值，last_value返回一組排序值後的最後一個值mysql> #first_value函數查看每一個分組的第一個值mysql> selectt.*,first_value(sale) over(partitionbyid) asrank1 fromtestast;+------+------+------+-------+| 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.00sec)mysql> #對id進行分組，同一個組內的數據再根據sale進行排序，查看每一個分組的第一個值mysql> selectt.*,first_value(sale) over(partitionbyidorderbysale)-> asrank1 fromtestast;+------+------+------+-------+| 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.00sec)mysql> #last_value函數查看每一個分組的最後一個值mysql> selectt.*,last_value(sale) over(partitionbyid) asrank1 -> fromtestast;+------+------+------+-------+| 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.00sec)如果你使用下列代碼進行分組並排序之後，查詢最後一個值，那麼得到的結果可能會和你想象中的不一樣mysql> #對id進行分組，同一個組內的數據再根據sale進行排序，查看每一個分組的最後一個值mysql> #但是你發現id=1的組每一行顯示的不是300，id=2的分組每一行顯示的不是400mysql> selectt.*,last_value(sale) over(partitionbyidorderbysale) asrank1-> fromtestast;+------+------+------+-------+| 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.00sec)不要急~你使用的語法沒有錯誤，邏輯也沒有錯誤，這種理想偏差來自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_num） OVER ( partition bycol1 order bycol2 )ntile_num是一個整數，用於創建「桶」的數量，即分組的數量，不能小於等於0。其次需要注意的是，在over函數內，儘量要有排序ORDER BY子句這裡因為我平時用不到NTILE函數，如果統計分析學需要的同學，可以自己再去深度研究一下，因為我這個案例中數據量太小，發揮不了NTILE函數的作用，簡單說明用法：mysql> 給所有數據分配四個桶mysql> selectt.*,ntile(4) over(partitionbyidorderbysale) asrank1 fromtestast;+------+------+------+-------+| 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.00sec)MAX()、MIN()、AVG()、SUM()與COUNT()我們知道聚合函數的語法是一樣的，可以實現不一樣的統計功能max(EXPR) over(partition bycol1 order bycol2)min(EXPR) over(partition bycol1 order bycol2)avg(EXPR) over(partition bycol1 order bycol2)sum(EXPR) over(partition bycol1 order bycol2)count(EXPR) over(partition bycol1 order bycol2)為了測試聚合函數，我這裡使用另一個測試表，而且在下面的例子中，我先用max函數求最大值為例，因為大家都知道聚合函數五兄弟用法是一模一樣的mysql> createtabletest( idint, val int);mysql> insertintotestvalues(1,1),(1,2),(1,3),(1,4),(1,5),(2,6),-> (2,7),(2,8),(2,9),(1,3),(1,5);mysql> select* fromtest;+------+------+| 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.00sec)只有分組，沒有排序，顯示分組的最大值mysql> selectt.*,max(val) over(partitionbyid) asMAXfromtestast;+------+------+------+| 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.00sec)如果既有分組也有排序，那麼排序之後的開窗函數是默認排序之後第一行數據到當前行（邏輯層面）的最大值，那麼可想而知，既然已經排序了，那麼當前行肯定是最大值，就會出現下面的現象，我會在表的旁邊加上注釋mysql> selectt.*,max(val) over(partitionbyidorderbyval) asMAX-> fromtestast;+------+------+------+| 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.00sec)其實，在上面這個代碼中，完整的顯示是這樣的：
mysql> selectt.*,max(val) over(partitionbyidorderbyval rangebetweenunboundedprecedingandcurrentrow) -> asMAXfromtestast;+------+------+------+| 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.00sec)其中代碼
rangebetween unbounded preceding andcurrent row是排序之後的默認窗口從句，它表示了一個範圍，通過between...and...指定一個範圍，unbounded preceding表示排序之後的第一行，current row表示當前行。其中range是邏輯層面的範圍，邏輯範圍意思是排序之後把具有相同的值看成同一行，例如上面第3、4行有兩個相同的值val=3，那麼會把第三行和第三行看成同一行，所以range與排序之後的行號是沒有關係的，取定的範圍和字段值有關；與之相對應的是rows物理範圍，物理範圍就是嚴格根據排序之後的行號所確定的，例如：rows between unbounded preceding andcurrent row現在你可以回開頭再仔細研究窗口從句的用法了，我們一起來看一個例子幫助你理解窗口子句的用法：mysql> selectt.*,max(val) over(partitionbyidorderbyval rowsbetweenunboundedprecedingandunboundedfollowing) asMAX-> fromtestast;+------+------+------+| 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.00sec)rows between unbounded preceding andunbounded followingrows是物理範圍，只和排序之後的行號有關，和當前行的數值無關，between...and...圈示了一個範圍，unbounded preceding表示排序之後的第一行，unbounded following表示排序之後的最後一行，因此得到上面的結果，就是可以取得每個分組從第一行開始到最後一行之間這個範圍的最大值接下來，我會用幾個具體例子來更好的說明窗口從句的使用學完聚合函數之後，就可以研究窗口子句的使用方法了，這裡我們還是使用上面那個表test，換用sum函數來學進行說明，示例一，只使用分組，沒有排序：mysql> #分組之後沒有排序，就沒有默認的窗口子句，得到的結果是每一組的最大值mysql> selectt.*,sum(val) over(partitionbyid) asSUMfromtestast;+------+------+------+| 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.00sec)示例二，同時使用分組和排序：
mysql> #分組並且排序mysql> #排序如果沒有窗口子句會有一個默認的規則，即range between unbounded preceding and current rowmysql> selectt.*,sum(val) over(partitionbyidorderbyval) -> asSUMfromtestast;+------+------+------+| 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.00sec)有興趣的同學可以證明示例二的正確性，在排序之後手動添加窗口子句，一定會得到相同的結果：
mysql> #得到和上面一樣的結果Orzmysql> selectt.*,sum(val) over(partitionbyidorderbyval rangebetweenunboundedprecedingandcurrentrow) -> asSUMfromtestast;+------+------+------+| 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.00sec)示例三，同時使用了分組和排序，但是窗口從句使用物理範圍rows：
mysql> selectt.*,sum(val) over(partitionbyidorderbyval rowsbetweenunboundedprecedingandcurrentrow) -> asSUMfromtestast;+------+------+------+| 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.00sec)rows是物理範圍，聚合函數的生效範圍是嚴格根據行號來的，這種用法也更好解釋，但是實際生活中可能使用邏輯範圍range應用更廣泛，舉一個實際的栗子來說明：班級內相同成績的學生是有相同的名次的，那麼老師在計算平均分的時候肯定是用邏輯範圍進行相加再求平均值，不可能具有相同的分數的若干個同學中只取了一個希望通過上面三個例子能幫助你初步了解什麼是窗口從句及其使用語法，到這裡你可能會想，為什麼範圍總是要從第一行開始呢？可不可以自己自定義一個範圍呢，答案是可以的，而且可以是任意範圍，例如：mysql> #使用rows物理範圍mysql> #使用1 preceding表示當前行的前一行作為起點mysql> #使用1 following表示當前行的後一行作為終點mysql> selectt.*,max(val) over(partitionbyidorderbyval rowsbetween1precedingand1following) -> asMAXfromtestast;+------+------+------+| 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.00sec)再來試試使用range邏輯範圍，會產生什麼奇妙的結果，這次我們使用sum函數
mysql> #使用range邏輯範圍mysql> #使用1 preceding表示當前行的前一行作為起點mysql> #使用1 following表示當前行的後一行作為終點mysql> selectt.*,sum(val) over(partitionbyidorderbyval rangebetween1precedingand1following)-> asSUMfromtestast;+------+------+------+| 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.00sec)現在你就徹底弄清楚了邏輯範圍range和物理範圍rows的區別了~歡迎大家討論補充，如有不對或者哪裡有描述不準確或歧義的地方，敬請指正，感謝~~<end>
python爬取B站Top100，發現了這些熱門UP主的秘密！
python爬取某東實戰|358元的月餅憑什麼能賣20萬單？
python爬蟲，13行代碼把2520個美女帶回家，快速掌握
留言列表