窗口函数
1. 什么是窗口函数
窗口可以理解为记录集合,窗口函数是在满足某种条件的记录集合上执行的特殊函数。
- 静态窗口:随着记录不同,窗口大小都是固定的
- 动态窗口:不同的记录对应着不同的窗口
1.1 窗口函数和普通聚合函数
窗口函数 | 聚合函数 |
---|---|
每条记录都会执行,有几条记录执行完还是几条 | 多条记录聚合为一条 |
每组保留多个值 | 每组保留一个值 |
聚合函数可存在于窗口函数中 |
1.2 窗口函数语法
窗口函数的作用域位于over()子句中,窗口函数有3个元素:分区、排序和框架。
窗口函数的一般框架
<窗口函数>
OVER (
[PARTITION BY <列清单>]
[ORDER BY <排序用清单列>] [ASC/DESC]
(ROWS | RANGE)<范围条件>
)
默认情况下,用于计算的一组行(“窗口”)是整个数据集,可以使用ORDER BY子句进行排序。然后使用PARTITION BY子句将窗口缩小到数据集内的特定组。
-
函数子句:
指明具体操作,如sum求和,first_value取第一个值; -
partition by子句:
指明分区字段,如果没有,则将所有数据作为一个分区; -
order by子句:
指明了每个分区排序的字段和方式,也是可选的,默认是按照表中的顺序; -
窗口子句:
指明相对当前记录的计算范围,可以向上(preceding),可以向下(following),也可以使用between指明上下边界的值,没有的话默认为当前分区。
滑动窗口的范围指定方式有:基于行和基于范围
-
基于行:
通常使用
BETWEEN frame_start AND frame_end
语法来表示行范围,frame_start和frame_end可以支持如下关键字,来确定不同的动态行记录
。CURRENT ROW 边界是当前行,一般和其他范围关键字一起使用
。UNBOUNDED PRECEDING 边界是分区中的第一行
。UNBOUNDED FOLLOWING 边界是分区中的最后一行
。expr PRECEDING 边界是当前行减去expr的值
。expr FOLLOWING 边界是当前行加上expr的值
比如,下面都是合法的范围:
rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。
rows UNBOUNDED FOLLOWING 窗口范围是当前行到分区中的最后一行。
rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行,等同于不写。
- 基于范围:
和基于行类似,但有些范围不是直接可以用行数来表示的,比如希望窗口范围是一周前的订单开始,截止到当前行,则无法使用rows来直接表示,此时就可以使用范围来表示窗口:INTERVAL 7 DAY PRECEDING。Linux中常见的最近1分钟、5分钟负载是一个典型的应用场景。
有的函数不管有没有frame子句,它的窗口都是固定的,也就是静态窗口,这些函数包括如下:
- CUME_DIST()
- DENSE_RANK()
- LAG()
- LEAD()
- NTILE()
- PERCENT_RANK()
- RANK()
- ROW_NUMBER()
1. 分区函数
partition by语句定义,对当前计算的窗口函数进行限制,如果没有指定partition by子句,窗口就没有限制,即如果没有指定分区就是将整个查询结果当做一个分区。
2. 排序函数
排序元素定义计算的顺序,如果与分区有关,是对分区内排序,但对于不同的函数(排序窗口函数和聚合窗口函数)有点不同
3. 框架元素
框架是一个筛选器,对分区内的进一步的限制。适合于聚合函数、偏移函数等。
2. 窗口函数的类型
窗口函数有4种函数类型:排序函数、偏移函数、聚合函数和分布函数。
2.1 排序函数
- row_number(),rank(),dense_rank()
不需要指定参数 - ntile()
把窗口分区里的数据行分成数量大致相等的块,块的个数由输入的参数决定
2.2 偏移函数
- lead(),lag()
偏移量相对于当前的行 - first_value(),last_value(),nth_value()
偏移量相对于窗口的开始和结尾
2.3 聚合函数
- sum(),count(),avg(),min(),max()
2.4 分布函数
- cume_dist(),percent_rank()