大数据框架 Hive—— 通过sql案例分析窗口函数

本篇转自作者：无名一小卒

为什么需要使用分析窗口函数 有如下需求：求每个部门中工资前三的人

乍一听感觉这个需求很简单

但是这个简单的案例中，涉及到了分组，排序，条件查询，子查询，如果用 mysql 来实现

这语句将会特别复杂，不相信的可以亲自来实现一下这个需求

而且语句也很不容易懂

求每个部门中工资前三的人 mysql 语句如下 意思就是：我们要查询的这个人，在这部门中工资比他高的少于 3 个人（0,1,2 人）。
括号里面是查询在这部门中工资比他高的人的数量。
很不容易懂

select
d.Name Department,e1.Name Employee,e1.Salary 
from
emp e1,dept d
where e1.deptno=d.deptno and
(select count(distinct e2.sal) from emp e2 where e2.deptno=d.deptno and e2.sal>e1.sal)<3
order by 
d.dname,e1.sal DESC;

这时候，如果使用 hive 中的分析窗口函数，将会很简单
ROW_NUMBER（）方法，排序后加上行号
hive 中的 sql 语句

hive (db_practise)> select empno,ename,sal,deptno,
                  > ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal desc) as rnk
                  > from tb_emp;

执行结果：这时候排名就已经出来了，想要显示前三只需要加一个 where rnk < 4; 即可

RANK（）方法：
从上面的结果可以看出，即时工资一样，但是排名还是分出了先后
那么 RANK 方法就是为了解决这个问题，
工资一样的人的排名将会一样

hive 中的语句如下：和第一个案例并没有实质区别，只是方法不同

hive (db_practise)> select
                  >    empno,ename,sal,deptno,
                  > RANK() OVER(PARTITION BY deptno ORDER BY sal desc) as rnk
                  > from tb_emp;

执行结果：排名已经一样了

DENSE_RANK（）方法
从上面的结果可以看到一个现象，
那就是两个排名为 1 后面的人的排名为 3
有时候这是不合理的，那么 DENSE_RANK（）就可以解决这个问题

hive 语句如下：和上个案例并没有实质区别，只是方法不同

hive (db_practise)> select
                  >    empno,ename,sal,deptno,
                  > DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal desc) as rnk
                  > from tb_emp;

执行结果，可以看到名次已经发生了变化

此处只针对需求使用了部分分析窗口函数，更多有关分析窗口函数使用的方法可以参考下面这篇文章，还是比较全面的

https://blog.youkuaiyun.com/Abysscarry/article/details/81408265
本篇转自作者：无名一小卒