Hive中常用的窗口函数

窗口函数简介与总结

在构建数据仓库或者进行数据分析时，难免会使用Hive中的窗口函数完成一些较复杂的ETL工作，现对Hive中常用的窗口函数进行总结与记录。

在Hive中，一般会使用窗口函数生成新的一列，使用样式为：

select 字段1， 字段2，..., window_function() over(partition by 字段1 order by 字段2 )  as 新字段1 from 表1 where ......

常用的窗口函数有：

1.  窗口排序函数：ROW_NUMBER()、RANK()、DENSE_RANK()；

基于上文中的使用样式，窗口排序函数是先根据字段1进行分组，组内根据字段2进行升序排序或降序排序，然后新生成的一列为排序的序号，三个排序函数之间有一些区别如下。

• ROW_NUMBER()函数，生成的排序序号从1开始，按照顺序，生成分组内记录的序号，不存在相同序号。
• RANK()函数，生成的排序序号从1开始，字段2数值相同的行，序号相同，且会在排序序号中留下空位。
• DENSE_RANK()函数，生成的排序序号从1开始，对于字段2数值相同的行，序号相同，但在排序序号中不会留下空位。

2. 窗口聚合函数：SUM()、MIN()、MAX()、AVG()；

窗口聚合函数可以搭配窗口子句使用， 如：

select 字段1，字段2, ..., sum(字段3) over( partition by 字段1 order by 字段2 rows between unbounded preceding and current row) as 新字段1 
// 新字段1含义为计算当前分组中，从第一行到当前行字段3的和。

其中ROWS BETWEEN叫窗口子句， 其中CURRENT ROW表示当前行，UNBOUNDED PRECEDING表示前面的起点，UNBOUNDED FOLLOWING表示后面的终点，当没有写窗口子句时，语义为从第一行到当前行。

MIN()、MAX()、AVG()的用法与SUM()一样。 

3. LAG()

LAG(col，n，DEFAULT) 用于取窗口内列col往前第N行的值，其中第一个参数为列名，第二个参数表示往前取n行（可选，默认为1），第三个参数为默认值（当列col往前第n行为NULL时，取该默认值，如不指定，则为NULL）。

4. LEAD()

LEAD(col, n, DEFAULT) 与函数LAG类似，不过取数方向相反，用于去窗口内列col往后第n行的值，其中第一个参数为列名，第二个参数表示往后取n行（可选，默认为1），第三个参数为默认值（即当列col往后取n行为NULL时，取该默认值，若不指定，则为NULL）。

5. FIRST_VALUE()

用法：FIRST_VALUE(col), 表示分组排序后，截止到当前行排名第一的那一行对应的列col的值。

6. LAST_VALUE()

用法：LAST_VALUE(col)，表示分组排序后，截止到当前行排名最后一名的那一行对应的列col的值。

7. NTILE()

NTILE函数的作用是，对数据分组排序后，将有序的数据平均分配到指定数量的桶中，即给每一行一个桶的编号。用法：NTILE(n) OVER(PARTITION BY 字段1 ORDER BY 字段2)。基于此，可以在查询结果的外面再嵌套一层查询语句，这样就可以过滤出排序之后的前N分之几或者后N分之几的数据，达到数据抽样的效果。

2. 使用窗口函数的CASE

 待补充。