Hive--开窗函数

窗口函数（分析函数）

一：问题引入

​ 例题：求部门中年龄最大的那个人？

​ 表：test_dept

​ 不使用窗口函数怎么做？

​	select dept, max(age) ,name 

​	from test_dpt 

​	group by dept;

​ 但是这样会报错，因为group by执行顺序在select 前面，group by前面只能是group by 后面的字段和聚合函数，显然name不是；group by有局限性。

二：开窗函数的好处

​ 1）突破group by 查询的极限性

​ 2）有效解决分组 top N

三：开窗函数的使用

​ 开窗函数的关键字是over，over(partiton by+ orer by | distribute by + sort by);参数1：开窗依据；参数2；每一个窗口中的排序依据；

1）over字句和聚合函数一起使用

​ 聚合函数+over(distribute by +sort by | partiton by + order by)

​ 表结构以及数据如下：


​ 开窗依据：dept 排序：age

​ ①当over子句中指定distrubute by + sort by 的时候，求的是每一个窗口内的累计到当前为止的最大值，执行顺序是：1.distribute by ;2.sort by ;求出一个窗口，对这个窗口里面的数据做聚合函数；

select 
sid,name,sex,age,dept,
sum(age) over(distribute by dept sort by age)
from student;

​ 结果如下：

​ ②指定distribute by，求得就是指定窗口的聚合函数

​ 求每个部门中的年龄最大值

select sid,name,sex,dept,
max(age) over(distribute by dept) 
from student;

​ 执行结果：

2）over字句和row_number | rank | dense_rank 一起使用

​ over字句用来开窗，后面的函数才是功能点

​ ①row_number:针对每一个窗口显示行号的，从1开始，并且每一个窗口都是顺序递增的

​ 开窗依据：dept

​ 排序：sort

select 
sid,name,age,sex,dept,
row_number() over(partition by dept order by age) r_no
from student;

​ 运行结果如下：

​ 注意：1.row_number() 在这里相当于被查询的字段，要注意语法 ；

​ 2.不要忘记加row_number()后面的括号

​ 例题1：求每一个部门中年龄最大的那个人

​ 开窗依据：partition by dept order by age desc

​ 功能：row_number()

select * from 
(select 
sid,name,sex,age,dept,
row_number() over(partition by dept order by age desc) no
from student) a where a.no=1;

​ 运行结果如下：

​ 例题二：每一个部门中年龄最小的的前两个人

​ 开窗依据：dept

​ 功能：取两个

select * from 
(select 
sid,name,sex,age,dept,
row_number() over(distribute by dept sort by age) no
from student) a
where a.no<=2;

​ 运行结果如下：

​ ②rank():排名，累计了名次

select 
sid,name,sex,age,dept,
rank() over(distribute by dept sort by age) no 
from student;

​ 运行结果：

​ ③dense_rank和开窗函数一起使用

select 
sid,name,sex,age,dept,
dense_rank() over(partition by dept order by age) no 
from student;

四：开窗函数的总结

​ 1.开窗函数只是用来做分析用，本身没有功能，需要和功能函数配合使用；

​ 2.功能函数放在前面，over()放在后面；

​ 3.over()字句中的执行顺序是先partition by | distribute by ，再 order by+ sort by；