It Ninja Turtles Oracle's method review of sorting within a group

Oracle analytic functions are very powerful, and as long as we have these methods in hand, it is more straightforward to know that these analytic functions can do a lot of work.

These functions are posted below, as well as simple applications.

Where I want to sit down for the lag () and lead () functions: lag () itself means delaying the number of columns, while the lead () leads, leads, means that a column of data is displayed in a few lines ahead

Rank () Dense_rank () "Syntax" Rank () over ([Query_partition_clause] order_by_clause) Dense_rank () over ([Query_partition_cla Use] order_by_clause) "function" aggregate function rank and dense_rank The main function is to calculate the sort values in a set of values. The "parameter" Dense_rank is equivalent to rank () usage, and the "difference" dence_rank in the side-by-side relationship is that the correlation level is not skipped. Rank skips rank () is a jump sort, with two second names followed by fourth (also within each grouping) Dense_rank () L is sequential, with two second in the third place.　　　　Description Oracle Analytic function sample aggregate function rank and Dense_rank The primary function is to calculate the sorted values in a set of values.　　　　Prior to the 9i release, only the analysis function (analytic), which calculates the sorted value of each row from one query result, is based on the Value_exprs specified field in the Order_by_clause clause. The syntax is: RANK () over ([Query_partition_clause] order_by_clause) adds a new aggregate function (aggregate) to the 9i version, which calculates the sorted value of a given parameter value in a set sort query.　　　　These parameters must be constant or literal expressions and must exactly match the number, location, and type of fields in the ORDER BY clause. Its syntax is: RANK (expr [, expr] ...) Within GROUP (ORDER by expr [DESC | ASC] [NULLS {First | Last}] [, expr [DESC | ASC] [NULLS {First |　　　　Last}]] ...) Example 1: Tables table contents are as follows COL1 COL2 1 1 2 1 3 2 3 1 4 1 4 2 5 2 5 2 6 2 analysis function: Lists Col2 grouped by Col1 sort, and generates a numeric column.　　　　The comparison is used to find out the first few information of each section in the score table. SELECT A.*,rank () over (PARTITION by Col2 ORDER by col1) ' Rank ' from Table A; 　　　　The results are as follows: 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 Example 2:table:a (subjects, fractions) mathematics, 80 Chinese, 70 maths, 90 maths, 60 maths, 100 languages, 88 languages, 65 languages, 77 now I want the result is: (that is, want to score the top 3 of each subject) math , 100 mathematics, 90 maths, 80 languages, 88 languages, 77 languages, 70 so the statement is written as follows: SELECT * FROM (select rank () over (partition by account ORDER BY score Desc　　　　) rk,a.* from a) t where t.rk<=3; Example 3: Total function: Calculates the sorted value (4,1) under Orade by Col1,col2, which is the position of col1=4,col2=1 after sorting SELECT RANK (4,3) within GROUP (ORDER by col1　　　　, col2) "Rank" from table; The results are as follows: Rank 4 Dense_rank with rank (), but there is one difference: Dence_rank in a tie relationship, the correlation level does not skip. 　　　　　Rank skips for example: Table A b C a Liu Wang a Jin shu a cai kai B yang du b Lin Ying b Yao Cai b Yang 99 for example: When rank is: 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 2 b Yao Cai 4 and if 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 2 b yao Cai 3

Row_number () "Syntax" Row_number () over (PARTITION by COL1 Order by COL2) "function" means that according to COL1 grouping, according to COL2 sort within the grouping, This value represents the sequential number of each set of internally ordered (consecutive unique within the group) Row_number () returns the main "line" information, and does not have the rank "parameter" "description" the main function of the Oracle analysis function: To take the first few, or the last few "sample" table content as follows: 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 I want to have an SQL statement, the result of the search is a | 1 | TestA | 2 | Testb | 1 | Testb | 2 | TESTC | 1 | TESTC | 2 | Test implementation: Select Name,seqno,description from (select Name,seqno,description,row_number () over (partition by name order by SE QNO) Idfrom table_name) where id<=3;
Lag () and lead () "Syntax" Lag (expr,<offset>,<default>) lead (expr,<offset>,<default>) "function" Represents the sort based on COL2 within the grouping according to the COL1 grouping, and this value represents the sequential number within each group (consecutive unique within the group) lead () The next value lag () the previous value "parameter" expr is an expression returned from another row offset is a positive number that is defaulted to 1. Represents the relative number of rows. The offset of the current row partition that you want to retrieve is the default value that is returned when offset represents a number that is outside the range of the grouping. "description" Example of Oracle Analysis function--Create tablecreate table lead_table (Caseid VARCHAR2 (Ten), StepID VARCHAR2 (), Actiondate DATE) Tablespace colm_data pctfree Initrans 1 Maxtrans 255 storage (initial 64K minextents 1 maxextents Unlimited); Insert I Nto 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 ') and insert into lead_table values (' Case2 ', ' Step3 ', to_date (' 20070203 ', ' yyyy-mm-dd '); commit; The results are as follows: 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-4cas E1 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 case 2 Step2 2007-2-2 Step3 2007-2-3 Step1 2007-2-1case2 Step3 2007-2-3 Step2 2007-2-2 You can also take a closer look at the difference between the two days select Caseid,stepid,ac Tiondate,nextactiondate,nextactiondate-actiondate datebetween from (select Caseid,stepid,actiondate,lead (stepid) Over (partition by Caseid ORDER by Actiondate) Nextstepid,lead (actiondate) over (partitionby Caseid Order by Actiondate) Nextactiondate,lag (stepid) through (partition by Caseid ORDER by Actiondate) Prestepid,lag (act iondate) over (partition by Caseid ORDER by Actiondate) Preactiondatefrom lead_table) The results are as follows: Case1 Step1 2007-1-1 2007-1-2 1  Case1 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 Each record can be connected to the contents of the top/Next line lead () Next value lag () previous value Select Caseid,stepid,actiondate,lead (stepid) over (partition by C Aseid ORDER by Actiondate) Nextstepid,lead (actiondate) through (partition by Caseid ORDER by Actiondate) Nextactiondate,lag (s Tepid) over (partition by Caseid ORDER by Actiondate) Prestepid,lag (actiondate) through (partition by Caseid ORDER by Actiond ATE) Preactiondatefrom lead_table

It Ninja Turtles Oracle's method review of sorting within a group