hive 窗口函数_HIVE 窗口函数详解

什么是窗口函数

窗口函数是 SQL 中一类特别的函数。和聚合函数相似，窗口函数的输入也是多行记录。不 同的是，聚合函数的作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口， 这里，窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函 数，如 sum, max, min, avg,count 等也可以当作窗口函数使用。

窗口函数的实现原理

在用group-by处理数据分组时，每一行只能进入一个分组。窗口函数基于称为框 (f r a m e)的一组行，计算表的每一输入行的返回值，每一行可以属于一个或多个 框。常见用例就是查看某些值的滚动平均值，其中每一行代表一天，那么每行属于7个不同的框。

如下图所示，每一行是如何匹配多个窗口框的。

窗口函数使用场景

1. 分组排序，如取某年级每个班学习成绩排名前10的学生。
2. 分组聚合

基本语法

窗口函数的语法分为四个部分：

• 函数子句：指明具体操作，如sum-求和，first_value-取第一个值；
• partition by子句：指明分区字段，如果没有，则将所有数据作为一个分区；
• order by子句：指明了每个分区排序的字段和方式,也是可选的，没有就是按照表中的顺序；
• 窗口子句：指明相对当前记录的计算范围，可以向上(preceding)，可以向下(following),也可以使用between指明，上下边界的值，没有的话默认为当前分区。ROWS BETWEEN，也叫做window子句数字+PRECEDING 向前n条数字+FOLLOWING 向后n条CURRENT ROW 当前行UNBOUNDED 无边界，表示从最前面的起点开始，表示到最后面的终点UNBOUNDED PRECEDING 向前无边界UNBOUNDED FOLLOWING 向后无边界

窗口函数有哪些

窗口函数的功能分为：聚合、取值、排名、序列四种，前三种的使用场景比较常见，容易理解，最后一种(序列)的使用场景比较少。

• 聚合
  • 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_dist 小于等于当前值的行数/分组内总行数比如，统计小于等于当前薪水的人数，所占总人数的比例
  • percent_rank 计算给定行的百分比排名。分组内当前行的RANK值-1/分组内总行数-1，可以用来计算超过了百分之多少的人。
  • ntile(n) 将分区中的数据按照顺序划分为N片，返回当前片的值。注1：如果切片分布不均匀，默认增加第一个切片的分布注2：不支持