大数据开发：Hive窗口函数入门

在Hive支持的诸多函数当中，窗口函数是重要的一部分，能够对数据库当中的数据，进行实时分析处理，这也是当下大数据分析处理当中的重点需求。今天的大数据开发学习分享，我们就来讲讲Hive窗口函数这个部分。

什么是窗口函数？

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的语法：

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

窗口函数的作用：

同时具有分组和排序的功能，且不减少原表的行数

窗口函数的优点：

简单、快速、功能多

核心内容：

虚构一个数据集，访问量的数据data：use_id、date、pv

1）聚合函数：sum、avg、count、max、min

‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)是窗口函数的用法，下面省去了partition，那么得到的结果就是直接截止当前use_id计算的结果，聚合函数也可以在partition分组下使用。

select *,

sum(pv) over() as col1,-- 所有行相加

sum(pv) over(order by use_id) as col2,-- 截止到当前行相加

avg(pv) over(order by use_id) as col3,-- 截止到当前行相加

count(pv) over(order by use_id) as col4,-- 截止到当前行计数

max(pv) over(order by use_id) as col5,-- 截止到当前行最大值

min(pv) over(order by use_id) as col6-- 截止到当前行最小值

from data

结果：col1是全部pv求和，col2是截止当前use_id的pv求和，col3是截止当前use_id的pv求平均值，col4是截止当前use_id的pv计数，col5是截止当前use_id的pv的最大值，col6是截止当前use_id的pv最小值。

2）排序函数：rank、dense_rank、row_number

rank函数：如果有并列名次的行，会占用下一名次的位置。

dense_rank函数：如果有并列名次的行，不占用下一名次的位置。

row_number函数：不考虑并列名次的情况，依据顺序排列下去。

下面这个例子同样是省去了partition，当然它也可以在partition分组下使用。

select *,

rank() over(order by pv desc) as col1,-- 如果有并列名次的行，会占用下一名次的位置

dense_rank() over(order by pv desc) as col2,-- 如果有并列名次的行，不占用下一名次的位置

row_number() over(order by pv desc) as col3-- 不考虑并列名次的情况

from data

结果：col1是按照pv大小排序，由高到低，占位排序，并列会占用名次，名次可能出现空缺，col2是按照pv大小排序，由高到低，不占位排序，并列不会占用名次，名次不出现空缺，col3是按照pv大小排序，由高到低，不考虑并列，名次不出现空缺。

3）分组函数：ntile

用于将分组数据按顺序切成分段的n片，返回当前切片值，如果切片不均匀，优先满足组别小的分组。

select use_id,date,pv,

ntile(2) over(partition by use_id order by date) as col1, -- 分成2组

ntile(3) over(partition by use_id order by date) as col2, -- 分成3组

ntile(4) over(partition by use_id order by date) as col3 -- 分成4组

from data

结果：col1是use_id分组下，按照data升序分成2组，use_id=5仅有一组，那么分组为1，优先满足组别小的分组，use_id=2有3行数据，那么有2个1，1个2。col2是use_id分组下，按照data升序分成3组。col3是use_id分组下，按照data升序分成4组。

4）滑动窗口：

preceding：往前

following：往后

current row：当前行

unbounded：起点

unbounded preceding：表示从前面的起点

unbounded following：表示到后面的终点

select use_id,date,pv,

sum(pv) over(partition by use_id order by date) as col1, -- 默认为从起点到当前行

sum(pv) over(partition by use_id order by date rows between unbounded preceding and current row) as col2, --从起点到当前行，结果同pv1

sum(pv) over(partition by use_id) as col3,        --分组内所有行

sum(pv) over(partition by use_id order by date rows between 2 preceding and current row) as col4, --当前行+往前2行

sum(pv) over(partition by use_id order by date rows between 2 preceding and 1 following) as col5, --当前行+往前2行+往后1行

sum(pv) over(partition by use_id order by date rows between current row and unbounded following) as col6 ---当前行+往后所有行

from data

order by use_id asc, date asc

结果：col1是use_id分组下，data升序，对pv进行分组求和。col2是use_id分组下，data升序，对pv进行分组从起点到当前列求和。col3是use_id分组下，对pv进行求和。col4是use_id分组下，data升序，从当前列和前两列对pv进行分组求和。例如use_id=1，data=20200103，col4为sum(pv)=1+5+3=9。col5是use_id分组下，data升序，从当前列+前两列+后一列对pv进行分组求和。例如use_id=1，data=20200103，col5为sum(pv)=1+5+3+1=10。col6是use_id分组下，data升序，从当前列+后面全部列对pv进行分组求和。例如use_id=1，data=20200103，col6为sum(pv)=3+1=4。

5）查询函数：

lag、lead可以返回上下数据行的数据

first_value取分组内排序后，截止到当前行，第一个值

last_value取分组内排序后，截止到当前行，最后一个值

select use_id,date,pv,

lag(date,2) over(partition by use_id order by date) as col1, -- 往上2行

lead(date,2) over(partition by use_id order by date) as col2, -- 往下2行

first_value(date) over(partition by use_id order by date) as col3, -- 分组内排序后，截止到当前行，第一个值

last_value(date) over(partition by use_id order by date) as col4 -- 分组内排序后，截止到当前行，最后一个值

from data

结果：col1是use_id分组下，data升序，当前列往上两列的data，例如：use_id=1，data=20200103，col1就是往上两列的20200101。col2是use_id分组下，data升序，当前列往下两列的data，例如：use_id=1，data=20200101，col2就是往下两列的20200103。col3是use_id分组下，data升序，截止到当前行，第一个data。例如：use_id=1，data=20200103，col3=20200101。col4是use_id分组下，data升序，截止到当前行，最后一个data。例如：use_id=1，data=20200103，col4=20200103。

6）占比函数：

cume_dist：计算某个窗口或分区中某个值的累积分布。

percent_rank：计算给定行的百分比排名。

select use_id,date,pv,

cume_dist() over(partition by use_id order by date) as col1, -- 小于等于当前值的行数/分组内总行数

rank() over(partition by use_id order by date) as col2,-- 如果有并列名次的行，会占用下一名次的位置

percent_rank() over(partition by use_id order by date) as col3 -- 分组内当前行的RANK值-1/分组内总行数-1

from data

结果：col1是use_id分组下，data升序，小于等于当前值的行数/分组内总行数，例如：use_id=1，data=20200103，col1=3/4。col2是use_id分组下，data升序，进行排序。例如：use_id=1，data=20200101，col2=3。col3是use_id分组下，data升序，分组内当前行的RANK值-1/分组内总行数-1。例如：use_id=1，data=20200103，col3=(3-1)/(4-1)=0.66667。

关于大数据开发学习，Hive窗口函数入门，以上就为大家做了简单的介绍了。Hive窗口函数是学习当中需要掌握的重点部分，先要理解各个函数的适用场景，其次要能够灵活地运用起来解决问题。