看到很多用户经常会问如何对分组内进行排序。
官方文档:https://help.aliyun.com/document_detail/34994.html?spm=5176.doc27891.6.611.Q1bk3j
例如需求:
1. odps 里面能否做排名操作,比如一个表里面有 用户 ID 和 金额 两个字段,用金额大小排序的话,我如何计算用户的排名(金额最大的是 第一名 ,以此类推)
2. 计算每个金融产品的最大投资者,或者前几名
类似这一类的需求,我们总结为实现分组内的排序,取 TopN,那么在 hive 中有两个个函数可以分开实现
first_value: 取分组内排序后,截止到当前行,第一个值,
row_number():实现组内排序,并对组内行进行标记行号。
那在 odps 中没有 first_value 这个函数,但是同样可以实现 top1 的需求。下面用一个大家最爱的公司员工表来举例实现
员工表:
empno | ename | job | mgr | hiredate | sal | comm | deptno |
---|---|---|---|---|---|---|---|
7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.0 | NULL | 10 |
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.0 | NULL | 20 |
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.0 | NULL | 30 |
7788 | SCOTT | ANALYST | 7566 | 1987-07-13 01:00:00 | 3000.0 | NULL | 20 |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.0 | NULL | 10 |
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.0 | NULL | 20 |
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.0 | NULL | 20 |
7876 | ADAMS | CLERK | 7788 | 1987-07-13 01:00:00 | 1100.0 | NULL | 20 |
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.0 | NULL | 30 |
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.0 | NULL | 10 |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.0 | 300.0 | 30 |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.0 | 1400.0 | 30 |
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0 | 0.0 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.0 | 500.0 | 30 |
使用 row_number() 对相同 job 的薪水 sal 进行
排序,取组内最大,等不及了,直接上 sql
```select * from (
select job,sal,
row_number() over(partition by job order by sal desc) as rn from emp
) a where rn=1;
//partition by 跟分组字段
//order by 跟排序字段 + 升降关键字 默认升序排列。
- 结果:
- job | sal | rn |
- -----|----------|------------
- ANALYST | 3000.0 | 1
- CLERK | 1300.0 | 1
- MANAGER | 2975.0 | 1
- PRESIDENT | 5000.0 | 1
- SALESMAN | 1600.0 | 1
- 同理如果想实现topN,那把rn=1改成rn<N,例如我想看每个岗位job前两个工资最多的人,sql如下
- select * from (
- select job,ename,sal,
- row_number() over(partition by job order by sal desc) as rn from emp
- ) a where rn<3;
- 结果:
- job | ename | sal | rn |
- ----|-------|----------|------------
- ANALYST | SCOTT | 3000.0 | 1 |
- ANALYST | FORD | 3000.0 | 2 |
- CLERK | MILLER | 1300.0 | 1 |
- CLERK | ADAMS | 1100.0 | 2 |
- MANAGER | JONES | 2975.0 | 1 |
- MANAGER | BLAKE | 2850.0 | 2 |
- PRESIDENT | KING | 5000.0 | 1 |
- SALESMAN | ALLEN | 1600.0 | 1 |
- SALESMAN | TURNER | 1500.0 | 2 |
- +-----+-------+------------+------------+
- 那这是一个简单的例子。
- 对于类似需求可以用这个方法来实现TopN的计算。
- 注意:这种方法对于数量级不是很大的或者分组比较均匀的大数据量实用,
- 如果分组键值不均匀,导致单个或者几个键值比较大,那会有数据倾斜的问题。此时我们可以从sql上优化写法,例如可以排查哪几个键值比较大单独拉出来一个任务执行。
来源: https://yq.aliyun.com/articles/111557