?http://blog.csdn.net/rokii/article/details/6429145

排列（rank ()）函數(shù)。這些排列函數(shù)提供了定義一個(gè)集合（使用 PARTITION 子句），然后根據(jù)某種排序方式對(duì)這個(gè)集合內(nèi)的元素進(jìn)行排列的能力，下面以scott用戶的emp表為例來(lái)說(shuō)明rank over partition如何使用

?

1）查詢員工薪水并連續(xù)求和

select deptno,ename,sal,

sum(sal)over (order by ename) sum1,? /*表示連續(xù)求和*/
sum(sal)over () sum2,?????????????????????????? /*相當(dāng)于求和sum(sal)*/
100* round(sal/sum(sal)over (),4) "bal%"
from emp

結(jié)果如下：

??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? bal%
---------- ---------- ---------- ---------- ---------- ----------
??????? 20 ADAMS??????????? 1100?????? 1100????? 29025?????? 3.79
??????? 30 ALLEN??????????? 1600?????? 2700????? 29025?????? 5.51
??????? 30 BLAKE??????????? 2850?????? 5550????? 29025?????? 9.82
??????? 10 CLARK??????????? 2450?????? 8000????? 29025?????? 8.44
??????? 20 FORD???????????? 3000????? 11000????? 29025????? 10.34
??????? 30 JAMES???????????? 950????? 11950????? 29025?????? 3.27
??????? 20 JONES??????????? 2975????? 14925????? 29025????? 10.25
??????? 10 KING???????????? 5000????? 19925????? 29025????? 17.23
??????? 30 MARTIN?????????? 1250????? 21175????? 29025?????? 4.31
??????? 10 MILLER?????????? 1300????? 22475????? 29025?????? 4.48
??????? 20 SCOTT??????????? 3000????? 25475????? 29025????? 10.34

??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? bal%
---------- ---------- ---------- ---------- ---------- ----------
??????? 20 SMITH???????????? 800????? 26275????? 29025?????? 2.76
??????? 30 TURNER?????????? 1500????? 27775????? 29025?????? 5.17
??????? 30 WARD???????????? 1250????? 29025????? 29025?????? 4.31

?

2）如下：

select deptno,ename,sal,
sum(sal)over (partition by deptno order by ename) sum1,/*表示按部門(mén)號(hào)分氏，按姓名排序并連續(xù)求和*/
sum(sal)over (partition by deptno) sum2,/*表示部門(mén)分區(qū)，求和*/
sum(sal)over (partition by deptno order by sal) sum3,/*按部門(mén)分區(qū)，按薪水排序并連續(xù)求和*/
100* round(sal/sum(sal)over (),4) "bal%"
from emp

結(jié)果如下：

??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? SUM3?????? bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
??????? 10 CLARK??????????? 2450?????? 2450?????? 8750?????? 3750?????? 8.44
??????? 10 KING???????????? 5000?????? 7450?????? 8750?????? 8750????? 17.23
??????? 10 MILLER?????????? 1300?????? 8750?????? 8750?????? 1300?????? 4.48
??????? 20 ADAMS??????????? 1100?????? 1100????? 10875?????? 1900?????? 3.79
??????? 20 FORD???????????? 3000?????? 4100????? 10875????? 10875????? 10.34
??????? 20 JONES??????????? 2975?????? 7075????? 10875?????? 4875????? 10.25
??????? 20 SCOTT??????????? 3000????? 10075????? 10875????? 10875????? 10.34
??????? 20 SMITH???????????? 800????? 10875????? 10875??????? 800?????? 2.76
??????? 30 ALLEN??????????? 1600?????? 1600?????? 9400?????? 6550?????? 5.51
??????? 30 BLAKE??????????? 2850?????? 4450?????? 9400?????? 9400?????? 9.82
??????? 30 JAMES???????????? 950?????? 5400?????? 9400??????? 950?????? 3.27

??? DEPTNO ENAME???????????? SAL?????? SUM1?????? SUM2?????? SUM3?????? bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
??????? 30 MARTIN?????????? 1250?????? 6650?????? 9400?????? 3450?????? 4.31
??????? 30 TURNER?????????? 1500?????? 8150?????? 9400?????? 4950?????? 5.17
??????? 30 WARD???????????? 1250?????? 9400?????? 9400?????? 3450?????? 4.31

?

3）如下：

select empno,deptno,sal,
sum(sal)over (partition by deptno) "deptSum",/*按部門(mén)分區(qū)，并求和*/
rank ()over (partition by deptno order by sal desc nulls last)? rank , /*按部門(mén)分區(qū)，按薪水排序并計(jì)算序號(hào)*/
dense_rank()over (partition by deptno order by sal desc nulls last) d_rank,
row_number()over (partition by deptno order by sal desc nulls last) row_rank
from emp

注：

rang()主要用于排序，并給出序號(hào)

dense_rank()：功能同rank ()一樣，區(qū)別在于，rank ()對(duì)于排序并的數(shù)據(jù)給予相同序號(hào)，接下來(lái)的數(shù)據(jù)序號(hào)直接跳中躍，dense_rank()則不是，比如數(shù)據(jù)：

???????????? 1，2，2，4，5，6.。。。。這是rank ()的形式

???????????? 1，2，2，3，4，5，。。。。這是dense_rank()的形式

???????????? 1，2，3，4，5，6.。。。。。這是row_number()涵數(shù)形式

row_number()則是按照順序依次使用，相當(dāng)于我們普通查詢里的rownum值

?

?

其實(shí)從上面三個(gè)例子當(dāng)中，不難看出over (partition by ... order by ...)的整體概念，我理解是

partition by :按照指字的字段分區(qū)，如果沒(méi)有則針對(duì)全體數(shù)據(jù)

order by????? :按照指定字段進(jìn)行連續(xù)操作（如求和(sum),排序(rank ()等)，如果沒(méi)有指定，就相當(dāng)于對(duì)指定分區(qū)集合內(nèi)的數(shù)據(jù)進(jìn)行整體sum操作

?

http://keke-wanwei.iteye.com/blog/138632

首先,要了解rank在英語(yǔ)的意思:等級(jí).也就是說(shuō)這是一個(gè)給數(shù)據(jù)確定等級(jí)的函數(shù).

以銷售為例,有地區(qū),年,月,銷售員,銷售額,記錄這五個(gè)字段.我們可以按地區(qū),年,月,銷售額對(duì)銷售員進(jìn)行排序,這樣對(duì)銷售員來(lái)說(shuō)就相當(dāng)于有一個(gè)等級(jí)概念了,第一名就是銷售最高的......,如果我們要找出每個(gè)地區(qū),年,月,銷售額的前三名銷售員.SQL如何寫(xiě)?

java 代碼

SELECT?area_code,?YEAR,?MONTH,?saleroom,saler ??

???????RANK?()?OVER?

????(PARTITION?BY?area_code,year?,month?ORDER?BY?area_code,year,month,saleroom?)?RANK???

FROM?t_sale??

現(xiàn)在RANK 就是1,2,3,3,3,6,有了這個(gè)字段,就很容易得到前三名的銷售員了.

新問(wèn)題:銷售額50000塊在深圳,2007年5月能排到第幾?

sql 代碼

SELECT? ??

??????RANK('SHENZHEN',2007,5,50000)??WITHIN?GROUP? ??

??????(ORDER?BY?area_code,year,month,saleroom)?Rank? ??

FROM?T_SALE??

上面這個(gè)SQL就可以搞定了.要注意的是,Rank()里的參數(shù)必須為常數(shù),或常值表達(dá)式,里面參數(shù)的個(gè)數(shù),類型也要和order by后字段的類型相對(duì)應(yīng).

上面就是Rank函數(shù)的兩個(gè)用法.另外還有一個(gè)dense_rank(),它的用法和rank()一樣,只是計(jì)算等級(jí)的方式不同.例如上面的

1,2,3,3,3,6.用dense_rank() 就是1,2,3,3,3,4.

?

http://blog.csdn.net/maqinqin/article/details/3320247

若不是這次使用oracle lead，我還不會(huì)發(fā)現(xiàn)，原來(lái)還有這么好用的東西。

???? oracle 統(tǒng)計(jì)分析函數(shù) lead

???? 語(yǔ)法結(jié)構(gòu)：

???? lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)

???? 參數(shù)說(shuō)明：

?????value_expr 值表達(dá)式，通常是字段，也可是是表達(dá)式。value_expr本身不支持分析函數(shù)，也就是lead不支持多層調(diào)用。
?????offset 偏移，應(yīng)該是很熟悉的數(shù)學(xué)概念了，或者是相對(duì)偏移，表格來(lái)開(kāi)當(dāng)前行的第offset行，如果offset是整數(shù)就表示是順序下的前第n行，如果是負(fù)數(shù)就是往后第n行。 如果不提供這個(gè)參數(shù)，就是默認(rèn)為1.
??????default 默認(rèn)值，如果沒(méi)有找到，應(yīng)該返回什么值的意思，有點(diǎn)類似nvl(col,value)。如果沒(méi)有設(shè)置，且找不到，那么就返回Null
??????over? 可以簡(jiǎn)單地翻譯為在什么。。。的基礎(chǔ)之上
??????query_partition_clause? 分區(qū)語(yǔ)句，對(duì)結(jié)果集合分區(qū)的語(yǔ)句，是可選的，如果沒(méi)有就是所有的一個(gè)分區(qū)。
??????Order_by_clause 排序語(yǔ)句 必須需要 ,形如order by xxx desc/asc

?

??? ?解釋示例：
?????SQL> select * from test_value;
??? ???
??? ??? ????? MONS JJR?????????????? CJL?????? CJJE
??? ??? ---------- ---------- ---------- ----------
??? ??? ??? 200801 LZF?????????????? 250?????? 1999
??? ??? ??? 200802 LZF?????????????? 200?????? 2000
??? ??? ??? 200803 LZF?????????????? 300?????? 1000
??? ??? ??? 200804 LZF??????????????? 23??????? 189
??? ??? ??? 200805 LZF?????????????? 356??????? 456
??? ??? ??? 200806 LZF?????????????? 100??????? 200
??? ??? ??? 200807 LZF?????????????? 600??????? 700
??? ??? ??? 200808 LZF??????????????? 23??????? 123
??? ??? ??? 200809 LZF?????????????? 400??????? 500
??? ???
??? ??? 9 rows selected
??? ???
??? ??? SQL>
??? ??? SQL> select rownum 序號(hào),Mons,cjl cjl_01,
??? ??? ? 2? lead(cjl,1) over (order by mons desc) cjl_02,
??? ??? ? 3? lead(cjl,2) over (order by mons desc) cjl_03,
??? ??? ? 4? lead(cjl,3) over (order by mons desc) cjl_04,
??? ??? ? 5? lead(cjl,4) over (order by mons desc) cjl_05,
??? ??? ? 6? lead(cjl,5) over (order by mons desc) cjl_06,
??? ??? ? 7? lead(cjl,6) over (order by mons desc) cjl_07,
??? ??? ? 8? lead(cjl,7) over (order by mons desc) cjl_08,
??? ??? ? 9? lead(cjl,8) over (order by mons desc) cjl_09
??? ??? ?10?? from test_value

????????????? 序號(hào)?????? MONS???? CJL_01???? CJL_02???? CJL_03???? CJL_04???? CJL_05???? CJL_06???? CJL_07???? CJL_08???? CJL_09
??? ??? ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
??? ??? ???????? 9???? 200809??????? 400???????? 23??????? 600??????? 100??????? 356???????? 23??????? 300??????? 200??????? 250
??? ??? ???????? 8???? 200808???????? 23??????? 600??????? 100??????? 356???????? 23??????? 300??????? 200??????? 250
??? ??? ???????? 7???? 200807??????? 600??????? 100??????? 356???????? 23??????? 300??????? 200??????? 250???????????
??? ??? ???????? 6???? 200806??????? 100??????? 356???????? 23??????? 300??????? 200??????? 250??????????????????????
??? ??? ???????? 5???? 200805??????? 356???????? 23??????? 300??????? 200??????? 250?????????????????????????????????
??? ??? ???????? 4???? 200804???????? 23??????? 300??????? 200??????? 250????????????????????????????????????????????
??? ??? ???????? 3???? 200803??????? 300??????? 200??????? 250???????????????????????????????????????????????????????
??? ??? ???????? 2???? 200802??????? 200??????? 250??????????????????????????????????????????????????????????????????
??? ??? ???????? 1???? 200801??????? 250???????????????????

??實(shí)踐使用示例：??????????

?? select id,lead(id,1)over(partition by? call_req_id order by call_req_id ,id),type,analyst,time_stamp from act_log order by call_req_id ,id

?

總結(jié)

以上是生活随笔為你收集整理的oracle的rank,over partition 使用 和lead的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。