阿裡雲大資料利器Maxcompute學習之--視窗函數實作分組TopN

看到很多使用者經常會問如何對分組内進行排序。

例如需求：

odps 裡面能否做排名操作，比如一個表裡面有 使用者id 和 金額 兩個字段，用金額大小排序的話，我如何計算使用者的排名（金額最大的是 第一名 ，以此類推）

計算每個金融産品的最大投資者，或者前幾名

類似這一類的需求，我們總結為實作分組内的排序，取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

10

7566

jones

manager

1981-04-02 00:00:00

2975.0

20

7698

blake

1981-05-01 00:00:00

2850.0

30

7788

scott

analyst

1987-07-13 01:00:00

3000.0

7782

clark

1981-06-09 00:00:00

2450.0

7369

smith

clerk

7902

1980-12-17 00:00:00

800.0

ford

1981-12-03 00:00:00

7876

adams

1100.0

7900

james

950.0

7934

miller

1982-01-23 00:00:00

1300.0

7499

allen

salesman

1981-02-20 00:00:00

1600.0

300.0

7654

martin

1981-09-28 00:00:00

1250.0

1400.0

7844

turner

1981-09-08 00:00:00

1500.0

0.0

7521

ward

1981-02-22 00:00:00

500.0

使用row_number()對相同 job 的薪水sal 進行

排序，取組内最大，等不及了，直接上sql

結果：

rn

1

同理如果想實作topn，那把rn=1改成rn

select * from (

) a where rn<3;

2

那這是一個簡單的例子。

對于類似需求可以用這個方法來實作topn的計算。

注意：這種方法對于數量級不是很大的或者分組比較均勻的大資料量實用，

如果分組鍵值不均勻，導緻單個或者幾個鍵值比較大，那會有資料傾斜的問題。此時我們可以從sql上優化寫法，例如可以排查哪幾個鍵值比較大單獨拉出來一個任務執行。

有對大資料技術感興趣的，可以加筆者的微信 wx4085116.目前筆者已經從阿裡離職，部落格不代表阿裡立場。筆者開了一個大資料教育訓練班。有興趣的加我。