Hive的分析函数的使用

我们先准备数据库、表和数据

 

 

 

开窗分析函数相对于mysql中的聚合函数区别是：分析函数是基于某个组多个数据进行计算聚合，而聚合函数是每个组返回一行数据。

官网

 

 

 

我们先做个例子感受下：

要求: 查询部门编号为10的所有的员工，按照薪资降序排序

Mysql实现：select empno,ename,deptno,sal from emp where deptno=10 order by sal desc;

 

如果要求是：将每个部门薪资最高的那个人的薪资显示在最后一行。

select empno,ename,deptno,sal, max(sal) over(partition by deptno order by sal desc) as sal_max from emp;

 

 

我们看官网这个位置

 

 

我们来解释里面的概念

当前有一个数据

 

CURRENT ROW

 

UNBOUNDED PRECEDING 初始行

PRECEDING 前一行

CURRENT ROW 当前行

FOLLOWING 后一行

UNBOUNDED FOLLOWING 最后一行

 

所以上面的语句我们变动下

select empno,ename,deptno,sal, max(sal) over(partition by deptno order by sal desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sal_max from emp;

可以控制窗口大小

 

结果是一样的，但是窗口大小我们做了变化

 

我们准备数据

 

创建一个表

 

 

载入数据

load data local inpath '/data/hivetest/anal.txt' into table analytical;

 

语句1：

 

 

我们看结果

 

语句2

 

 

 

语句3

 

 

语句4

 

 

语句5

 

 

语句6

 

 

已上是窗口和基本语句的使用。

我们来看ROW_NUMBER

 

将每个部门薪资最高的那个人的薪资降序排序并且显示唯一的编号ROW_NUMBER

 

 

获取每个部门薪资最高的前两位(嵌套子查询)