赞
开窗函数:
开窗函数是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,有 的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着 不同的窗口,这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算,只不过它更具灵活性, 它对数据的每一行,都使用与该行相关的行进行计算并返回计算结果。
语法:开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 滑动窗口>])
开窗函数的一个概念是当前行,当前行属于某个窗口,窗口由over关键字来指定函数执行的窗口范围,如果后面括号中什么都不写,则意味着窗口包含满足where条件的所有行,开窗函数基于所有行进行计算;如果不为空,则有三个参数来设置窗口:
partition by子句:按照指定字段进行分区,两个分区由边界分隔,开窗函数在不同的分区内分别 执行,在跨越分区边界时重新初始化。
order by子句:按照指定字段进行排序,开窗函数将按照排序后的记录顺序进行编号。可以和 partition by子句配合使用,也可以单独使用。
frame子句:当前分区的一个子集,用来定义子集的规则,通常用来作为滑动窗口使用。
对于滑动窗口的范围指定,通常使用 between frame_start and frame_end 语法来表示行范围, frame_start和frame_end可以支持如下关键字,来确定不同的动态行记录:
current row 边界是当前行,一般和其他范围关键字一起使用。
unbounded preceding 边界是分区中的第一行。
unbounded following 边界是分区中的最后一行。
expr preceding 边界是当前行减去expr的值。
expr following 边界是当前行加上expr的值。
动态窗口函数:first_value() / last_value()/nth_value()/聚合函数用于开窗。如没有指定排序和滑动窗口范围,默认计算的是分区内的所有记录。 指定分区和排序后,如没有指定滑动窗口范围,默认计算的是分区内的第一行到当前行。
静态窗口函数:row_number() / rank() / dense_rank()/percent_rank() / cume_dist()/lag() / lead()/ntile()
不管是否指定滑动窗口范围,窗口都是固定的,所以指定的滑动窗口范围无效。
开窗函数和普通聚合函数的区别:聚合函数是将多条记录聚合为一条;而开窗函数是每条记录都会执行,有几条记录执行完还是几 条。 聚合函数也可以用于开窗函数中。
举例:
在第一节基础上 更新了函数和执行顺序:实操了字符数函数、时间函数、字段截取函数、interval函数等,除最后顺序实操因未导入数据库,导致无法执行