阿里云大数据利器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

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上优化写法,例如可以排查哪几个键值比较大单独拉出来一个任务执行。

有对大数据技术感兴趣的,可以加笔者的微信 wx4085116.目前笔者已经从阿里离职,博客不代表阿里立场。笔者开了一个大数据培训班。有兴趣的加我。

上一篇:你好,生产力(1) - 一切的起源VisiCalc


下一篇:一分钟了解阿里云产品:消息队列