阿里云大数据利器Maxcompute学习之--窗口函数实现分组TopN

原文地址

看到很多用户经常会问如何对分组内进行排序。
官方文档：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上优化写法，例如可以排查哪几个键值比较大单独拉出来一个任务执行。

原文地址