五月丁香影视,影音先锋每日资源啪啪AV,国产麻豆操逼视频,中文黄色电影,91北条麻妃在线,亚洲第一福利区,特级西西444www无码视频免费看 ,亚洲人天堂

作者：五四青年

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

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

前言

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

窗口函數(shù)經(jīng)常會在leetCode的題目中使用到

窗口函數(shù)的格式

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

函數(shù)（Function）的類型

不是所有的函數(shù)（Function）都支持開窗函數(shù)。目前支持的窗口函數(shù)可結(jié)合的函數(shù)有：

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

開窗函數(shù)over()

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

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

其中rows和range為二選其一：

rows是物理范圍，即根據(jù)order by子句排序后，取的前N行及后N行的數(shù)據(jù)計算（與當(dāng)前行的值無關(guān)，只與排序后的行號相關(guān)）；
range是邏輯范圍，根據(jù)order by子句排序后，指定當(dāng)前行對應(yīng)值的范圍取值，行數(shù)不固定，只要行值在范圍內(nèi)，對應(yīng)行都包含在內(nèi)

between…and...用來指定范圍的起始點(diǎn)和終結(jié)點(diǎn)，start_expr為起始點(diǎn)，end_expr為終結(jié)點(diǎn)

Start_expr為起始點(diǎn)，起始點(diǎn)有下面幾種選項：

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

end_expr為終結(jié)點(diǎn)，終結(jié)點(diǎn)有下面幾種選項：

unbounded following：以排序之后的最后一行為終點(diǎn)；
current row：以當(dāng)前行為終點(diǎn)；
n preceding：以當(dāng)前行的前面第n行為終點(diǎn)；
n following：以當(dāng)前行的后面第n行為終點(diǎn)；

窗口函數(shù)使用

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

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);

表中的數(shù)據(jù)為：

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函數(shù)根據(jù)字段col1進(jìn)行分組，在分組內(nèi)部根據(jù)字段col2進(jìn)行排序，而此函數(shù)計算的值就表示每組內(nèi)部排序后的順序編號（組內(nèi)的排序是連續(xù)且唯一的），例如：

mysql> #對id進(jìn)行分組，同一個組內(nèi)的數(shù)據(jù)再根據(jù)sale進(jìn)行排序，這個排序序號是唯一并且連續(xù)的
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> #當(dāng)沒有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函數(shù)根據(jù)字段col1進(jìn)行分組，在分組內(nèi)部根據(jù)字段col2進(jìn)行跳躍排序，有相同的排名時，相同排名的數(shù)據(jù)有相同的序號，排序序號不連續(xù)；

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

dense_rank函數(shù)根據(jù)字段col1進(jìn)行分組，在分組內(nèi)部根據(jù)字段col2進(jìn)行連續(xù)排序，有相同的排名時，相同排名的數(shù)據(jù)有相同的序號，但是排序序號連續(xù)，rank函數(shù)和dense_rank函數(shù)的區(qū)別看例子：

mysql> #對id進(jìn)行分組，分組后根據(jù)sale排序
mysql> #可以發(fā)現(xiàn)sale相同時有相同的序號，并且由于id=1的分組中沒有排名第3的序號造成排序不連續(xù)
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> #沒有分組，只根據(jù)sale排序，sale相同時有相同的序號，沒有排名3和4造成排序不連續(xù)
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函數(shù)的用法，再看dense_rank函數(shù)

mysql> #對id進(jìn)行分組，分組后根據(jù)sale排序
mysql> #可以發(fā)現(xiàn)sale相同時有相同的序號，但是整個排序序號是連續(xù)的
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> #沒有分組，只根據(jù)sale排序，sale相同時有相同的序號，整個排序序號是連續(xù)的
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)

到這里小結(jié)一下，row_number函數(shù)，rank函數(shù)和dense_rank函數(shù)都是一種排名函數(shù)，他們有以下區(qū)別：

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

LEAD()與LAG()

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

lead(EXPR,,) over（partition by col1 order?by col2）

lag(EXPR,,) over（partition by col1 order?by col2）

其中：

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

看具體例子，下面是lead函數(shù)和lag函數(shù)的基本用法，參數(shù)只有目標(biāo)字段，則OFFSET偏移量默認(rèn)為1，DEFAULT默認(rèn)為NULL

mysql> #為每一行數(shù)據(jù)的下一行數(shù)據(jù)進(jìn)行開窗，如果該行沒有下一行數(shù)據(jù)，則顯示為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，開窗結(jié)果為200
| 1 | bbb | 200 | 200 | <--下一行的sale值為200，開窗結(jié)果為200
| 1 | ccc | 200 | 300 | <--下一行的sale值為300，開窗結(jié)果為300
| 1 | ddd | 300 | NULL | <--已經(jīng)是最后一行，沒有下一行數(shù)據(jù)，開窗結(jié)果為NULL
| 2 | fff | 200 | 400 |
| 2 | eee | 400 | NULL |
+------+------+------+-------+
6 rows in set?(0.00?sec)


mysql> #為每一行數(shù)據(jù)的上一行數(shù)據(jù)進(jìn)行開窗，如果該行沒有上一行數(shù)據(jù)，則顯示為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 | <--當(dāng)前行為第一行，沒有上一行數(shù)據(jù)，開窗結(jié)果為NULL
| 1 | bbb | 200 | 100 | <--上一行的sale值為100，開窗結(jié)果為100
| 1 | ccc | 200 | 200 | <--上一行的sale值為200，開窗結(jié)果為200
| 1 | ddd | 300 | 200 | <--上一行的sale值為200，開窗結(jié)果為200
| 2 | fff | 200 | NULL |
| 2 | eee | 400 | 200 |
+------+------+------+-------+
6 rows in set?(0.00?sec)

將OFFSET偏移量設(shè)置為2，即可以查到當(dāng)前行的后面第2行的數(shù)據(jù)，如果當(dāng)前行的往下數(shù)2行沒有數(shù)據(jù)，則會顯示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，開窗結(jié)果為200
| 1 | bbb | 200 | 300 | <--下2行的sale值為300，開窗結(jié)果為300
| 1 | ccc | 200 | NULL | <--已經(jīng)是倒數(shù)第2行，沒有下2行的數(shù)據(jù)，開窗結(jié)果為NULL
| 1 | ddd | 300 | NULL | <--已經(jīng)是最后一行，沒有下2行的數(shù)據(jù)，開窗結(jié)果為NULL
| 2 | fff | 200 | NULL |
| 2 | eee | 400 | NULL |
+------+------+------+-------+
6 rows in set?(0.00?sec)

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

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 | <--已經(jīng)是倒數(shù)第2行，沒有下2行的數(shù)據(jù)，開窗結(jié)果為"Empty"
| 1 | ddd | 300 | Empty | <--已經(jīng)是最后一行，沒有下2行的數(shù)據(jù)，開窗結(jié)果為"Empty"
| 2 | fff | 200 | Empty |
| 2 | eee | 400 | Empty |
+------+------+------+-------+
6 rows in set?(0.00?sec)

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

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

mysql> #存在下一行數(shù)據(jù)顯示為Exist，不存在下一行數(shù)據(jù)則顯示NULL，這個NULL是默認(rèn)的
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 | <--下一行的數(shù)據(jù)存在，開窗結(jié)果為"Exist"
| 1 | bbb | 200 | Exist | <--下一行的數(shù)據(jù)存在，開窗結(jié)果為"Exist"
| 1 | ccc | 200 | Exist | <--下一行的數(shù)據(jù)存在，開窗結(jié)果為"Exist"
| 1 | ddd | 300 | NULL | <--已經(jīng)是最后一行，沒有下一行數(shù)據(jù)，開窗結(jié)果為NULL
| 2 | fff | 200 | Exist |
| 2 | eee | 400 | NULL |
+------+------+------+-------+
6 rows in set?(0.00?sec)


mysql> #存在下一行數(shù)據(jù)顯示為Exist，不存在下一行數(shù)據(jù)則顯示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 | <--下一行的數(shù)據(jù)存在，開窗結(jié)果為"Exist"
| 1 | bbb | 200 | Exist | <--下一行的數(shù)據(jù)存在，開窗結(jié)果為"Exist"
| 1 | ccc | 200 | Exist | <--下一行的數(shù)據(jù)存在，開窗結(jié)果為"Exist"
| 1 | ddd | 300 | Empty | <--已經(jīng)是最后一行，沒有下一行數(shù)據(jù)，開窗結(jié)果為"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通常是直接是列名，也可以是從其他行返回的表達(dá)式，根據(jù)字段col1進(jìn)行分組，在分組內(nèi)部根據(jù)字段col2進(jìn)行排序，first_value函數(shù)返回一組排序值后的第一個值，last_value返回一組排序值后的最后一個值

mysql> #first_value函數(shù)查看每一個分組的第一個值
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，開窗結(jié)果100
| 1 | bbb | 200 | 100 | <--分組的第一個值為100，開窗結(jié)果100
| 1 | ccc | 200 | 100 | <--分組的第一個值為100，開窗結(jié)果100
| 1 | ddd | 300 | 100 | <--分組的第一個值為100，開窗結(jié)果100
| 2 | eee | 400 | 400 |
| 2 | fff | 200 | 400 |
+------+------+------+-------+
6 rows in set?(0.00?sec)


mysql> #對id進(jìn)行分組，同一個組內(nèi)的數(shù)據(jù)再根據(jù)sale進(jìn)行排序，查看每一個分組的第一個值
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，開窗結(jié)果100
| 1 | bbb | 200 | 100 | <--分組排序之后的第一個值為100，開窗結(jié)果100 
| 1 | ccc | 200 | 100 | <--分組排序之后的第一個值為100，開窗結(jié)果100
| 1 | ddd | 300 | 100 | <--分組排序之后的第一個值為100，開窗結(jié)果100
| 2 | fff | 200 | 200 |
| 2 | eee | 400 | 200 |
+------+------+------+-------+
6 rows in set?(0.00?sec)


mysql> #last_value函數(shù)查看每一個分組的最后一個值
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，開窗結(jié)果300
| 1 | bbb | 200 | 300 | <--分組排序之后的最后一個值為300，開窗結(jié)果300
| 1 | ccc | 200 | 300 | <--分組排序之后的最后一個值為300，開窗結(jié)果300
| 1 | ddd | 300 | 300 | <--分組排序之后的最后一個值為300，開窗結(jié)果300
| 2 | eee | 400 | 200 |
| 2 | fff | 200 | 200 |
+------+------+------+-------+
6 rows in set?(0.00?sec)

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

mysql> #對id進(jìn)行分組，同一個組內(nèi)的數(shù)據(jù)再根據(jù)sale進(jìn)行排序，查看每一個分組的最后一個值
mysql> #但是你發(fā)現(xiàn)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函數(shù)的默認(rèn)語法，因為在開窗函數(shù)over()中除了分組和排序，還有一個窗口的從句，在經(jīng)過排序之后，使用last_value函數(shù)生效的范圍是第一行至當(dāng)前行，在上面的例子id=1分組中，每一行顯示的所謂最后一個值last value來自第一行到當(dāng)前行這個范圍內(nèi)的最后一個，這里，我們僅對id=1組逐行分析，id=2分組同理可證，希望對你能理解上面代碼為什么會出現(xiàn)這種結(jié)果能夠有所幫助

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

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

NTILE()

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

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

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

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

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

mysql> 給所有數(shù)據(jù)分配四個桶
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()

我們知道聚合函數(shù)的語法是一樣的，可以實(shí)現(xiàn)不一樣的統(tǒng)計功能

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)

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

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)

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

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)

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

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

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

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

與之相對應(yīng)的是rows物理范圍，物理范圍就是嚴(yán)格根據(jù)排序之后的行號所確定的，例如：

rows between unbounded preceding and?current row

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

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是物理范圍，只和排序之后的行號有關(guān)，和當(dāng)前行的數(shù)值無關(guān)，between...and...圈示了一個范圍，unbounded preceding表示排序之后的第一行，unbounded following表示排序之后的最后一行，因此得到上面的結(jié)果，就是可以取得每個分組從第一行開始到最后一行之間這個范圍的最大值

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

窗口從句的使用

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

mysql> #分組之后沒有排序，就沒有默認(rèn)的窗口子句，得到的結(jié)果是每一組的最大值
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> #排序如果沒有窗口子句會有一個默認(rèn)的規(guī)則，即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行的和，開窗結(jié)果為1
| 1 | 2 | 3 | <--計算前2行的和，開窗結(jié)果為3
| 1 | 3 | 9 | <--計算前3行的和，由于是range邏輯范圍，相同的val看作同一行，所以和為1+2+3+3=9
| 1 | 3 | 9 | <--計算前4行的和，該行和第三行同屬于一行，所以和為9，開窗結(jié)果為9
| 1 | 4 | 13 | <--計算前5行的和，開窗結(jié)果為13
| 1 | 5 | 23 | <--計算前6行的和，由于是range邏輯范圍，相同的val看作同一行，所以和為23
| 1 | 5 | 23 | <--計算前7行的和，該行和第6行同屬于一行，所以和為23，開窗結(jié)果為23
| 2 | 6 | 6 |
| 2 | 7 | 13 |
| 2 | 8 | 21 |
| 2 | 9 | 30 |
+------+------+------+
11 rows in set?(0.00?sec)

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

mysql> #得到和上面一樣的結(jié)果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行的和，開窗結(jié)果為1
| 1 | 2 | 3 | <--計算前2行的和，開窗結(jié)果為3
| 1 | 3 | 6 | <--計算前3行的和，開窗結(jié)果為1+2+3=6
| 1 | 3 | 9 | <--計算前4行的和，開窗結(jié)果為1+2+3+3=9
| 1 | 4 | 13 | <--計算前5行的和，開窗結(jié)果為1+2+3+3+4=13
| 1 | 5 | 18 | <--計算前6行的和，開窗結(jié)果為1+2+3+3+4+5=18
| 1 | 5 | 23 | <--計算前7行的和，開窗結(jié)果為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是物理范圍，聚合函數(shù)的生效范圍是嚴(yán)格根據(jù)行號來的，這種用法也更好解釋，但是實(shí)際生活中可能使用邏輯范圍range應(yīng)用更廣泛，舉一個實(shí)際的栗子來說明：班級內(nèi)相同成績的學(xué)生是有相同的名次的，那么老師在計算平均分的時候肯定是用邏輯范圍進(jìn)行相加再求平均值，不可能具有相同的分?jǐn)?shù)的若干個同學(xué)中只取了一個

窗口從句進(jìn)階

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

mysql> #使用rows物理范圍
mysql> #使用1 preceding表示當(dāng)前行的前一行作為起點(diǎn)
mysql> #使用1 following表示當(dāng)前行的后一行作為終點(diǎn)
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、當(dāng)前行1、后一行2，比較而得的最大值，開窗結(jié)果為2
| 1 | 2 | 3 | <--前一行1、當(dāng)前行2、后一行3，比較而得的最大值，開窗結(jié)果為3
| 1 | 3 | 3 | <--前一行2、當(dāng)前行3、后一行3，比較而得的最大值，開窗結(jié)果為3
| 1 | 3 | 4 | <--前一行3、當(dāng)前行3、后一行4，比較而得的最大值，開窗結(jié)果為4
| 1 | 4 | 5 | <--前一行3、當(dāng)前行4、后一行5，比較而得的最大值，開窗結(jié)果為5
| 1 | 5 | 5 | <--前一行4、當(dāng)前行5、后一行5，比較而得的最大值，開窗結(jié)果為5
| 1 | 5 | 5 | <--前一行5、當(dāng)前行5、后一行NULL，比較而得的最大值，開窗結(jié)果為5
| 2 | 6 | 7 |
| 2 | 7 | 8 |
| 2 | 8 | 9 |
| 2 | 9 | 9 |
+------+------+------+
11 rows in set?(0.00?sec)

再來試試使用range邏輯范圍，會產(chǎn)生什么奇妙的結(jié)果，這次我們使用sum函數(shù)

mysql> #使用range邏輯范圍
mysql> #使用1 preceding表示當(dāng)前行的前一行作為起點(diǎn)
mysql> #使用1 following表示當(dāng)前行的后一行作為終點(diǎn)
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、當(dāng)前行1、后一行2，1+2=3
| 1 | 2 | 9 | <--前一行1、當(dāng)前行2、后一行有2個相同的值，邏輯上規(guī)定為同一行的3，1+2+3+3=9
| 1 | 3 | 12 | <--前一行2、當(dāng)前行有2個相同的值，邏輯上規(guī)定為同一行的3、后一行4，2+3+3+4=12
| 1 | 3 | 12 | <--前一行2、當(dāng)前行有2個相同的值，邏輯上規(guī)定為同一行的3、后一行4，2+3+3+4=12
| 1 | 4 | 20 | <--前一行有2個相同的值，邏輯上規(guī)定為同一行的3、當(dāng)前行4、后一行有2個相同的值，邏輯上規(guī)定為同一行的5，3+3+4+5+5=20
| 1 | 5 | 14 | <--前一行4、當(dāng)前行有2個相同的值，邏輯上規(guī)定為同一行的5、后一行NULL，4+5+5=14
| 1 | 5 | 14 | <--前一行4、當(dāng)前行有2個相同的值，邏輯上規(guī)定為同一行的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)

現(xiàn)在你就徹底弄清楚了邏輯范圍range和物理范圍rows的區(qū)別了~

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


最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識第二版》和《SQL高級知識第二版》的PDF電子版。里面有各個語法的解釋、大量的實(shí)例講解和批注等等，非常通俗易懂，方便大家跟著一起來實(shí)操。

有需要的讀者可以下載學(xué)習(xí)，在下面的公眾號「數(shù)據(jù)前線」(非本號)后臺回復(fù)關(guān)鍵字：SQL，就行
數(shù)據(jù)前線
——End——
后臺回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。

推薦閱讀
如何寫出一手好 SQL ？很有必要！
SQL中去重的三種方法，還有誰不會？
SQL 學(xué)習(xí)最強(qiáng)刷題網(wǎng)站！
一千行 MySQL 學(xué)習(xí)筆記
SQL自定義排序

MySQL中的開窗函數(shù)

前言