大数据学习：Hive窗口函数入门
作者：张老师 浏览次数： 2021-05-08 17:24
在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窗口函数是学习当中需要掌握的重点部分，先要理解各个函数的适用场景，其次要能够灵活地运用起来解决问题。成都加米谷大数据，专业大数据培训，大数据开发、数据分析挖掘，零基础班本月正在招生中，课程大纲及试学视频可联系客服获取！
什么是窗口函数？
窗口函数，也叫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窗口函数是学习当中需要掌握的重点部分，先要理解各个函数的适用场景，其次要能够灵活地运用起来解决问题。成都加米谷大数据，专业大数据培训，大数据开发、数据分析挖掘，零基础班本月正在招生中，课程大纲及试学视频可联系客服获取！
