相关推荐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;

      MySQL窗口函数(MySQL Window Functions),第1张

      对于 sum() 函数我们先作为聚合函数,按照wind列进行分组,求val列的和:

      select wind,sum(val) from wf_example group by wind;

      MySQL窗口函数(MySQL Window Functions),第2张

      这是最简单的聚合函数示例,sum按wind列对数据分组后求和,每组分别返回一条数据。

      下面在将 sum() 作为窗口函数使用:

      select wind,
      sum(val) over () total_sum,
      sum(val) over (partition by wind) group_sum
      from wf_example;

      MySQL窗口函数(MySQL Window Functions),第3张

      这里我们在 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);  -- 命名窗口定义

        MySQL窗口函数(MySQL Window Functions),第4张

        通常情况下使用时只需要直接引用窗口名称即可,有时需要对窗口进一步加工,例如排序等,可以用括号将窗口名扩起来,后面跟上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);  -- 命名窗口定义

        MySQL窗口函数(MySQL Window Functions),第5张

        三、专用窗口函数

        除了上面示例的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);

        MySQL窗口函数(MySQL Window Functions),第6张

        上面示例中:

        • 每一行都有唯一的编号,从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;

          MySQL窗口函数(MySQL Window Functions),第7张

          上面示例中:

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

            MySQL窗口函数(MySQL Window Functions),第8张

            上面示例中:

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

              MySQL窗口函数(MySQL Window Functions),第9张

              上面示例中:

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

                MySQL窗口函数(MySQL Window Functions),第10张

                上面示例中:

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

                    MySQL窗口函数(MySQL Window Functions),第11张

                    上面示例中:

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

                      MySQL窗口函数(MySQL Window Functions),第12张

                      上面的示例中:

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

                        MySQL窗口函数(MySQL Window Functions),第13张

                        上面的示例中:

                        • 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,其中参数Ndefault可以省略,如果省略了则默认N为1,defaultnull

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

                          MySQL窗口函数(MySQL Window Functions),第14张

                          上面的示例中:

                          • 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,其中参数Ndefault可以省略,如果省略了则默认N为1,defaultnull

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

                            MySQL窗口函数(MySQL Window Functions),第15张

                            上面的示例中:

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

                              MySQL窗口函数(MySQL Window Functions),第16张

                              上面示例中:

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

                                    MySQL窗口函数(MySQL Window Functions),第17张

                                    上面示例中:

                                    • 框架的定义是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;

                                      MySQL窗口函数(MySQL Window Functions),第18张

                                      上面示例中:

                                      • 第一个框架的定义是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;

                                        MySQL窗口函数(MySQL Window Functions),第19张

                                        上面示例中,当框架单位变为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;
                                          • MySQL窗口函数(MySQL Window Functions),第20张
                                          • 在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;

                                            MySQL窗口函数(MySQL Window Functions),第21张

                                            可以看到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;

                                                MySQL窗口函数(MySQL Window Functions),第22张

                                                上面示例中: