【Analytic】分析函式之DENSE_RANK函式
我們透過這個文章來看一下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函式的語法描述。
DENSE_RANK( )
OVER([ query_partition_clause ] order_by_clause)
2.透過實驗看一下DENSE_RANK函式的使用方法
1）建立測試表T，並初始化9條資料。
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,'Secooler',600);
sec@ora10g> insert into t values (3,'Hou',600);
sec@ora10g> insert into t values (3,'Mary',300);
sec@ora10g> insert into t values (3,'Ellen',200);
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 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的基本使用方法
sec@ora10g> 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函式，比較一下他們的區別。
sec@ora10g> col rn for 99
sec@ora10g> col rank for 99
sec@ora10g> col dense_rank for 99
sec@ora10g> 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取每組的前四
sec@ora10g> 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取每組的前四
sec@ora10g> 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取每組的前四
sec@ora10g> 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”描述：
Good luck.
secooler
10.01.13
-- The End --
【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函式的語法描述。
DENSE_RANK( )
OVER([ query_partition_clause ] order_by_clause)
2.透過實驗看一下DENSE_RANK函式的使用方法
1）建立測試表T，並初始化9條資料。
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,'Secooler',600);
sec@ora10g> insert into t values (3,'Hou',600);
sec@ora10g> insert into t values (3,'Mary',300);
sec@ora10g> insert into t values (3,'Ellen',200);
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 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的基本使用方法
sec@ora10g> 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函式，比較一下他們的區別。
sec@ora10g> col rn for 99
sec@ora10g> col rank for 99
sec@ora10g> col dense_rank for 99
sec@ora10g> 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取每組的前四
sec@ora10g> 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取每組的前四
sec@ora10g> 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取每組的前四
sec@ora10g> 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”描述：
Good luck.
secooler
10.01.13
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-625115/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Analytic】分析函式之MIN函式函式
- 【Analytic】分析函式之MAX函式函式
- 【Analytic】分析函式之AVG函式函式
- 【Analytic】分析函式之RANK函式函式
- 【Analytic】分析函式之COUNT函式函式
- 【Analytic】分析函式之ROW_NUMBER函式函式
- 【Analytic】分析函式之FIRST_VALUE函式函式
- 【Analytic】分析函式之LAST_VALUE函式函式AST
- 分析函式DENSE_RANK 和 RANK函式
- 【SQL 學習】分析函式之RANK() DENSE_RANK ()SQL函式
- oracle 分析函式之(rank()/dense_rank()/row_number())Oracle函式
- 分析函式——keep(dense_rank first/last)函式AST
- Oracle 中分析函式用法之--rank(),dense_rank(),partition,over()Oracle函式
- rank,dense_rank,row_number 分析函式函式
- oracle分析函式Rank, Dense_rank, row_numberOracle函式
- ORACLE HANDBOOK系列之一：Oracle分析函式（Analytic Function）Oracle函式Function
- 【分析函式】Oracle分析函式之LAG和LEAD函式Oracle
- Oracle vs PostgreSQL Develop（14） - 分析函式KEEP DENSE_RANKOracleSQLdev函式
- 分析函式——排序排列（rank、dense_rank、row_number）函式排序
- Spark2 Dataset分析函式--排名函式row_number,rank,dense_rank,percent_rankSpark函式
- Oracle聚合函式/分析函式Oracle函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- Oracle分析函式七——分析函式案例Oracle函式
- 字串函式之Strtok()函式字串函式
- 分析函式rank,dense_rank,row_number使用和區別 .函式
- 分析函式中rank(),row_number(),dense_rank()的區別函式
- Python 擴充之特殊函式（lambda 函式，map 函式，filter 函式，reduce 函式）Python函式Filter
- 分析函式函式
- 分析函式之視窗子句函式
- 分析函式之排名統計函式
- Oracle分析函式與視窗函式Oracle函式
- ORACLE單行函式與多行函式之七：多行函式之分組函式示例Oracle函式
- 常見函式之單行函式函式
- oracle 10g函式大全--分析函式Oracle 10g函式
- sql優化用group by 函式代替分析函式SQL優化函式
- 分析函式概述函式
- 分析函式 over函式
- Oracle 分析函式Oracle函式