Hive 窗口函数

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