oracle 在分組內排序的方法（轉帖）

oracle 在分組內排序的方法

　　

oracle分析函數十分強大，我們只要掌握這些方法，更直接的說法就是知道這些分析函數的作用就能完成很多工作。

下邊貼出這些函數，及簡單應用。

其中我想對lag（）和lead（）函數坐下說明：lag（）本身是延後的意思也就是延後出現某列的數，而lead（）有引領、

領先的意思也就是提前幾行顯示某列資料

RANK()

dense_rank()

【文法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )

           dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

【功能】彙總函式RANK 和 dense_rank 主要的功能是計算一組數值中的排序值。

【參數】dense_rank與rank()用法相當，

【區別】dence_rank在並列關係是，相關等級不會跳過。rank則跳過

rank()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內） 

dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名。

【說明】Oracle分析函數

【樣本】

彙總函式RANK 和 dense_rank 主要的功能是計算一組數值中的排序值。

　　在9i版本之前，只有分析功能（analytic ），即從一個查詢結果中計算每一行的排序值，是基於order_by_clause子句中

的value_exprs指定欄位的。

　　其文法為：

　　RANK ( ) OVER ( [query_partition_clause] order_by_clause )

　　在9i版本新增加了合計功能（aggregate），即對給定的參數值在設定的排序查詢中計算出其排序值。這些參數必須是常數

或常值運算式，且必須和ORDER BY子句中的欄位個數、位置、類型完全一致。

　　其文法為：

　　RANK ( expr [, expr]... ) WITHIN GROUP

　　( ORDER BY

　　expr [ DESC | ASC ] [NULLS { FIRST | LAST }]

　　[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...

　　)

　　例子1：

　　有表Table內容如下

　　COL1　COL2

　　　 1　1

　　　 2　1

　　　 3　2

　　　 3　1

　　　 4　1

　　　 4　2

　　　 5　2

　　　 5　2

　　　 6　2

　　分析功能：列出Col2分組後根據Col1排序,並產生數字列。比較實用於在成績表中查出各科前幾名的資訊。

　　SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" from table a;

　　結果如下：

　　COL1　COL2　Rank

　　　 1　1　　 1

　　　 2　1　　 2

　　　 3　1　　 3

　　　 4　1　　 4

　　　 3　2　　 1

　　　 4　2　　 2

　　　 5　2　　 3

　　　 5　2　　 3

　　　 6　2　　 5

　　例子2：

　　TABLE：A （科目，分數）

　　數學，80

　　語文，70

　　數學，90

　　數學，60

　　數學，100

　　語文，88

　　語文，65

　　語文，77

　　現在我想要的結果是：（即想要每門科目的前3名的分數）

    數學，100

　　數學，90

　　數學，80

　　語文，88

　　語文，77

　　語文，70

　　那麼語句就這麼寫：

　　select * from (select rank() over(partition by 科目 order by 分數 desc) rk,a.* from a) t

　　where t.rk<=3;

　　例子3：

　　合計功能：計算出數值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以後的位置

　　SELECT RANK(4,3) WITHIN GROUP (ORDER BY col1,col2) "Rank" from table;

　　結果如下：

　　Rank

　　4

　　dense_rank與rank()用法相當，但是有一個區別：dence_rank在並列關係是，相關等級不會跳過。rank則跳過

　　例如：表

　　A　　　　　　B　　　　　　C

　　a　　　　　liu　　　　　wang

　　a　　　　　jin　　　　　shu

　　a　　　　　cai　　　　　kai

　　b　　　　　yang　　　　 du

　　b　　　　　lin　　　　　ying

　　b　　　　　yao　　　　　cai

　　b　　　　　yang　　　　 99

　　例如：當rank時為：

　　select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m

　　 A　　　　　B　　　　　　 C　　　　　LIU

　　 a　　　　　cai　　　　　 kai　　　　　1

　　 a　　　　　jin　　　　　 shu　　　　　2

　　 a　　　　　liu　　　　　 wang　　　　 3

　　 b　　　　　lin　　　　　 ying　　　　 1

　　 b　　　　　yang　　　　　du　　　　　 2

　　 b　　　　　yang　　　　　99　　　　　 2

　　 b　　　　　yao　　　　　 cai　　　　　4

　　而如果用dense_rank時為：

　　select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m

　　 A　　　　　B　　　　　　 C　　　　　LIU

　　 a　　　　　cai　　　　　kai　　　　　1

　　 a　　　　　jin　　　　　shu　　　　　2

　　 a　　　　　liu　　　　　wang　　　　 3

　　 b　　　　　lin　　　　　ying　　　　 1

　　 b　　　　　yang　　　　 du　　　　　 2

　　 b　　　　　yang　　　　 99　　　　　 2

　　 b　　　　　yao　　　　　cai　　　　　3 

ROW_NUMBER()

【文法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 

【功能】表示根據COL1分組，在分組內部根據 COL2排序，而這個值就表示每組內部排序後的順序編號（組內連續的唯一的） 

row_number() 返回的主要是“行”的資訊，並沒有排名

【參數】

【說明】Oracle分析函數

主要功能：用於取前幾名，或者最後幾名等

【樣本】

表內容如下：

name | seqno | description

A | 1 | test

A | 2 | test

A | 3 | test

A | 4 | test

B | 1 | test

B | 2 | test

B | 3 | test

B | 4 | test

C | 1 | test

C | 2 | test

C | 3 | test

C | 4 | test

我想有一個sql語句，搜尋的結果是 

A | 1 | test

A | 2 | test

B | 1 | test

B | 2 | test

C | 1 | test

C | 2 | test

實現: 

select name,seqno,description 

from(select name,seqno,description,row_number() over (partition by name order by seqno) id

from table_name) where id<=3;

lag()和lead()

【文法】

lag(EXPR,<OFFSET>,<DEFAULT>)

LEAD(EXPR,<OFFSET>,<DEFAULT>)

【功能】表示根據COL1分組，在分組內部根據 COL2排序，而這個值就表示每組內部排序後的順序編號（組內連續的唯一的） 

lead （） 下一個值 lag（） 上一個值

【參數】

EXPR是從其他行返回的運算式 

OFFSET是預設為1 的正數，表示相對行數。希望檢索的當前行分區的位移量

DEFAULT是在OFFSET表示的數目超出了分組的範圍時返回的值。

【說明】Oracle分析函數

【樣本】

-- Create table

create table LEAD_TABLE

(

 CASEID VARCHAR2(10),

 STEPID VARCHAR2(10),

 ACTIONDATE DATE

)

tablespace COLM_DATA

 pctfree 10

 initrans 1

 maxtrans 255

 storage

 (

 initial 64K

 minextents 1

 maxextents unlimited

 );

insert into LEAD_TABLE values('Case1','Step1',to_date('20070101','yyyy-mm-dd'));

insert into LEAD_TABLE values('Case1','Step2',to_date('20070102','yyyy-mm-dd'));

insert into LEAD_TABLE values('Case1','Step3',to_date('20070103','yyyy-mm-dd'));

insert into LEAD_TABLE values('Case1','Step4',to_date('20070104','yyyy-mm-dd'));

insert into LEAD_TABLE values('Case1','Step5',to_date('20070105','yyyy-mm-dd'));

insert into LEAD_TABLE values('Case1','Step4',to_date('20070106','yyyy-mm-dd'));

insert into LEAD_TABLE values('Case1','Step6',to_date('20070101','yyyy-mm-dd'));

insert into LEAD_TABLE values('Case1','Step1',to_date('20070201','yyyy-mm-dd'));

insert into LEAD_TABLE values('Case2','Step2',to_date('20070202','yyyy-mm-dd'));

insert into LEAD_TABLE values('Case2','Step3',to_date('20070203','yyyy-mm-dd'));

commit;

結果如下：

Case1 Step1 2007-1-1 Step2 2007-1-2 

Case1 Step2 2007-1-2 Step3 2007-1-3 Step1 2007-1-1

Case1 Step3 2007-1-3 Step4 2007-1-4 Step2 2007-1-2

Case1 Step4 2007-1-4 Step5 2007-1-5 Step3 2007-1-3

Case1 Step5 2007-1-5 Step4 2007-1-6 Step4 2007-1-4

Case1 Step4 2007-1-6 Step6 2007-1-7 Step5 2007-1-5

Case1 Step6 2007-1-7 Step4 2007-1-6

Case2 Step1 2007-2-1 Step2 2007-2-2 

Case2 Step2 2007-2-2 Step3 2007-2-3 Step1 2007-2-1

Case2 Step3 2007-2-3 Step2 2007-2-2

還可以進一步統計一下兩者的相差天數

select caseid,stepid,actiondate,nextactiondate,nextactiondate-actiondate datebetween from (

select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,

lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,

lag(stepid) over (partition by caseid order by actiondate) prestepid,

lag(actiondate) over (partition by caseid order by actiondate) preactiondate

from lead_table) 

結果如下：

Case1 Step1 2007-1-1 2007-1-2 1

Case1 Step2 2007-1-2 2007-1-3 1

Case1 Step3 2007-1-3 2007-1-4 1

Case1 Step4 2007-1-4 2007-1-5 1

Case1 Step5 2007-1-5 2007-1-6 1

Case1 Step4 2007-1-6 2007-1-7 1

Case1 Step6 2007-1-7 

Case2 Step1 2007-2-1 2007-2-2 1

Case2 Step2 2007-2-2 2007-2-3 1

Case2 Step3 2007-2-3 

每一條記錄都能串連到上/下一行的內容

lead （） 下一個值 lag（） 上一個值

select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid,

lead(actiondate) over (partition by caseid order by actiondate) nextactiondate,

lag(stepid) over (partition by caseid order by actiondate) prestepid,

lag(actiondate) over (partition by caseid order by actiondate) preactiondate

from lead_table