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

xiaoxiao2023-07-06 130

看到很多用户经常会问如何对分组内进行排序。官方文档：https://help.aliyun.com/document_detail/34994.html?spm=5176.doc27891.6.611.Q1bk3j例如需求：

odps 里面能否做排名操作，比如一个表里面有用户ID 和金额两个字段，用金额大小排序的话，我如何计算用户的排名（金额最大的是第一名，以此类推）计算每个金融产品的最大投资者，或者前几名

类似这一类的需求，我们总结为实现分组内的排序，取TopN，那么在hive中有两个个函数可以分开实现first_value: 取分组内排序后，截止到当前行，第一个值，row_number()：实现组内排序，并对组内行进行标记行号。那在odps中没有first_value这个函数，但是同样可以实现top1的需求。下面用一个大家最爱的公司员工表来举例实现

员工表：

empnoenamejobmgrhiredatesalcommdeptno7839KINGPRESIDENTNULL1981-11-17 00:00:005000.0NULL107566JONESMANAGER78391981-04-02 00:00:002975.0NULL207698BLAKEMANAGER78391981-05-01 00:00:002850.0NULL307788SCOTTANALYST75661987-07-13 01:00:003000.0NULL207782CLARKMANAGER78391981-06-09 00:00:002450.0NULL107369SMITHCLERK79021980-12-17 00:00:00800.0NULL207902FORDANALYST75661981-12-03 00:00:003000.0NULL207876ADAMSCLERK77881987-07-13 01:00:001100.0NULL207900JAMESCLERK76981981-12-03 00:00:00950.0NULL307934MILLERCLERK77821982-01-23 00:00:001300.0NULL107499ALLENSALESMAN76981981-02-20 00:00:001600.0300.0307654MARTINSALESMAN76981981-09-28 00:00:001250.01400.0307844TURNERSALESMAN76981981-09-08 00:00:001500.00.0307521WARDSALESMAN76981981-02-22 00:00:001250.0500.030

使用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 跟排序字段+升降关键字默认升序排列。

结果：

jobsalrnANALYST3000.01CLERK1300.01MANAGER2975.01PRESIDENT5000.01SALESMAN1600.01

同理如果想实现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;

结果：

jobenamesalrnANALYSTSCOTT3000.01ANALYSTFORD3000.02CLERKMILLER1300.01CLERKADAMS1100.02MANAGERJONES2975.01MANAGERBLAKE2850.02PRESIDENTKING5000.01SALESMANALLEN1600.01SALESMANTURNER1500.02

那这是一个简单的例子。对于类似需求可以用这个方法来实现TopN的计算。注意：这种方法对于数量级不是很大的或者分组比较均匀的大数据量实用，如果分组键值不均匀，导致单个或者几个键值比较大，那会有数据倾斜的问题。此时我们可以从sql上优化写法，例如可以排查哪几个键值比较大单独拉出来一个任务执行。

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