mysql内核教程(MySQL8.0窗口函数框架用法-爱可生)

mysql内核教程(MySQL8.0窗口函数框架用法-爱可生)(1)

之前在 MySQL 8.0 新特性栏目里介绍过 8.0 的窗口函数的用法,没有细化到全部的语法,恰巧今天有客户追问其中的框架子句用法,写出来大家一起探讨。

窗口函数其实就是一个分组窗口内部处理每条记录的函数,这个窗口也就是之前聚合操作的窗口。不同的是,聚合函数是把窗口关闭,给一个汇总的结果;而窗口函数是把窗口打开,给分组内每行记录求取对应的聚合函数值或者其他表达式的结果。

今天重点看窗口函数内的 frame 子句:frame 子句用来把窗口内的记录按照指定的条件打印出来,跟在 partition 和 order by 子句后面。frame 子句的语法为:

frame_clause: frame_units frame_extent frame_units: {ROWS | RANGE} frame_extent: {frame_start | frame_between} frame_between: BETWEEN frame_start AND frame_end frame_start, frame_end: { CURRENT ROW | UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING | expr PRECEDING | expr FOLLOWING }

这里分为两块,一块是 frame_units (框架单元),另外一块是 frame_extent (框架内容)

  • frame_units 框架单元有两个,一个是 rows,一个是 range。
  • rows 后面跟的内容为指定的行号,而 range 不同,range 是指的行内容。
  • 框架内容看起来挺多分类,其实就一句话来表达:为了定义分组内对应行记录的边界值来求取对应的计算结果。

    基于 t1 举例说明下:

    mysql: ytt_80 > desc t1; ------- ------ ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- ------ ------ ----- --------- ------- | id | int | YES | | NULL | | | r1 | int | YES | | NULL | | | r2 | int | YES | | NULL | | ------- ------ ------ ----- --------- ------- 3 rows in set (0.00 sec) mysql: ytt_80 > select * from t1; ------ ------ ------ | id | r1 | r2 | ------ ------ ------ | 2 | 1 | 1 | | 2 | 2 | 20 | | 2 | 3 | 30 | | 2 | 4 | 40 | | 3 | 3 | 3 | | 3 | 2 | 2 | | 3 | 10 | 20 | | 3 | 30 | 20 | | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 4 | | 1 | 10 | 10 | | 1 | 15 | 20 | | 2 | 15 | 2 | | 3 | 15 | 5 | | 1 | 9 | 100 | ------ ------ ------ 16 rows in set (0.00 sec)

    1、CURRENT ROW

    表示获取当前行记录,也就是边界是当前行,等值关系

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc range current row ) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 2 | | 1 | 3 | 3 | | 1 | 9 | 9 | | 1 | 10 | 10 | | 1 | 15 | 15 | ------ ------ ----------- 6 rows in set (0.00 sec)

    这里我们求 ID 为 1 的分组记录,基于聚合函数 SUM 来对分组内的行记录按照一定的条件求和。其中 OVER 子句用来定义分区以及相关条件,这里表示只获取分组内排序字段的当前行记录,也就是字段 r1 对应的记录,这是最简单的场景。

    2、UNBOUNDED PRECEDING

    表示边界永远为第一行

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows unbounded preceding ) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 6 | | 1 | 9 | 15 | | 1 | 10 | 25 | | 1 | 15 | 40 | ------ ------ ----------- 6 rows in set (0.00 sec)

    以上 unbounded preceding 用来获取表 t1 按照字段 ID 来分组,并且对字段 r1 求和。由于都是以第一行,也就是 r1 = 1 为基础求和,也就是求取上一行和当前行相加的结果,基于第一行记录。这个例子中 r1 字段的第一行记录为 1,后面的所有求和都是基于第一行来累加的结果。

    3、unbounded following

    表示边界永远为最后一行

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows between unbounded preceding and unbounded following ) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 40 | | 1 | 2 | 40 | | 1 | 3 | 40 | | 1 | 9 | 40 | | 1 | 10 | 40 | | 1 | 15 | 40 | ------ ------ ----------- 6 rows in set (0.00 sec)

    以上用了 rows between 把边界局限在第一行和最后一行,这样每行的求和结果和不带边界一样,也就是下面查询:

    mysql: ytt_80 > select id,r1,sum(r1) over() as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 40 | | 1 | 2 | 40 | | 1 | 3 | 40 | | 1 | 10 | 40 | | 1 | 15 | 40 | | 1 | 9 | 40 | ------ ------ ----------- 6 rows in set (0.00 sec)

    4、expr preceding / following

    带表达式的边界,

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows 1 preceding) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 5 | | 1 | 9 | 12 | | 1 | 10 | 19 | | 1 | 15 | 25 | ------ ------ ----------- 6 rows in set (0.00 sec)

    带表达式的边界只是把无边界换成 具体的行号。上面的查询表达的意思是基于分组内每行记录和它上一条记录求和,不累加。可以看到 wf_result 的具体值,25 对应的是 10 和 15 求和,19 对应的是 9 和 10 求和。

    那以此类推,求每行和它上面两行的和:

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows 2 preceding) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 6 | | 1 | 9 | 14 | | 1 | 10 | 22 | | 1 | 15 | 34 | ------ ------ ----------- 6 rows in set (0.00 sec)

    再来求每行的前两行和后面四行相加的结果:

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc rows between 2 preceding and 4 following) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 25 | | 1 | 2 | 40 | | 1 | 3 | 40 | | 1 | 9 | 39 | | 1 | 10 | 37 | | 1 | 15 | 34 | ------ ------ ----------- 6 rows in set (0.00 sec)

    其实 rows 单元很简单,接下来看下 range ,range 稍微难理解些。

    5、range preceding / following

    求当前行值范围内的分组记录。这个没有 rows 好理解,rows 对应的是行号,range 对应的行值。看下面例子:

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc range 1 preceding) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 5 | | 1 | 9 | 9 | | 1 | 10 | 19 | | 1 | 15 | 15 | ------ ------ ----------- 6 rows in set (0.00 sec)

    这个例子包含的关键词 range 1 preceding ,是个表达式条件,表示对于分组内每一行来讲:以字段 r1 当前行值减去1的结果为边界来求和。具体点就是:第一行,r1 的值为 1,那 1-1=0, 由于表 t1 里没有找到 r1 = 0 的结果,所以此时 wf_result = 1,也就是等于当前行值;对于第五行,由于 r1 对应的值为 10 , 10 - 1 = 9 ,表 t1 里 r1 = 9 是存在的,此时求和结果为 9 10 = 19。

    再次带上范围来看下另外一个例子:

    mysql: ytt_80 > select id,r1,sum(r1) over(partition by id order by r1 asc range between 1 preceding and 1 following) as wf_result from t1 where id = 1; ------ ------ ----------- | id | r1 | wf_result | ------ ------ ----------- | 1 | 1 | 3 | | 1 | 2 | 6 | | 1 | 3 | 5 | | 1 | 9 | 19 | | 1 | 10 | 19 | | 1 | 15 | 15 | ------ ------ ----------- 6 rows in set (0.00 sec)

    这个例子 over 子句里指定一个边界范围,也就是对每行值减1和加1后对应的记录来求和。比如第一行:r1 = 1,1 -1 =0,1 1 =2,表 t1 没有 r1 = 0 的记录,但是有 r1 = 2 的记录,所以第一行的窗口求和结果为 3;再来看看 r1 = 10 的这行,10-1 = 9,10 1 = 11, 表 t1 里有 r1 = 9 的记录,没有 r1 = 11 的记录,所以这里的求和结果为 9 10=19.

    这里举例说明了 MySQL 8.0 窗口函数 frame 子句的用法,可能使用场景比较稀少,不过可以收藏起来以备不时之需。

    关键字:爱可生、MySQL数据库、数据库运维管理、开源数据库解决方案

    ,

    免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

      分享
      投诉
      首页