0 前言
工作中经常使用窗口函数处理一些复杂的业务,很久之前就想把自己对窗口函数的理解记录下来,始终没有动笔。现抽取打游戏时间(已经是王者啦,一段时间不打,段位也不会掉下来的啦),把自己对窗口函数的理解以及怎么巧妙的用到工作中记录一下,说明一下,使用的数据库环境为MySQL8.0,MySQL8.0之前的版本不支持窗口函数,当然MSSQL和Oracle以及PostgreSQL等都已经完全支持窗口函数了。学会巧用窗口函数,会觉得写代码是一种享受的过程。
1 窗口函数简介
窗口函数又称分析函数,窗口函数针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数对每一组返回多个值,就是说使用窗口函数,不改变原来数据集的行数。窗口函数的作用域位于over子句中,窗口函数有3个元素:分区、排序和框架。
1.1 分区元素
分区元素由partition by语句定义,对当前计算的窗口函数进行限制,如果没有指定partition by子句,窗口就没有限制,就是说,如果没有指定分区,就是将整个查询结果当做一个分区。
1.2 排序元素
排序元素定义计算的顺序,如果与分区有关,是对分区内排序,但对于不同的函数(排序窗口函数和聚合窗口函数)有点不同,下文会举例说明。
1.3 框架元素
简单的理解:框架是一个筛选器,对分区内的进一步的限制。适合于聚合函数、偏移函数等。
2 窗口函数的类型
窗口函数有4种函数类型:排序函数、偏移函数、聚合函数和分布函数。
2.1 排序函数
排序函数包含:row_number()、rank()、dense_rank()和ntile()四个函数,我个人将前三个函数归为一类,ntile()归为一类,不仅因为前3个函数不需要输入参数,更重要的是因为ntile()函数把窗口分区里的数据行分成数量大致相等的块,块的个数由输入的参数决定。
2.2 偏移函数
偏移函数包含:lead()、lag()、first_value()、last_value()、nth_value()函数等,可将lead()和lag()函数归为一类:因为这2个函数偏移量是相对于当前的行;将first_value()、last_value()、nth_value()归为一类,因为这3个函数的偏移量相对于窗口的开始和结尾。
2.3 聚合函数
聚合函数包含sum()、count()、avg()、min()、max()函数等,聚合窗口函数可以理解发育好的苏烈,既是输出位,又是坦克位,聚合窗口函数既可以实现常规的聚合功能,也可以实现累计计算的功能,下文会详细介绍的。
2.4 分布函数
分布函数包含cume_dist()、percent_rank()等,这类函数,不经常使用,不敢造次。
3 总结
窗口函数主要有3个元素:分区、排序和框架;4种类型函数:排序函数、偏移函数、聚合函数和分布函数。简单的介绍一些基本概念,结合具体的实例才能真正地理解窗口函数,本篇就当做前期的猥琐发育,后面才是真正伤害的时候。