Hive窗口函数(排序、聚合、分析)

窗口函数

窗口函数是一组特殊的函数,通过扫描多个输入行来计算输出值,为每行数据生成一个结果,我们可以通过窗口函数实现复杂的计算和聚合。
语法格式:

Function (arg1,…, arg n) OVER ([PARTITION BY <…>] [ORDER BY
<…>] [<window_clause>])

这里的partition by 不是分区,而是列斯与group by,未指定则按整个结果集,只有在指定order by之后才能进行窗口定义,当然也可以同时使用多个窗口函数,过滤窗口函数计算结果必须在外面一层。窗口函数的功能主要有三种:排序,聚合和分析。接下来我们一一介绍。

排序

row_number()
row_number()是对多有数值输出不同的序号,序号唯一连续。
我们以下面的数据为例,包括学生的id,班级,分数,截止当前行的总分数:

K001,1,86,86
K002,1,95,181
K003,2,89,270
K004,1,83,353
K005,2,86,439
K006,3,92,531
K007,3,86,617
K008,1,88,705

create table student(
id string,
class int,
score int,
sum int)
row format delimited
fields terminated by ','
lines terminated by '\n';

用load将本地数据导入student表

load data local inpath '/opt/student.txt'
into table student;
select *,row_number()over(partition by class) from student;

结果以班级划分,按总分数排序。
在这里插入图片描述

select *,row_number()over(partition by class order by score desc) from student;

指定排序
在这里插入图片描述
rank()
对相同的数值,输出相同的序号,下一个序号跳过。
这里为了方便演示,我又在原有数据上追加了相同的数据。

select *,rank() over(partition by score) from student;

如果不指定排序,则会全部输出1,因为每一个分数和相同的相比都是一样大的,而排序后就会产生不一样的结果:
在这里插入图片描述

 select *,rank() over(partition by class order by score) from student;

加入排序后可以进行排名:
在这里插入图片描述
dense_rank()
对相同的数值,输出相同的序号,下一个序号连续。

select *,dense_rank() over(partition by class order by score) from student;

在这里插入图片描述
nlite(n)
对分组数据按照顺序切片分成n片,返回当前切片值。

select *,ntile(4) over(partition by class order by score) from student;

在这里插入图片描述

聚合

聚合函数包括有:
count():计数,可以和distinct一起用。使用distinct时会很耗资源;
sum():求和;
AVG():求平均数;
Max()/Min():求最大值/最小值;
从Hive2.1.0开始支持在over()子句中使用聚合函数,因为本文中所用的是1.1.0版本的,所以无法做演示。
新建一个表,数据还是用原先的。

create table students like student;
load data local inpat '/opt/student.txt'
into table students;

count:

select *,count(1) over(partition by class order by score)from students;

在这里插入图片描述

select *,count(1) over(partition by class)from students;

如果不排序的话,则会在每行数据后显示全部的统计数:
在这里插入图片描述
sum:

select *,sum(score) over(partition by class)from students;

同count,不排序只会显示分组总的成绩。
在这里插入图片描述

select *,sum(score) over(partition by class order by score )from students;

在这里插入图片描述

分析

窗口函数分析所使用到的方法主要有:
Lead/Lag(col,n):某一列

select lead(class,2) over(partition by class)from students;

在这里插入图片描述
first_value
取到当前行为止的第一个值

 select*,first_value(sum)over(partition by class order by score) from students;

在这里插入图片描述
last_value
取到当前行为止的最后一个值,所以取得值都是当前行的值。

select*,last_value(score)over(partition by class order by score desc) from students;

在这里插入图片描述

窗口定义

行窗口:根据当前行之前或之后的行号确定的窗口
ROWS BETWEEN <start_expr> AND <end_expr>

<start_expr>可以为下列值:
UNBOUNDED PRECEDING : 窗口起始位置(分组第一行);
CURRENT ROW:当前行;
N PRECEDING/FOLLOWING:当前行之前/之后n行;

<end_expr>可以为下列值:
UNBOUNDED FOLLOWING : 窗口结束位置(分组最后一行)
CURRENT ROW:当前行

select*,max(score)over(partition by class order by score
rows between 2 preceding and current row) from students;

从当前行往前2行到当前行为止,最高分:
在这里插入图片描述
从当前行往前两行到当前行往后2行为止的最高分:

select*,max(score)over(partition by class order by score
rows between 2 preceding and 2 following) from students;
select*,max(score)over(partition by class order by score 
rows between unbounded preceding and 2 following) from students;

从起始位置到当前行后两行为止的最高分:
在这里插入图片描述

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页