IT忍者神龜之oracle 在分組內排序的方法回顧，忍者神龜oracle

IT忍者神龜之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 | descriptionA | 1 | testA | 2 | testA | 3 | testA | 4 | testB | 1 | testB | 2 | testB | 3 | testB | 4 | testC | 1 | testC | 2 | testC | 3 | testC | 4 | test我想有一個sql語句，搜尋的結果是 A | 1 | testA | 2 | testB | 1 | testB | 2 | testC | 1 | testC | 2 | test實現: select name,seqno,description from(select name,seqno,description,row_number() over (partition by name order by seqno) idfrom 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 tablecreate 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-1Case1 Step3 2007-1-3 Step4 2007-1-4 Step2 2007-1-2Case1 Step4 2007-1-4 Step5 2007-1-5 Step3 2007-1-3Case1 Step5 2007-1-5 Step4 2007-1-6 Step4 2007-1-4Case1 Step4 2007-1-6 Step6 2007-1-7 Step5 2007-1-5Case1 Step6 2007-1-7 Step4 2007-1-6Case2 Step1 2007-2-1 Step2 2007-2-2 Case2 Step2 2007-2-2 Step3 2007-2-3 Step1 2007-2-1Case2 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) preactiondatefrom lead_table) 結果如下：Case1 Step1 2007-1-1 2007-1-2 1Case1 Step2 2007-1-2 2007-1-3 1Case1 Step3 2007-1-3 2007-1-4 1Case1 Step4 2007-1-4 2007-1-5 1Case1 Step5 2007-1-5 2007-1-6 1Case1 Step4 2007-1-6 2007-1-7 1Case1 Step6 2007-1-7 Case2 Step1 2007-2-1 2007-2-2 1Case2 Step2 2007-2-2 2007-2-3 1Case2 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) preactiondatefrom lead_table

oracle資料怎分組排序提取？

select a.(座標),b.(座標) from 表名 a,表名 b

其實就是一個笛卡兒積
 
ORACLE分組排序查詢

row_number(),rank()是分析函數,是實現這種需求最高效的方法
你需要大表的排序,速度肯定不會快到哪裡去
建議你對需要排序的欄位先建立一個索引,會快很多很多
發放依然還是用row_number(),這個比較好