Hive开窗函数、窗口帧

文章目录

前言
一、什么是开窗函数
- 1、概念
- 2、开窗函数都有哪些
- - (1)row_number 无并列排名
  - (2)dense_rank:并列排名，并且依次递增
  - (3)rank：有并列排名，不依次递增
  - (4)percent_rank: (rank的结果-1)/(分区内数据的个数-1)
  - (5)cume_dist: 计算某个窗口或分区中某个值的累计分布
  - (6)NTILE(n)：对分区内数据再分成n组，然后打上组号
  - (7)max、min、avg、count、sum：基于每个partition分区内的数据做对应的计算
- 3、开窗函数如何使用
- - （1）首先给出测试数据
  - （2）建表语句
  - （3）需求
二、窗口帧
- 1、窗口帧的概念、作用
- 2、格式
- 3、例子

前言

    在sql中有一类函数叫做聚合函数，例如sum()、avg()、max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是，哟偶是我们想要既显示聚集后的数据，这时我们便引入了窗口函数。

一、什么是开窗函数 1、概念

    好像给每一份数据开一扇窗户，所以叫开窗函数

2、开窗函数都有哪些 (1)row_number 无并列排名

用法：

select xxxx,row_number() over (partition by 分组字段 order by 排序字段 desc) as rn from 需要查询的表 group by xxxx

(2)dense_rank:并列排名，并且依次递增

select *,dense_rank() over (partition by clazz order by score desc) as s from new_score;

(3)rank：有并列排名，不依次递增

select *,rank() over (partition by clazz order by score desc) as s from new_score;

(4)percent_rank: (rank的结果-1)/(分区内数据的个数-1)

select *,percent_rank() over (partition by clazz order by score desc) as s from new_score;

(5)cume_dist: 计算某个窗口或分区中某个值的累计分布

假定升序排序，则使用以下公式确定累积分布：小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。

select *,cume_dist() over (partition by clazz order by score desc) as s from new_score;

(6)NTILE(n)：对分区内数据再分成n组，然后打上组号

select *,ntile(3) over (partition by clazz order by score desc) as s from new_score;

(7)max、min、avg、count、sum：基于每个partition分区内的数据做对应的计算

直接使用即可
例如：

select *,count(*) over (partition by clazz) from new_score;
select *,max(score) over (partition by clazz order by score desc) from new_score;

3、开窗函数如何使用

下面引入几个例子来说明

例：

（1）首先给出测试数据

111,69,class1,department1
112,80,class1,department1
113,74,class1,department1
114,94,class1,department1
115,93,class1,department1
121,74,class2,department1
122,86,class2,department1
123,78,class2,department1
124,70,class2,department1
211,93,class1,department2
212,83,class1,department2
213,94,class1,department2
214,94,class1,department2
215,82,class1,department2
216,74,class1,department2
221,99,class2,department2
222,78,class2,department2
223,74,class2,department2
224,80,class2,department2
225,85,class2,department2

（2）建表语句

create table new_score(
id int
,score int
,clazz string
,department string
) row format delimited fields terminated by “,”;

（3）需求

①求每个班级有多少人并且输出所有的学生信息

select ,count() over (partition by clazz) from new_score;

输出的结果为

②求每个学生跟班级里学生最高分的差距有多大

select *,score.s-score.score from (select *,max(score) over (partition by clazz) as s from new_score) score;

③求每个班级的学生成绩前三名

select * from (select *,row_number() over (partition by clazz order by score desc) as s from new_score) as t where t.s<=3;

二、窗口帧 1、窗口帧的概念、作用

用于从分区中选择指定的多条记录，供窗口函数处理

Hive提供了两种定义窗口帧的形式：rows和range。两种类型都需要配置上界和下界。例如，rows between unbounded preceding and current row 表示选择分区起始记录到当前记录的所有行；**sum(close) range between 2 preceding and 2 following ** 则通过字段差值来进行选择。如当前行的 close 字段值是 90，那么这个窗口帧的定义就会选择分区中 close 字段值落在 88 至 92 区间的记录。以下是所有可能的窗口帧定义组合。如果没有定义窗口帧，则默认为 range between unbounded preceding and current row。

只能运用在max、min、avg、count、sum、FIRST_VALUE、LAST_VALUE这几个窗口函数上

2、格式

row between 2 preceding and 2 following

3、例子

例如上面的表new_score求成绩最大值最大值
使用格式1

select *,max(score) over (partition by clazz order by score desc range between 2 preceding and 2 following) from new_score;

使用窗口帧得出的结果

求和：前两行的值+当前行的值+后两行的值
使用格式2
有边界

select *,sum(score) over (partition by clazz order by score desc rows between 2 preceding and 2 following) from new_score;

无边界
select *,sum(score) over (partition by clazz order by score desc rows between current row and unbounded following) from new_score;

Hive开窗函数、窗口帧

大数据系统相关栏目本月热门文章