IT Ninja Turtles-A Review of oracle grouping Methods

IT Ninja Turtles-A Review of oracle grouping Methods

Oracle analysis functions are very powerful. We only need to master these methods. A more direct saying is that knowing the functions of these analysis functions can accomplish a lot of work.

The following describes these functions and simple applications.

Here, I want to explain to the lag () and lead () functions that lag () itself is delayed, that is, the number of columns is delayed, and lead () this means that a certain column of data is displayed several rows in advance.

RANK () dense_rank () [syntax] RANK () OVER ([query_partition_clause] order_by_clause) dense_RANK () OVER ([query_partition_clause] order_by_clause) [function] the Aggregate functions RANK and dense_rank are mainly used to calculate the sorting values in a group of values. [Parameter] the usage of dense_rank is the same as that of rank (). [difference] the parallel relationship between dence_rank is that the correlation level is not skipped. Rank skips rank () for Skip sorting. When there are two second names, dense_rank () l is a continuous sorting, when there are two second names, they still follow the third name. [Description] Oracle analysis function [Example] the Aggregate functions RANK and dense_rank are mainly used to calculate the sorting values in a group of values. Before 9i, analytic is only available, that is, the sort value of each row is calculated from a query result based on the value_exprs field specified in the order_by_clause. The syntax is RANK () OVER ([query_partition_clause] order_by_clause). In 9i, the aggregate function (aggregate) is added ), calculates the value of a given parameter value in a set sort query. These parameters must be constant or constant expressions, and must be exactly the same as the number, position, and type of fields in the order by clause. Syntax: RANK (expr [, expr]...) within group (order by expr [DESC | ASC] [NULLS {FIRST | LAST}] [, expr [DESC | ASC] [NULLS {FIRST | LAST}]...) example 1: A Table contains the following content: COL1 COL2 1 1 2 1 3 2 3 1 4 1 4 2 5 2 5 2 6 2 analysis function: List Col2 groups and sort them by Col1, and generate numeric columns. It is more useful to find the information of the first few subjects in the tables Table. SELECT. *, RANK () OVER (partition by col2 order by col1) "Rank" FROM table a; the result is as follows: COL1 COL2 Rank 1 1 1 2 2 2 3 3 3 4 1 4 3 2 1 4 2 2 2 2 3 2 3 6 2 5 Example 2: TABLE: A (subject, scores) mathematics, 80 Chinese, 70 mathematics, 90 mathematics, 60 mathematics, 100 Chinese, 88 Chinese, 65 Chinese, 77 the result I want now is: (I want the first three scores of each subject) mathematics, 100 mathematics, 90 mathematics, 80 Chinese, 88 Chinese, 77 Chinese, 70 then the statement is written as follows: select * from (select rank () over (partition by subject order by score desc) rk,. * from a) t where t. rk <= 3; Example 3: Total function: Calculate the order value () in Orade By Col1, Col2, that is, col1 = 4, col2 = 1 in the position after sorting select rank) within group (order by col1, col2) "Rank" FROM table; the result is as follows: Rank 4 dense_rank is equivalent to rank (), but there is a difference: dence_rank is in the parallel relationship, the related level is not skipped. Rank is skipped. 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 99 2 B yao cai 4 If dense_rank when: 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 () [syntax] ROW_NUMBER () OVER (partition by COL1 order by COL2) [function] indicates grouping BY COL1 and sorting BY COL2 within the group, this value indicates the order number (the continuous and unique in the group) after sorting in each group. row_number () mainly returns "row" information, there is no [parameter] [description] main function of Oracle analysis function: used to take the first few or the last few. [Example] The table content is 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 search result 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 seqno) idfrom table_name) where id <= 3;

Lag () and lead () [syntax] lag (EXPR, <OFFSET>, <DEFAULT>) LEAD (EXPR, <OFFSET>, <DEFAULT>) [function] indicates to group by COL1 and sort by COL2 within the Group. This value indicates the sequential number (consecutive and unique in the group) after sorting in each group. lead () next value lag () the previous value [parameter] EXPR is the expression OFFSET returned from other rows is the positive number of 1 by default, indicating the relative number of rows. The default offset of the current row partition to be retrieved is the value returned when the number indicated by OFFSET exceeds the group range. [Description] Oracle analysis function [Example] -- 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 64 K minextents 1 maxextents unlimited); insert into LEAD_TABLE values ('case1 ', 'step1', to_date ('2016 ', 'yyyy-mm-dd'); insert into LEAD_TABLE values ('case1 ', 'step2', to_date ('20170101', 'yyyy-mm-dd ')); insert into LEAD_TABLE values ('case1 ', 'step3', to_date ('20170101', 'yyyy-mm-dd'); insert into LEAD_TABLE values ('case1 ', 'step4', to_date ('20170101', 'yyyy-mm-dd'); insert into LEAD_TABLE values ('case1 ', 'step5', to_date ('20170101 ', 'yyyy-mm-dd'); insert into LEAD_TABLE values ('case1 ', 'step4', to_date ('2013', 'yyyy-mm-dd ')); insert into LEAD_TABLE values ('case1 ', 'step6', to_date ('20170101', 'yyyy-mm-dd'); insert into LEAD_TABLE values ('case1 ', 'step1', to_date ('20170101', 'yyyy-mm-dd'); insert into LEAD_TABLE values ('case2', 'step2', to_date ('20170101 ', 'yyyy-mm-dd'); insert into LEAD_TABLE values ('case2', 'step3', to_date ('20170101', 'yyyy-mm-dd ')); the result is as follows: case1 Step 1 step 2 Step 1 step 1 step 1 step 1 step 1 step 1 step 1 step 1 step 1 step 3 step 3 step 3 step 4 step 2 Step 1 step 1 step 4 step 1 step 5 step 3 step 3 step 1 step 5 step 4 step 4 step 4 step 1 step 4 step 4 step 6 step 5 case1 Step6 Step4 2007-1-6 Case2 Step1 2007-2-1 Step2 2007-2-2 Case2 Step2 2007-2-2 Step3 2007-2-2-step1 2007-2-1 Case2 Step3 2007-2-2 Step2 then you can further calculate the number of days of difference between the two, 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) the result of preactiondate from lead_table is as follows: case1 Step1 2007-1-1 2007-1-2 1 Case1 Step2 2007-1-2 2007-1-3 1 Case1 Step3 2007-1-3 then 1 Case1 Step4 then 1_1 Case1 Step5 then 1_1 Case1 Step4 then 1_1 Case1 Step6 then Case2 Step1 then 1 Case2 Step2 2007-2-2 2007-2-3 1 Case2 Step3 2007-2-3 each record can be connected to the content lead () of the Upper/lower line () 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