突然發(fā)現(xiàn)DENSE_RANK是個(gè)不錯(cuò)的函數(shù),以前一直以為FIRST_VALUE,LAST_VALUE可以替代 ,但是其實(shí)不然.有時(shí)候可以用的到大家。 DENSE_RANK 功能描述:根據(jù)ORDER BY子句中表達(dá)式的值,從查詢返回的每一行,計(jì)算它們與其它行的相對(duì)位置。組內(nèi)的數(shù)據(jù)按ORDER BY子句排
突然發(fā)現(xiàn)DENSE_RANK是個(gè)不錯(cuò)的函數(shù),以前一直以為FIRST_VALUE,LAST_VALUE可以替代 ,但是其實(shí)不然.有時(shí)候可以用的到大家。
DENSE_RANK
功能描述:根據(jù)ORDER BY子句中表達(dá)式的值,從查詢返回的每一行,計(jì)算它們與其它行的相對(duì)位置。組內(nèi)的數(shù)據(jù)按ORDER BY子句排序,然后給每一行賦一個(gè)號(hào),從而形成一個(gè)序列,該序列從1開始,往后累加。每次ORDER BY表達(dá)式的值發(fā)生變化時(shí),該序列也隨之增加。有同樣值的行得到同樣的數(shù)字序號(hào)(認(rèn)為null時(shí)相等的)。密集的序列返回的時(shí)沒有間隔的數(shù).
FIRST
功能描述:從DENSE_RANK返回的集合中取出排在最前面的一個(gè)值的行(可能多行,因?yàn)橹悼赡芟嗟龋虼送暾恼Z法需要在開始處加上一個(gè)集合函數(shù)以從中取出記錄
SAMPLE:下面例子中DENSE_RANK按部門分區(qū),再按傭金commission_pct排序,F(xiàn)IRST取出傭金最低的對(duì)應(yīng)的所有行,然 后前面的MAX函數(shù)從這個(gè)集合中取出薪水最低的值;LAST取出傭金最高的對(duì)應(yīng)的所有行,然后前面的MIN函數(shù)從這個(gè)集合中取出薪水最高的值
LAST
功能描述:從DENSE_RANK返回的集合中取出排在最后面的一個(gè)值的行(可能多行,因?yàn)橹悼赡芟嗟龋虼送暾恼Z法需要在開始處加上一個(gè)集合函數(shù)以從中取出記錄
SAMPLE:下面例子中DENSE_RANK按雇用日期排序,F(xiàn)IRST取出salary最低的對(duì)應(yīng)的所有行,然后前面的MAX函數(shù)從這個(gè)集合中取出薪水最低的值;LAST取出雇用日期最高的對(duì)應(yīng)的所有行,然后前面的MIN函數(shù)從這個(gè)集合中取出薪水最高的值
SELECT
department_id,
first_name||' '||last_name employee_name,
hire_date,
salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date) OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) OVER (PARTITION BY department_id) "Best"
FROM employees
然后再舉個(gè)使用dense rank的例子,其實(shí)在有些特別的場景,比如我說統(tǒng)計(jì)部門最高工資里面入職最早員工的信息,dense rank 的first , last函數(shù)就非常好實(shí)現(xiàn).
下面例子是求最大最小值的,其實(shí)沒有完全利用到我剛才說的那個(gè)場景.
CREATE TABLE TEST( V1 VARCHAR2(20), V2 VARCHAR2(10), V3 VARCHAR2(10)) ;
Insert into TEST (V1, V2, V3) Values ('1', '1', 'm');
Insert into TEST (V1, V2, V3) Values ('1', '2', 'f');
Insert into TEST (V1, V2, V3) Values ('2', '1', 'n');
Insert into TEST (V1, V2, V3) Values ('2', '2', 'g');
Insert into TEST (V1, V2, V3) Values ('3', '1', 'b');
Insert into TEST (V1, V2, V3) Values ('3', '2', 'a');
Insert into TEST (V1, V2, V3) Values ('1', '3', 'a');
SQL> SELECT t.* ,t.rowid FROM test t order by v1,v2;
V1 V2 V3 ROWID
-------------------- ---------- ---------- ------------------
1 1 m AAASUkAAEAAAAisAAA
1 2 f AAASUkAAEAAAAisAAB
1 3 a AAASUkAAEAAAAisAAG
2 1 n AAASUkAAEAAAAisAAC
2 2 g AAASUkAAEAAAAisAAD
3 1 b AAASUkAAEAAAAisAAE
3 2 a AAASUkAAEAAAAisAAF
怎么實(shí)現(xiàn)如下結(jié)果:
V1 V3 V3
-------------------- ---------- ----------
1 m a
2 n g
3 b a
------------------------------------------------------------------------------------------------------------
Answer:
select v1
,max(v3) keep (dense_rank first order by v2)
,max(v3) keep (dense_rank last order by v2)
from test
group by v1;
-------------------------------------------------------------------------------------------------------------
<無> $velocityCount-->SELECT department_id, first_name||' '||last_name employee_name, hire_date, salary, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date) OVER (PARTITION BY department_id) "Worst", MAX(salary) KEEP (DENSE_RANK LAST ORDER BY hire_date) OVER (PARTITION BY department_id) "Best" FROM employees
CREATE TABLE TEST( V1 VARCHAR2(20), V2 VARCHAR2(10), V3 VARCHAR2(10)) ; Insert into TEST (V1, V2, V3) Values ('1', '1', 'm'); Insert into TEST (V1, V2, V3) Values ('1', '2', 'f'); Insert into TEST (V1, V2, V3) Values ('2', '1', 'n'); Insert into TEST (V1, V2, V3) Values ('2', '2', 'g'); Insert into TEST (V1, V2, V3) Values ('3', '1', 'b'); Insert into TEST (V1, V2, V3) Values ('3', '2', 'a'); Insert into TEST (V1, V2, V3) Values ('1', '3', 'a'); SQL> SELECT t.* ,t.rowid FROM test t order by v1,v2;
select v1 ,max(v3) keep (dense_rank first order by v2) ,max(v3) keep (dense_rank last order by v2) from test group by v1;
聲明:本網(wǎng)頁內(nèi)容旨在傳播知識(shí),若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系,我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com