我們透過這個文章來看一下ROW_NUMBER函式的用法。這是一個很重要的分析函式，在得到分組排序上有著自己的重要位置。
【Analytic】分析函式之MIN函式：http://space.itpub.net/519536/viewspace-624736
【Analytic】分析函式之MAX函式：http://space.itpub.net/519536/viewspace-624749
【Analytic】分析函式之AVG函式：http://space.itpub.net/519536/viewspace-624799

1.萬變不離其宗，先看ROW_NUMBER函式的語法描述。

ROW_NUMBER( )
   OVER ([ query_partition_clause ] order_by_clause)

2.透過實驗看一下ROW_NUMBER函式的使用方法
1）建立測試表T，並初始化幾條資料。
sec@ora10g> create table t (group_id number(10), name varchar2(10), salary int);
sec@ora10g> insert into t values (1,'Tom',1200);
sec@ora10g> insert into t values (2,'Kary',2400);
sec@ora10g> insert into t values (2,'Joe',800);
sec@ora10g> insert into t values (3,'Erick',3600);
sec@ora10g> insert into t values (3,'Andy',600);
sec@ora10g> insert into t values (3,'Mary',300);
sec@ora10g> commit;

2）T表全貌
sec@ora10g> select * from t;

GROUP_ID NAME                               SALARY
---------- ------------------------------ ----------
         1 Tom                                  1200
         2 Kary                                 2400
         2 Joe                                   800
         3 Erick                                3600
         3 Andy                                  600
         3 Mary                                  300

6 rows selected.

共三組資料，group_id分別是1、2和3。第1組有一個人，第2組有兩個人，第3組有三個人。最後一列是每個人的薪水值。

3）ROW_NUMBER函式應用之一：按照每組人的薪酬的高低進行排序。
sec@ora10g> select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary) as rn from t;

GROUP_ID NAME                               SALARY         RN
---------- ------------------------------ ---------- ----------
         1 Tom                                  1200          1
         2 Joe                                   800          1
         2 Kary                                 2400          2
         3 Mary                                  300          1
         3 Andy                                  600          2
         3 Erick                                3600          3

6 rows selected.

可見，在資料的最後一列我們新增了RN列，這一列可以得到每一組中人員薪酬從低到高的序號。

4）ROW_NUMBER函式應用之二：透過ROW_NUMBER計算得到的結果便可進一步得到每組薪酬最高的兩位員工（注意這裡沒有考慮到薪酬相同的情況）。
（1）錯誤用法
sec@ora10g> select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t where rn<=2;
select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t where rn<=2
                                                                                                                 *
ERROR at line 1:
ORA-00904: "RN": invalid identifier

注意RN是不能直接在where子句中使用的。

（2）正確用法
sec@ora10g> select * from (select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t ) where rn <= 2;

GROUP_ID NAME                               SALARY         RN
---------- ------------------------------ ---------- ----------
         1 Tom                                  1200          1
         2 Kary                                 2400          1
         2 Joe                                   800          2
         3 Erick                                3600          1
         3 Andy                                  600          2

sec@ora10g> select group_id,name from (select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t ) where rn <= 2;

GROUP_ID NAME
---------- ------------------------------
         1 Tom
         2 Kary
         2 Joe
         3 Erick
         3 Andy

使用巢狀SQL之後，一切恢復到了自然和諧的狀態。

3.小結
ROW_NUMBER函式在分組排序統計上有著自己的應用場合。細節之處需要注意，善用之。
有關分析函式的擴充套件可以參考Oracle的官方文件中的“Analytic Functions”描述：

Good luck.

secooler
10.01.11

-- The End --

【Analytic】分析函式之ROW_NUMBER函式

相關文章