1.介绍
普通聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。
因此,普通聚合函数每组(Group by)只有一个返回值,而开窗函数则可以为窗口中的每行都返回一个值。
1.1 基础结构
分析函数(如:sum(), max(), row_number()...) + 窗口子句(over函数)
1.2 over函数
over(partition by [column_n] order by [column_m] rows between 开始位置 and 结束位置)先按照column_n分区,相同的column_n分为一区,每个分区查询指定位置的数据并根据column_m排序(默认升序)
over()函数中的窗口范围说明:
1.3 函数
常用分析函数:
-
聚合类
avg()、sum()、max()、min() -
排名类
row_number() 按照值排序时产生一个自增编号,不会重复
rank() 按照值排序时产生一个自增编号,值相等时会重复,会产生空位
dense_rank() 按照值排序时产生一个自增编号,值相等时会重复,不会产生空位 -
其他类
lag(列名,往前的行数,[行数为null时的默认值,不指定为null])
lead(列名,往后的行数,[行数为null时的默认值,不指定为null])
ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号
-
注意点:
-
over()函数中的分区、排序、指定窗口范围可组合使用也可以不指定,根据不同的业务需求结合使用
-
over()函数中如果不指定分区,窗口大小是针对查询产生的所有数据,如果指定了分区,窗口大小是针对每个分区的数据
over()函数中的窗口范围说明:
-
current row:当前行
-
unbounded:起点,unbounded preceding 表示从前面的起点, unbounded following表示到后面的终点
-
n preceding :往前n行数据
-
n following:往后n行数据