MySQL窗口函数（MySQL Window Functions）_百度小程序_上海毫米网络优化公司

您的位置：上海毫米网络优化公司 > 小程序 > 百度小程序 >

相关推荐recommended

MySQL窗口函数（MySQL Window Functions）

作者：mmseoamin日期：2023-12-18

MySQL从8.0开始支持窗口函数（Window Functions），因其经常被用在数据分析的场景，窗口函数也被称为分析函数（Analytic Functions）。

目录

一、窗口函数概念

二、基础语法

2.1 窗口的定义

2.2 命名窗口

三、专用窗口函数

3.1 row_number()

3.2 rank()

3.3 dense_rank()

3.4 percent_rank()

3.5 cume_dist()

3.6 first_value()

3.7 last_value()

3.8 nth_value()

3.9 lag()

3.10 lead()

3.11 ntile()

四、窗口函数框架

4.1 框架的定义

4.2 框架定义示例

4.3 框架单位rows和range的区别

4.4 缺少order by子句时的默认框架

一、窗口函数概念

窗口的意思是将数据进行分组，每个分组即是一个窗口，这和使用聚合函数时的group by分组类似，但与聚合函数不同的地方是：

聚合函数（例如：sum/avg/min/max）会针对每个分组（窗口）聚合出一个结果（每一组返回一个结果）。
窗口函数会对每一条数据进行计算，并不会使返回的数据变少（每一行返回一个结果）。
窗口函数会逐行计算，其重点是计算当前行与窗口内其他成员之间的关系，例如：组内排序，累积分布等。

窗口函数可以分为两类：
- 一类既可以做为聚合函数，也可以作为窗口函数，当函数单独使用时是聚合函数，当与over关键字同时使用时作为窗口函数。
- 另一类是专用窗口函数，他们必须与 over 关键字同时使用。
  判断一个函数是不是窗口函数只需要盯着是否有 over 关键字即可。
  
  二、基础语法
  
  窗口函数在使用时需要定义一个窗口（分组），然后对每一行应用窗口函数，正在计算的这行叫做"当前行"（current row）。
  
  2.1 窗口的定义
  
  在使用聚合函数的时候我们通过 group by 关键字来定义如何分组，而窗口函数是通过 over 关键字和 partition by 关键字来定义分组（这里的 partition by 是分组的意思，和分区表没有任何关系）。
  
  下面用一组示例来说明，先创建实验数据：
```
create table wf_example(
id smallint unsigned not null auto_increment primary key,
wind varchar(32),
val smallint);
insert into wf_example values
(null,'Window_A',1),
(null,'Window_A',2),
(null,'Window_A',2),
(null,'Window_A',3),
(null,'Window_A',3),
(null,'Window_A',3),
(null,'Window_B',100),
(null,'Window_B',200),
(null,'Window_B',300),
(null,'Window_B',400),
(null,'Window_B',500);
select * from wf_example;
```
  对于 sum() 函数我们先作为聚合函数，按照wind列进行分组，求val列的和：
```
select wind,sum(val) from wf_example group by wind;
```
  这是最简单的聚合函数示例，sum按wind列对数据分组后求和，每组分别返回一条数据。
  
  下面在将 sum() 作为窗口函数使用：
```
select wind,
sum(val) over () total_sum,
sum(val) over (partition by wind) group_sum
from wf_example;
```
  这里我们在 sum 后增加了一个 over 关键字（代表sum作为窗口函数），over关键字后面括号中即是窗口定义。
  
  从返回结果我们看到 sum() 针对每一行都进行计算并返回了数据：
  - total_sum中我们在over后面使用了一个空括号，没有明确的窗口划分，代表所有数据作为一组。
  - group_sum中，我们通过partition by wind 表示通过wind列进行分组，可以看到求和被限制在Window_A和Window_B组内。
    
    2.2 命名窗口
    
    当一个窗口被多次引用的时候，在每个over后面都写一遍定义就显得有些繁琐了，此场景可以通过命名窗口优化：一次定义，多次引用。
    
    命名窗口的定义是通过 window wind_name as () 来进行定义的，括号内的部分就是原over子句后的窗口定义，在用over关键字调用窗口时，直接引用窗口名wind_name即可：
```
select wind,
sum(val) over w group_sum   -- 通过名称 w 引用窗口
from wf_example
window w as (partition by wind);  -- 命名窗口定义
```
    通常情况下使用时只需要直接引用窗口名称即可，有时需要对窗口进一步加工，例如排序等，可以用括号将窗口名扩起来，后面跟上order by 子句：
```
select wind,
first_value(val) over (w order by val desc) first_val_desc, -- 通过窗口名引用，并降序排列
first_value(val) over (w order by val asc) first_val_asc  -- 通过窗口名引用，并升序排列
from wf_example
window w as (partition by wind);  -- 命名窗口定义
```
    三、专用窗口函数
    
    除了上面示例的sum，其他的聚合函数例如count, max, min, avg等都可以作为窗口函数使用，但是专用窗口函数在数据分析中更加常用。专用窗口函数在使用时必须搭配 over 关键字。
    
    3.1 row_number()
    
    语法：row_number() over window
    
    没有参数，返回当前行在组内的位置编号，从1开始，order by子句会影响行的编号顺序，如果没有order by，那么行的编号是不确定的。另外，即使行完全相同，它们的编号也是不同的，这点和后面的 rank() 和 dense_rank() 不同。
```
select wind, val,
row_number() over w 组内编号
from wf_example
window w as (partition by wind);
```
    上面示例中：
    - 每一行都有唯一的编号，从1开始，即使数据完全相同，编号也不同。
      
      3.2 rank()
      
      语法：rank() over window
      
      没有参数，返回当前行在组内的排序，排序带间隙（排名数字不连续），在partition by 后面可以跟上order by 子句来指定按某列排序，示例中按照val值升序排列：
```
select wind,val,
rank() over (partition by wind order by val) 带间隙排序
from wf_example;
```
      上面示例中：
      - order by val 定义了按照 val 的值排序，注意相同的值，排序也相同，如果没有 order by val子句，那么所有行的排序都是1。
      - Window_A中，由于存在两个2，因此下一个排序是4，排序存在间隙。
        
        3.3 dense_rank()
        
        语法：dense_rank() over window
        
        没有参数，返回当前行在组内的排序，排序不带间隙（排名是连续的）：
        
        select wind, val, rank() over (partition by wind order by val) 带间隙排序, dense_rank() over (partition by wind order by val) 不带间隙排序 from wf_example;
        
        上面示例中：
        
        Window_A的组内排序中，虽然存在2个2，下一个排序依然是3，排序不存在间隙。
        
        3.4 percent_rank()
        
        语法：percent_rank() over window
        
        百分比排序，返回当前行在组内的百分比位置，返回值范围为[0, 1]，可以用当前行排序/(行数-1) 计算得出，但与rank不同，这里排序是从0开始而不是从1（相当于rank-1），因此第1行的百分比位置是0%，相当于：（rank-1）/(rows-1)。
        
        select wind,val, rank() over (partition by wind order by val) 带间隙排序, percent_rank() over (partition by wind order by val) 百分比排序 from wf_example;
        
        上面示例中：
        
        window_A中第1行rank为1，组内rows为6，代入(rank-1)/(rows-1)得到percent_rank为 (1-1)/(6-1)，结果为0.
        Window_A中第3行rank为2（和第2行并列第2），组内rows为6，代入(rank-1)/(rows-1)得到percent_rank为 (2-1)/(6-1)，结果为0.2。
        window_B中最后一行rank为5，rows为5，代入(rank-1)/(rows-1)得到percent_rank为 (5-1)/(5-1)，结果为1.
        
        3.5 cume_dist()
        
        语法：cume_dist() over window
        
        累积分布（cumulative distribution）。返回 "当前行之前" 与 "和当前行相等（包含当前行）" 的行数，占组内行数的百分比。
        
        （当前行及之前行数 + 和当前行相等的行数）/组内数量，这个和rank类似，但是统计的是一个累积的比例，数据分布从0到1.
        
        select wind,val, rank() over w 行编号, percent_rank() over w 百分比排序, cume_dist() over w 累积比例 from wf_example window w as (partition by wind order by val);
        
        上面示例中：
        
        Window_A中，第1行为独立一行，前面没有行，也没有和自己相同的行，因此累积数量为1，累积比例为：1/6，即0.166666666
        Window_A中，第2行，累积数量为：前面的1行，自己第2行，和自己相等的第3行，因此累积数量为3，累积比例为：3/6，即0.5
        Window_A中，第4行，累积数量为：前面的3行，自己第4行，和自己相等的第5，6行，因此累积数量为6，累积比例为：6/6，即1。
        Window_B中，每一行都是独立的，不存在和自己相同的行，行编号就包含了自己及之前所有的行，因此每行的累积比例都是：行编号/组内行数
        
        3.6 first_value()
        
        语法：first_value(expr) over window
        
        返回当前框架内第一行计算出的expr值，第一个值会随着排序定义而不同。
        
        这里引入了一个重要概念：框架（frame），框架是一个动态的概念，是组的子集，first_value中每一行的默认框架由3部分组成：
        
        当前行之前的所有行
        当前行
        和当前行相同的行
        这3部分相当于框架定义：range between unbound preceding and current row，框架定义语法在后面解释。
        
        select wind,val, first_value(val) over (w order by val desc) 指定列倒序框架内第一个值, first_value(val) over (w order by val asc) 指定列正序框架内第一个值 from wf_example window w as (partition by wind);
        
        上面示例中：
        
        window_B中当 order by val desc 时，框架内第一个值是500，这里直接取列的值，你也可以替换为表达式。
        window_B中当 order by val asc 时，框架内第一个值是100。
        
        3.7 last_value()
        
        语法：last_value(expr) over window
        
        last_value返回当前框架的最后一行计算出的expr值，从last_value函数可以更好看出框架的动态变化：
        
        select wind,val, last_value(val*10) over w 框架内最后一个值的十倍 from wf_example window w as (partition by wind order by val);
        
        上面的示例中：
        
        Window_B中，last_value(val*10)返回的值每一行都不同，截止当前行的框架内最后一个值（就是当前行自己），而不是整个组的最后一个值。
        
        3.8 nth_value()
        
        语法：nth_value(expr, N) over window
        
        返回框架内的第N行计算出的表达式expr值，当没有时返回null：
        
        select wind,val, nth_value(val, 2) over w 框架内第二个值, nth_value(val, 3) over w 框架内第三个值, nth_value(val, 4) over w 框架内第四个值 from wf_example window w as (partition by wind order by val);
        
        上面的示例中：
        
        nth_value(val, 2) 返回框架内第二个值，第1行计算时由于框架只有一行，没有第二个值，所以返回null。
        nth_value(val, 3) 返回框架内第三个值，注意window_A由于2，3行val是相等的，计算第二行时的框架会包含第三行，而Window_B是独立的，第二行返回null。
        nth_value(val, 4) 返回框架内第四个值，window_A和Window_B都在第四行才取到值，前三行都是null。
        
        3.9 lag()
        
        语法：lag(expr [, N [, default]])
        
        返回在当前窗口内，当前行的“前面N行”计算出的expr结果，如果没有满足条件的行，则返回default，其中参数N和default可以省略，如果省略了则默认N为1，default为null。
        
        select wind,val, lag(val*10) over w 当前行前面一个值, lag(val*10,1,'不存在') over w 当前行前面一个值带默认值, val-lag(val,2) over w 当前值与前两个值的差 from wf_example window w as (partition by wind order by val);
        
        上面的示例中：
        
        lag(val*10) 返回前一行表达式val*10计算值（N参数省略，默认为1，default参数省略，默认为nulll，第一行没有前一行，返回null）
        lag(val*10,1,'不存在') 返回前一行表达式val*10计算值（default为"不存在"，因此第一行返回字符串"不存在"）
        val-lag(val,2) 计算当前行与前2行之间的差额
        
        3.10 lead()
        
        语法：lead(expr [N [, default]])
        
        lead() 和lag() 类似，返回在当前窗口内，当前行的“后面N行”计算出的expr结果，如果没有满足条件的行，则返回default，其中参数N和default可以省略，如果省略了则默认N为1，default为null。
        
        select wind,val, lead(val*10) over w 当前行后面一个值的十倍, lead(val*10,1,'不存在') over w 当前行后面一个值十倍带默认值, val-lead(val,2) over w 当前行与后面第二个值的差 from wf_example window w as (partition by wind order by val);
        
        上面的示例中：
        
        lead(val*10) 返回后一行表达式val*10计算值（N参数省略，默认为1，default参数省略，默认为null，最后一行由于没有后一行，返回null）
        lag(val*10,1,'不存在') 返回后一行表达式val*10计算值（default为"不存在"，因此最后一行返回字符串"不存在"）
        val-lead(val,2) 计算当前行与后面第2行之间的差额
        
        3.11 ntile()
        
        语法：ntile(N) over window
        
        将组内成员再次分为N个小组（子分组/buckets），返回子分组的编号。
        
        select wind,val, ntile(2) over w 将每个组再次分为2个组, ntile(4) over w 将每个组再次分为4个组, ntile(100) over w 将每个组再次分为100个组 from wf_example window w as (partition by wind order by val);
        
        上面示例中：
        
        ntile(2) 将每个组再次分为2个组，并返回每一行所属子分组的编号
        ntile(100) 当组的数量超过行数时，每一行都是一个独立子分组。
        
        四、窗口函数框架
        
        框架（frame）是窗口的一个子集，它会根据当前行动态变化。在前面在first_vlalue和 last_value 函数中，我们提到了框架的定义，它们默认的框架是从组内第一行到当前行（也包含和当前行相等的行）。
        
        窗口函数还支持框架子句，让你可以显式定义框架的范围，根据当前行的位置，来计算组内一小部分集合的数据。
        
        4.1 框架的定义
        
        框架的定义语法：frame_units frame_extent
        
        框架的定义由frame_units（框架单位）和 frame_extent（框架范围）两子句组成。
        
        当定义框架时，我们首先要指定框架使用的单位（frame_units子句），框架单位可以有2种选择：
        
        rows：通过起始行和结束行来划定框架的范围，边界是明确的一行。
        
        range：通过具有相同值的行来划定框架的范围，边界是一个范围，具有相同值的行作为一个整体看待。
        
        定义好单位后，我们还需要定义框架范围（frame_extent子句），也有两种定义方式：
        
        只定义起始点（frame_start），终止点（frame_end）默认就是当前行。
        通过between frame_start and frame_end 子句，同时定义起始点（frame_start）和终止点（frame_end）。
        
        合法的frame_start和frame_end可以有如下5种选择：
        
        current row：当框架单位是rows时，即当前行。当框架单位是range时，包含当前行和当前行相同的行（一个范围）。
        unbound preceding：窗口内第1行。
        unbound following：窗口内最后1行。
        expr preceding：当框架单位是rows时，边界时当前行的前expr行。当框架单位是range时，边界是值和"当前行的值-expr"相等的行，如果当前行的值是null，那边界就是和当前行相等的行。
        expr following：当框架单位是rows时，边界时当前行的后expr行。当框架单位是range时，边界时和"当前行的值+expr"相等的行，如果当前行的值是null，那边界就是和当前行相等的行。
        
        光看定义可能有些晦涩，我们通过几个示例来帮助理解。
        
        4.2 框架定义示例
        
        我们用几个示例来加深对框架定义的理解：
        
        select wind,val, last_value(val*10) over (partition by wind order by val rows unbounded preceding) 显式定义框架内最后一个值的十倍 from wf_example;
        
        上面示例中：
        
        框架的定义是rows unbouned preceding。
        框架单位是rows（行），框架范围是unbounded preceding（组内第1行）。
        这里采用了仅定义起始点的方式，框架的终止点默认就是当前行（current row），定义等同于：rows between unbound preceding and current row
        
        下面的示例为滚动求和，计算当前行和前一行的和：
        
        select wind,val, sum(val) over (partition by wind order by val rows 1 preceding) 当前行和前1行的和, sum(val) over (partition by wind order by val rows between 1 preceding and current row) 第二种定义方式 from wf_example;
        
        上面示例中：
        
        第一个框架的定义是rows 1 preceding，框架单位是rows（行），
        第一个框架范围是1 preceding（当框架单位为rows时，1 preceding 代表当前行的前1行）.
        第一个框架采用了仅定义起始点的方式，框架的终止点默认就是当前行。
        第二个框架采用了between 1 preceding and current row的方式，显式指定了框架的起始和结束范围，效果是相同的。
        
        4.3 框架单位rows和range的区别
        
        这里仅仅将上一个滚动求和SQL中的框架单位定义由rows改为range，再看一下效果：
        
        select wind,val, sum(val) over (partition by wind order by val range 1 preceding) range单位下当前行和当前行值减1范围的和 from wf_example;
        
        上面示例中，当框架单位变为range时：
        
        框架定义为 range 1 preceding，等价于 range between 1 preceding and current row。
        当框架单位为range时，这里的1 preceding不再是前1行的意思，而是"当前行的值-1"。
        而range between 1 preceding and current row 代表值的范围落在区间 [当前行值-1，当前行值] 内所有行。
        在Window_A中，第二行val值为2，因此框架包含值在 [2-1, 2] 范围内的所有行，即1，2，3行，sum求和结果为5，第三行同理。
        在Window_A中，第四行val值为3，因此框架包含值在 [3-1, 3] 范围内的所有行，即2，3，4，5，6行，sum求和结果为13，第五、六行同理。
        在Window_B中，第2行val值为200，因此框架包含值在[200-1, 200]范围内的所有所有行，只有第二行，sum求和结果就是自己，后面的行同理。
        
        上面的SQL通过加入first_value和last_value函数我们可以更直观的看出框架的边界（first_value返回框架内第1个值，last_value返回框架内最后一个值）：
        
        select wind,val, sum(val) over (partition by wind order by val range 1 preceding) range单位下当前行和当前行值减1范围的和, first_value(val) over (partition by wind order by val range 1 preceding) first_val, last_value(val) over (partition by wind order by val range 1 preceding) last_val from wf_example;
        
        在Window_A中，val的值差距为1，因此 range 1 preceding可以触及前面的行。
        在Window_B中，val的值差距为100，因此range 1 preceding无法触及前面的行（first_value和last_value都是自己），每一行的框架都只包含当前行自己。
        
        但如果我们把range 1 preceding改成 range 100 preceding，则Window_B中框架可以触及前面的行：
        
        select wind,val, sum(val) over (partition by wind order by val range 100 preceding) range单位下当前行和当前行值减1范围的和, first_value(val) over (partition by wind order by val range 100 preceding) first_val, last_value(val) over (partition by wind order by val range 100 preceding) last_val from wf_example;
        
        可以看到Window_B中求和列变成了当前行和前1行的val的和，同时框架的first_val变成了前1行的值（代表当前行的框架包含前1行）。
        
        框架单位rows和range的区别总结就是：
        
        rows是通过行来划分边界，框架边界是明确的某一行。
        range是通过值来划定边界，框架边界是具有某个值的所有行。
        
        4.4 缺少order by子句时的默认框架
        
        当没有显式的框架定义时，某些函数会采用默认框架定义，而默认框架定义会受到是否有order by子句影响，因此是否有order by子句可能会导致某些函数的计算结果不同。
        
        根据窗口定义是否有order by子句：
        
        有 order by 子句时，默认的框架定义是：range between unbound preceding and current row
        没有 order by 子句时，默认的框架定义是：range between unbound preceding and unbound following
        即：当有order by 子句时，框架是从组内第一行到当前行（注意框架单位是range，也包含当前行相同值的行）。当没有order by 子句时，框架就是从组内第1行到最后一行（组内所有行），所有的行都是相等的。
        
        我们通过最初的sum函数来观察这种默认框架的区别：
        
        select wind,val, sum(val) over (partition by wind order by val) 带orderby子句, sum(val) over (partition by wind) 不带orderby子句 from wf_example;
        
        上面示例中：
        
        带order by子句时，sum函数求和范围是第1行到当前行（包含和当前行相等的行）的和，sum的结果是递增的。
        不带order by 子句时，每一行sum，求出来都是组内全部行的和，没有order by子句，众生平等。
        
        辽宁建设工程信息网公司网站设计需要多少钱东莞做商城网站建设武汉网站开发公司成功营销十大经典案例网站制作建

上一篇：nginx:[error]OpenEvent(“Globalngx

下一篇：SpringBoot+Vue实现简单的登录注册功能