Hadoop入门——Hive窗口函数

目录

函数组成及窗口函数概念

函数组成

窗口函数概念

窗口函数三大类（功能划分）

1. 排序类

（1）row_number()

（2）rank()

（3）dense_rank()

（4）ntile(n)

2. 聚合类

3. 分析类

（1）cume_dist()

（2）lag/lead(col,n,default)

（3）first_value/last_value(col)

窗口的定义

---

函数组成及窗口函数概念

函数组成

函数由函数名，函数类型，函数参数，函数体组成。

窗口函数概念

自0.11.0版本开始，Hive加入了窗口函数功能。窗口函数是一组特殊的函数，它能扫描多个输入行以计算各输出值，可为每行数据分别生成一行结果记录，几乎所有复杂的聚合计算都可以通过它来完成。语法如下：

function(arg1,...) over([partition by <...>][order by <...> [<window_clause>]])

窗口函数注意事项：
（1）窗口函数会作为select语句中的一列出现，类似基本聚合函数。

（2）over()表示当前查询的结果集上进行操作，操作有分区与排序两种，均可选。

（3）partition by类似于group by，表示对当前结果集按其中某列进行分组。

（4）只有在指定order by子句后才能进行窗口定义（window_clause），窗口定义不常用，但功能强大。

（5）一个select语句中，可以多次出现窗口函数。

（6）如果需要对窗口函数的计算结果进行过滤，必须在窗口函数所在的select语句往外一层进行操作。

窗口函数三大类（功能划分）

1. 排序类

（1）row_number()

该函数基于over对象分组、排序的结果，为每一行分组记录返回一个序号，从1开始递增，序号总是连续的。

select *,
row_number()
over(partition by t_dep_id order by cast(t_id as int)) # cast类型转换函数
from table;

（2）rank()

该函数与row_number类似，但产生序号的连续性上的规则不同。当同时出现两个第一名时将并列第一而跳过第二，接下来的排名从第三名开始。

（3）dense_rank()

dense_rank与（1）（2）相比，在出现两个并列第一的情况下，不会跳号，将保持序号连续。

（4）ntile(n)

ntile(n)将 over 对象的分组结果数据集按照顺序平均切分成n片，并为每一行记录返回一个切片号。

# 查询数据的前1/3，当数据不均时会增加第一个切片的数据
select t_c_id,t_id,t_data,
ntile(3)
over(partition by t_c_id order by t_data desc)
from table limit 10;

2. 聚合类

常用聚合类及对应返回over对象分组内的值

聚合类	over对象分组内的值
count(col)	总行数
sum(col)	总和
max(col)	最大值
min(col)	最小值
avg(col)	平均值

3. 分析类

（1）cume_dist()

返回小于等于当前值的行数与分组内总行数的比值。

（2）lag/lead(col,n,default)

统计窗口内往上/下第n行的值。第一个参数为列名，第二参数为往上/下第n行（可选，默认为1)，第三个参数表示当往上/下第n行为null 时取该默认值，默认为 null。(lag:上，lead:下)

（3）first_value/last_value(col)

返回 over对象分组内第一个值/最后一个值。

窗口的定义

窗口的定义由窗口子句“[<window_clause>]”来实现，用于进一步细分分组结果并应用分析函数。

窗口子句不支持的函数包括：rank、ntile、dense_rank、cume_dist、percent_rank、lead、lag。

窗口分为两类：行类型窗口（行窗口）与范围类型窗口（范围窗口）。

最后，本篇文章是基于我所学所知进行的知识总结，如有误论，虚心接受指正。参考文献是《Hadoop数据仓库实战》，感兴趣的小伙伴可以对其进行深入阅读。