<返回更多

SQL中的开窗函数

2023-09-08  微信公众号  数据STUDIO
加入收藏
目录

 

前言

MySQL从8.0版本开始支持窗口函数了，窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组（GROUP BY）的某种聚合值，它和聚合函数的不同之处是：窗口函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

 

窗口函数经常会在leetCode的题目中使用到

 

窗口函数的格式

 

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

 

函数（Function）的类型
不是所有的函数（Function）都支持开窗函数。目前支持的窗口函数可结合的函数有：
  1. 排名函数 ROW_NUMBER()；
  2. 排名函数 RANK() 和 DENSE_RANK()；
  3. 错行函数 lead()、lag()；
  4. 取值函数 First_value()和last_value()；
  5. 分箱函数 NTILE()；
  6. 统计函数，也就是我们常用的聚合函数 MAX()、MIN()、AVG()、SUM()、COUNT()
 
开窗函数over()
我们在Function函数之后需要跟上一个开窗函数over()，over()函数参数包括了三个子句（分组子句，排序子句和窗口子句），根据实际需求选择子句：
 
  1. partition by query_patition_clause：即分组，通过query_patition_clause进行分组，一般是表中的某一个字段，所以可以把partition by 看作与GROUP BY 具有相同功能的语法。
  2. order by order_by_clause：即排序，通过order_by_clause 进行排序，一般是在分组（partition by）之后再进行排序，如此一来，就是在组内进行排序。如果没有前面的分组子句（partition by），那么就是全部数据进行排序。和普通MySQL中的查询语句一样，排序从句也支持ASC和DESC的用法。
  3. Window_clause：窗口从句，它是排序之后的功能扩展，它标识了在排序之后的一个范围，它的格式是：
    rows | range between start_expr and end_expr

 

其中rows和range为二选其一：
  1. rows是物理范围，即根据order by子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）；
  2. range是逻辑范围，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内
 
between…and...用来指定范围的起始点和终结点，start_expr为起始点，end_expr为终结点
 
Start_expr为起始点，起始点有下面几种选项：
 
  1. unbounded preceding：指明窗口开始于分组的第一行，以排序之后的第一行为起点；
  2. current row：以当前行为起点；
  3. n preceding：以当前行的前面第n行为起点；
  4. n following：以当前行的后面第n行为起点；
 
end_expr为终结点，终结点有下面几种选项：
 
  1. unbounded following：以排序之后的最后一行为终点；
  2. current row：以当前行为终点；
  3. n preceding：以当前行的前面第n行为终点；
  4. 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函数都是一种排名函数，他们有以下区别：
 
  1. row_number是没有重复的一种排序，即使对于两行相同的数据，也会根据查询到的顺序进行排名；而rank函数和dense_rank函数对相同的数据会有一个相同的次序；
  2. 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）

 

其中：

看具体例子，下面是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. 查询到第1行sale=100，只有当前一行，最后一个值只有100，开窗结果为100；
  2. 查询到第2行sale=100，200两个数据，最后一个值是200，开窗结果为200；
  3. 查询到第3行sale=100，200，200三个数据，最后一个值是200，开窗结果为200；
  4. 查询到四行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      点击(2)
声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多SQL相关>>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号