我們通過這個文章來看一下ROW_NUMBER函數的用法。這是一個很重要的分析函數，在得到分組排序前N條記錄和後N條記錄上有着自己的重要位置。
【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
【Analytic】分析函數之ROW_NUMBER函數：http://space.itpub.net/519536/viewspace-624886
【Analytic】分析函數之RANK函數：http://space.itpub.net/519536/viewspace-624985
1.萬變不離其宗，先看DENSE_RANK函數的文法描述。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions043.htm#SQLRF00633
DENSE_RANK( )
OVER([ query_partition_clause ] order_by_clause)
2.通過實驗看一下DENSE_RANK函數的使用方法
1）建立測試表T，并初始化9條資料。
[email protected]> create table t (group_id number(10), name varchar2(10), salary int);
[email protected]> insert into t values (1,'Tom',1200);
[email protected]> insert into t values (2,'Kary',2400);
[email protected]> insert into t values (2,'Joe',800);
[email protected]> insert into t values (3,'Erick',3600);
[email protected]> insert into t values (3,'Andy',600);
[email protected]> insert into t values (3,'Secooler',600);
[email protected]> insert into t values (3,'Hou',600);
[email protected]> insert into t values (3,'Mary',300);
[email protected]> insert into t values (3,'Ellen',200);
[email protected]> commit;
2）T表全貌
[email protected]> select * from t;
GROUP_ID NAME SALARY
---------- ------------------------------ ----------
1 Tom 1200
2 Kary 2400
2 Joe 800
3 Erick 3600
3 Andy 600
3 Secooler 600
3 Hou 600
3 Mary 300
3 Ellen 200
9 rows selected.
共三組資料，group_id分别是1、2和3。第1組有一個人，第2組有兩個人，第3組有六個人。最後一列是每個人的薪水值，注意第三組中的Andy、Secooler和Hou的薪水都是相同的。
3）分析函數DENSE_RANK的基本使用方法
[email protected]> select group_id, name, salary, dense_rank() over (PARTITION BY group_id ORDER BY salary) as dense_rank from t;
GROUP_ID NAME SALARY DENSE_RANK
---------- ------------------------------ ---------- ----------
1 Tom 1200 1
2 Joe 800 1
2 Kary 2400 2
3 Ellen 200 1
3 Mary 300 2
3 Hou 600 3
3 Secooler 600 3
3 Andy 600 3
3 Erick 3600 4
9 rows selected.
DENSE_RANK函數與RANK函數相同點是，當同組的薪水值相同時DENSE_RANK的值相同；不同點是，DENSE_RANK不會出現RANK函數的跳躍現象。
4）我們同時使用row_number、rank和dense_rank函數，比較一下他們的差別。
[email protected]> col rn for 99
[email protected]> col rank for 99
[email protected]> col dense_rank for 99
[email protected]> select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary) as rn, rank() over (PARTITION BY group_id ORDER BY salary) as rank, dense_rank() over (PARTITION BY group_id ORDER BY salary) as dense_rank from t;
GROUP_ID NAME SALARY RN RANK DENSE_RANK
---------- ------------------------------ ---------- --- ---- ----------
1 Tom 1200 1 1 1
2 Joe 800 1 1 1
2 Kary 2400 2 2 2
3 Ellen 200 1 1 1
3 Mary 300 2 2 2
3 Hou 600 3 3 3
3 Secooler 600 4 3 3
3 Andy 600 5 3 3
3 Erick 3600 6 6 4
9 rows selected.
上面的結果清晰的表明了ROW_NUMBER、RANK和DENSE_RANK的差別。在擷取前N條記錄和後N條記錄的需求裡各有使用場合。
5）使用ROW_NUMBER取每組的前四
[email protected]> select *
2 from (select GROUP_ID,
3 name,
4 salary,
5 ROW_NUMBER () over (partition by GROUP_ID order by salary)
6 as rn,
7 RANK () over (partition by GROUP_ID order by salary) as RANK,
8 DENSE_RANK () over (partition by GROUP_ID order by salary)
9 as DENSE_RANK
10 from t)
11 where rn <= 4;
GROUP_ID NAME SALARY RN RANK DENSE_RANK
---------- ------------------------------ ---------- --- ---- ----------
1 Tom 1200 1 1 1
2 Joe 800 1 1 1
2 Kary 2400 2 2 2
3 Ellen 200 1 1 1
3 Mary 300 2 2 2
3 Andy 600 3 3 3
3 Hou 600 4 3 3
7 rows selected.
6）使用RANK取每組的前四
[email protected]> select *
2 from (select GROUP_ID,
3 name,
4 salary,
5 ROW_NUMBER () over (partition by GROUP_ID order by salary)
6 as rn,
7 RANK () over (partition by GROUP_ID order by salary) as RANK,
8 DENSE_RANK () over (partition by GROUP_ID order by salary)
9 as DENSE_RANK
10 from t)
11 where RANK <= 4;
GROUP_ID NAME SALARY RN RANK DENSE_RANK
---------- ------------------------------ ---------- --- ---- ----------
1 Tom 1200 1 1 1
2 Joe 800 1 1 1
2 Kary 2400 2 2 2
3 Ellen 200 1 1 1
3 Mary 300 2 2 2
3 Andy 600 3 3 3
3 Hou 600 4 3 3
3 Secooler 600 5 3 3
8 rows selected.
7）使用DENSE_RANK取每組的前四
[email protected]> select *
2 from (select GROUP_ID,
3 name,
4 salary,
5 ROW_NUMBER () over (partition by GROUP_ID order by salary)
6 as rn,
7 RANK () over (partition by GROUP_ID order by salary) as RANK,
8 DENSE_RANK () over (partition by GROUP_ID order by salary)
9 as DENSE_RANK
10 from t)
11 where DENSE_RANK <= 4;
GROUP_ID NAME SALARY RN RANK DENSE_RANK
---------- ------------------------------ ---------- --- ---- ----------
1 Tom 1200 1 1 1
2 Joe 800 1 1 1
2 Kary 2400 2 2 2
3 Ellen 200 1 1 1
3 Mary 300 2 2 2
3 Andy 600 3 3 3
3 Hou 600 4 3 3
3 Secooler 600 5 3 3
3 Erick 3600 6 6 4
9 rows selected.
3.小結
ROW_NUMBER、RANK和DENSE_RANK都是很貼心的分析函數，也是用得比較普遍的，領會後必将裨益無限。
有關分析函數的擴充可以參考Oracle的官方文檔中的“Analytic Functions”描述：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174
Good luck.
secooler
10.01.13
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-625115/，如需轉載，請注明出處，否則将追究法律責任。
轉載于:http://blog.itpub.net/519536/viewspace-625115/
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)