日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

公告：魔扣目錄網(wǎng)為廣大站長提供免費收錄網(wǎng)站服務(wù)，提交前請做好本站友鏈：【 網(wǎng)站目錄：http://www.ylptlb.cn 】， 免友鏈快審服務(wù)（50元/站），

點擊這里在線咨詢客服
新站提交
  • 網(wǎng)站：51998
  • 待審：31
  • 小程序：12
  • 文章：1030137
  • 會員：747

SQL中的開窗函數(shù)

發(fā)布時間：2023-09-30 21:06:13 作者：網(wǎng)友整理

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

 

前言

MySQL從8.0版本開始支持窗口函數(shù)了，窗口函數(shù)又名開窗函數(shù)，屬于分析函數(shù)的一種。用于解決復(fù)雜報表統(tǒ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ù)有：
  1. 排名函數(shù) ROW_NUMBER()；
  2. 排名函數(shù) RANK() 和 DENSE_RANK()；
  3. 錯行函數(shù) lead()、lag()；
  4. 取值函數(shù) First_value()和last_value()；
  5. 分箱函數(shù) NTILE()；
  6. 統(tǒng)計函數(shù)，也就是我們常用的聚合函數(shù) MAX()、MIN()、AVG()、SUM()、COUNT()
 
開窗函數(shù)over()
我們在Function函數(shù)之后需要跟上一個開窗函數(shù)over()，over()函數(shù)參數(shù)包括了三個子句（分組子句，排序子句和窗口子句），根據(jù)實際需求選擇子句：
 
  1. partition by query_patition_clause：即分組，通過query_patition_clause進行分組，一般是表中的某一個字段，所以可以把partition by 看作與GROUP BY 具有相同功能的語法。
  2. order by order_by_clause：即排序，通過order_by_clause 進行排序，一般是在分組（partition by）之后再進行排序，如此一來，就是在組內(nèi)進行排序。如果沒有前面的分組子句（partition by），那么就是全部數(shù)據(jù)進行排序。和普通MySQL中的查詢語句一樣，排序從句也支持ASC和DESC的用法。
  3. Window_clause：窗口從句，它是排序之后的功能擴展，它標識了在排序之后的一個范圍，它的格式是：
    rows | range between start_expr and end_expr

 

其中rows和range為二選其一：
  1. rows是物理范圍，即根據(jù)order by子句排序后，取的前N行及后N行的數(shù)據(jù)計算（與當(dāng)前行的值無關(guān)，只與排序后的行號相關(guān)）；
  2. range是邏輯范圍，根據(jù)order by子句排序后，指定當(dāng)前行對應(yīng)值的范圍取值，行數(shù)不固定，只要行值在范圍內(nèi)，對應(yīng)行都包含在內(nèi)
 
between…and...用來指定范圍的起始點和終結(jié)點，start_expr為起始點，end_expr為終結(jié)點
 
Start_expr為起始點，起始點有下面幾種選項：
 
  1. unbounded preceding：指明窗口開始于分組的第一行，以排序之后的第一行為起點；
  2. current row：以當(dāng)前行為起點；
  3. n preceding：以當(dāng)前行的前面第n行為起點；
  4. n following：以當(dāng)前行的后面第n行為起點；
 
end_expr為終結(jié)點，終結(jié)點有下面幾種選項：
 
  1. unbounded following：以排序之后的最后一行為終點；
  2. current row：以當(dāng)前行為終點；
  3. n preceding：以當(dāng)前行的前面第n行為終點；
  4. n following：以當(dāng)前行的后面第n行為終點；
 
窗口函數(shù)使用
使用一個具體的實例來說明窗口函數(shù)使用方法，首先創(chuàng)建一個測試表，有字段id，name和sale，借用實際生活中的例子，假設(shè)一個公司有銷售部門（id）為1和2，每個部門內(nèi)有若干個成員（name），每個成員有自己的銷售業(yè)績（sale），然后就可以使用一些函數(shù)來做統(tǒng)計，首先創(chuàng)建測試表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);

 

 

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

 

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

 

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

 

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

 

mysql> #對id進行分組，分組后根據(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ù)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ū)別：
 
  1. row_number是沒有重復(fù)的一種排序，即使對于兩行相同的數(shù)據(jù)，也會根據(jù)查詢到的順序進行排名；而rank函數(shù)和dense_rank函數(shù)對相同的數(shù)據(jù)會有一個相同的次序；
  2. 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,<OFFSET>,<DEFAULT>) over（partition by col1 order by col2）

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

 

其中：

  • EXPR通常是直接是列名，也可以是從其他行返回的表達式；
  • OFFSET是默認為1，表示在當(dāng)前分區(qū)內(nèi)基于當(dāng)前行的偏移行數(shù)；
  • DEFAULT是在OFFSET指定的偏移行數(shù)超出了分組的范圍時(因為默認會返回null)，可以通過設(shè)置這個字段來返回一個默認值來替代null。
看具體例子，下面是lead函數(shù)和lag函數(shù)的基本用法，參數(shù)只有目標字段，則OFFSET偏移量默認為1，DEFAULT默認為NULL

 

mysql> #為每一行數(shù)據(jù)的下一行數(shù)據(jù)進行開窗，如果該行沒有下一行數(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ù)進行開窗，如果該行沒有上一行數(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"，即把默認顯示的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，一般為某一個字段或者其它表達式；第二個參數(shù)是偏移量，第三個參數(shù)是顯示的默認值，例如，我們只傳入一個參數(shù)

 

mysql> #存在下一行數(shù)據(jù)顯示為Exist，不存在下一行數(shù)據(jù)則顯示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 | <--下一行的數(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通常是直接是列名，也可以是從其他行返回的表達式，根據(jù)字段col1進行分組，在分組內(nèi)部根據(jù)字段col2進行排序，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進行分組，同一個組內(nèi)的數(shù)據(jù)再根據(jù)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，開窗結(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)

 

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

 

mysql> #對id進行分組，同一個組內(nèi)的數(shù)據(jù)再根據(jù)sale進行排序，查看每一個分組的最后一個值
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ù)的默認語法，因為在開窗函數(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. 查詢到第1行sale=100，只有當(dāng)前一行，最后一個值只有100，開窗結(jié)果為100；
  2. 查詢到第2行sale=100，200兩個數(shù)據(jù)，最后一個值是200，開窗結(jié)果為200；
  3. 查詢到第3行sale=100，200，200三個數(shù)據(jù)，最后一個值是200，開窗結(jié)果為200；
  4. 查詢到四行sale=100，200，200，300四個數(shù)據(jù)，最后一個值是300，開窗結(jié)果為300，至此id=1的分組查詢完畢
這里還是需要注意：窗口從句有一個默認的規(guī)則，就和上面分析的一樣，是從排序之后第一行到當(dāng)前行的范圍，這個規(guī)則是可以自己定義的，而且非常靈活，我會在最后會詳細介紹窗口從句的用法
 
NTILE()
NTILE函數(shù)對一個數(shù)據(jù)分區(qū)中的有序結(jié)果集進行劃分，舉一個生活中的例子，我們想要把一些雞蛋放入若干個籃子中，每個籃子可以看成一個組，然后為每個籃子分配一個唯一的組編號，這個組里面就有一些雞蛋。我們假設(shè)籃子的編號可以反映放在內(nèi)部雞蛋的體積大小，例如編號較大的籃子里面放著一些體積較大的雞蛋，編號較小的籃子則放著體積較小的雞蛋，現(xiàn)在，因為體積特別大的雞蛋和特別小的雞蛋不適合放入規(guī)定范圍包裝盒內(nèi)進行出售，所以要進行篩選，在進行分組之后，我們只需要拎出合適范圍的帶有編號的籃子就能拿到我們想要的雞蛋
 
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ù)的語法是一樣的，可以實現(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ù)是默認排序之后第一行數(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)

 

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

 

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表示當(dāng)前行。
 
其中range是邏輯層面的范圍，邏輯范圍意思是排序之后把具有相同的值看成同一行，例如上面第3、4行有兩個相同的值val=3，那么會把第三行和第三行看成同一行，所以range與排序之后的行號是沒有關(guān)系的，取定的范圍和字段值有關(guān)；
 
與之相對應(yīng)的是rows物理范圍，物理范圍就是嚴格根據(jù)排序之后的行號所確定的，例如：

 

rows between unbounded preceding and current row

 

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

 

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é)進行說明，示例一，只使用分組，沒有排序：

 

mysql> #分組之后沒有排序，就沒有默認的窗口子句，得到的結(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> #排序如果沒有窗口子句會有一個默認的規(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ù)的生效范圍是嚴格根據(jù)行號來的，這種用法也更好解釋，但是實際生活中可能使用邏輯范圍range應(yīng)用更廣泛，舉一個實際的栗子來說明：班級內(nèi)相同成績的學(xué)生是有相同的名次的，那么老師在計算平均分的時候肯定是用邏輯范圍進行相加再求平均值，不可能具有相同的分數(shù)的若干個同學(xué)中只取了一個
 
窗口從句進階
希望通過上面三個例子能幫助你初步了解什么是窗口從句及其使用語法，到這里你可能會想，為什么范圍總是要從第一行開始呢？可不可以自己自定義一個范圍呢，答案是可以的，而且可以是任意范圍，例如：

 

mysql> #使用rows物理范圍
mysql> #使用1 preceding表示當(dāng)前行的前一行作為起點
mysql> #使用1 following表示當(dāng)前行的后一行作為終點
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)前行的前一行作為起點
mysql> #使用1 following表示當(dāng)前行的后一行作為終點
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ū)別了~
 
歡迎大家討論補充，如有不對或者哪里有描述不準確或歧義的地方，敬請指正，感謝~~
 

作者：五四青年

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

分享到：
標簽：SQL
用戶無頭像

網(wǎng)友整理

注冊時間：

網(wǎng)站：5 個   小程序：0 個  文章：12 篇

  • 51998

    網(wǎng)站

  • 12

    小程序

  • 1030137

    文章

  • 747

    會員

趕快注冊賬號，推廣您的網(wǎng)站吧！
最新入駐小程序

數(shù)獨大挑戰(zhàn)2018-06-03

數(shù)獨一種數(shù)學(xué)游戲，玩家需要根據(jù)9

答題星2018-06-03

您可以通過答題星輕松地創(chuàng)建試卷

全階人生考試2018-06-03

各種考試題，題庫，初中，高中，大學(xué)四六

運動步數(shù)有氧達人2018-06-03

記錄運動步數(shù)，積累氧氣值。還可偷

每日養(yǎng)生app2018-06-03

每日養(yǎng)生,天天健康

體育訓(xùn)練成績評定2018-06-03

通用課目體育訓(xùn)練成績評定