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

开发者社区> 上单> 正文

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

简介: 看到很多用户经常会问如何对分组内进行排序。官方文档:https://help.aliyun.com/document_detail/34994.html?spm=5176.doc27891.6.611.
+关注继续查看

看到很多用户经常会问如何对分组内进行排序。
官方文档: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.目前笔者已经从阿里离职,博客不代表阿里立场。笔者开了一个大数据培训班。有兴趣的加我。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
独家对话阿里云函数计算负责人不瞋:你所不知道的 Serverless
日前,阿里云凭借函数计算产品能力全球第一的优势,入选 Forrester 2021 年第一季度 FaaS 平台评估报告,成为比肩亚马逊、全球前三的 FaaS 领导者。这也是首次有国内科技公司进入 FaaS 领导者象限。不瞋作为阿里云 Serverless 产品体系的负责人,也是国内 Serverless 的早期实践者。本文将呈现这次访谈的完整总结。
764 0
PHPpraffa也有了,一个PHP版本的阿里云函数计算与API网关的开发框架
发布了Python版本的函数计算与API网关的开发框架后,一直觉得对不起PHP,因为公司一直是用PHP的,我这弄了个Python,实在不该,对了,(Python版本说明点这里。 PHPpraffa是什么? PHPpraffa 是praffa的PHP版本。
1161 0
请问:hive中avg聚合函数会使用到combiner功能吗?
hive avg函数是否可以使用combiner功能
2900 0
使用阿里云Serverless函数计算实现HTTP健康检查+故障短信通知
定时对网站/API进行请求,根据请求响应判断服务是否可用,网站是否存在宕机,当发生宕机时,发送短信通知管理员.
1082 0
+关注
上单
对大数据框架有浓厚兴趣,对阿里云产品使用,运维排查有经验,odps,dataworks,开放搜索等 喜欢技术分享交流
17
文章
0
问答
来源圈子
更多
MaxCompute(原ODPS)是一项面向分析的大数据计算服务,它以Serverless架构提供快速、全托管的在线数据仓库服务,消除传统数据平台在资源扩展性和弹性方面的限制,最小化用户运维投入,使您经济并高效的分析处理海量数据。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载