Hive 窗口函数
窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果,而窗口函数对其所作用的窗口中的每一行记录输出一条结果.
sql标准允许将所有聚合函数用作开窗函数,用over关键字区分开窗函数和聚合函数
基本语法
窗口函数的语法分为四个部分:
函数子句:指明具体操作,如sum-求和,first_value-取第一个值;
partition by子句:指明分区字段,如果没有,则将所有数据作为一个分区;
order by子句:指明了每个分区排序的字段和方式,也是可选的,没有就是按照表中的顺序;
窗口子句:指明相对当前记录的计算范围,可以向上(preceding),可以向下(following),也可以使用between指明,上下边界的值,没有的话默认为当前分区。ROWS BETWEEN,也叫做window子句, 数字+PRECEDING 向前n条数字+FOLLOWING 向后n条
UNBOUNDED PRECEDING 初始行
PRECEDING 前一行
CURRENT ROW 当前行
FOLLOWING 后一行
UNBOUNDED FOLLOWING 最后一行
实现原理
-
聚合函数是通过
group by
某一个或者是几个字段进行分组,并且每条数据只能进入一个分组,之后对组内的数据进行聚合操作; -
而窗口函数基于称为框(frame)的一组行,计算表的每一输入行的返回值,每一行可以属于一个或多个框。
常见用例就是查看某些值的滚动平均值,其中每一行代表一天,那么每行属于7个不同的框。
分类
-
聚合:
- count 统计条数
- sum 求和
- avg 求平均值
- max 求最大值
- min 求最小值
-
取值 :
- first_value 取窗口中的第一值
- last_value 取窗口中的最后一个值
- lag(col, n, DEFAULT) 用于统计窗口内向上第n行的值col :列名 n:向上n行,[可选,默认为1] DEFAULT :当向上n行为NULL时,取默认值;如果不指定,则为NULL
- lead(col, n, DEFAULT) 用于统计窗口内向下第n行的值,和lag相反col :列名 n:向下n行,[可选,默认为1] DEFAULT :当向上n行为NULL时,取默认值;如果不指定,则为NULL
-
排序:
- rank 排序,有相同分数,排名相同并对后续跳过,如分数5,5,8,9,则得到的结果未1,1,3,4
- dense_rank 排序,有相同的分数排名相同,但后续接上,如分数5,5,8,9,则得到的排序结果未1,1,2,3
- row_number 排序,相同分数按先来后到排序,无重复排序,如分数5,5,8,9,得到的结果为1,2,3,4
- ntitle
-
其他:
- cume_dis 小于等于当前值的行数/分组内总行数比如,统计小于等于当前薪水的人数,所占总人数的比例
- percent_rank 计算给定行的百分比排名。分组内当前行的RANK值-1/分组内总行数-1,可以用来计算超过了百分之多少的人。
- ntile(n) 将分区中的数据按照顺序划分为N片,返回当前片的值。注1:如果切片分布不均匀,默认增加第一个切片的分布注2:不支持rows between