Row_number () over function usage)

(To) http://hi.baidu.com/122439049/blog/item/0c9c48131b2734d5f7039e13.html

Row_number () over (partition by col1 order by col2) indicates grouping by col1 and sorting by col2 within the group, the value calculated by this function indicates the sequential numbers after the sorting in each group (the continuous and unique values in the group ). The difference between rownum and rownum is that when rownum is used for sorting, a pseudo-column rownum is added to the result set before sorting, this function sorts the clause before calculating the row number. Row_number () is similar to rownum and more powerful (it can be sorted from 1 on each group ). Rank () is the Skip sorting. When there are two second names, the next is the fourth one (also in each group ). Dense_rank () L is a continuous sorting, with two second names still followed by the third. In contrast, row_number does not have repeated values. Lag (arg1, arg2, arg3 ): Arg1 is the expression returned from other rows. Arg2 is the offset of the current row partition to be retrieved. Is a positive offset, and the number of rows in the past is retrieved. Arg3 is the value returned when the number indicated by arg2 exceeds the group range. Check Several SQL statements: Statement 1: Select row_number () over (order by sale/cnt desc) As sort, sale/CNT From ( Select-60 as sale, 3 as CNT from dual Union Select 24 as sale, 6 as CNT from dual Union Select 50 as sale, 5 as CNT from dual Union Select-20 as sale, 2 as CNT from dual Union Select 40 as sale, 8 as CNT from dual ); Execution result: Sort sale/CNT -------------------- 1 10 2 5 3 4 4-10 5-20   Statement 2: Query employees' salaries, sorted by department Select ename, Sal, row_number () over (partition by deptno order by Sal DESC) as sal_order from Scott. EMP; Execution result: Ename Sal sal_order ---------------------------------------- King 5000 1 Clark 2450 2 Miller 1300 3 Scott 3000 1 Ford 3000 2 Jones 2975 3 Adams 1100 4 Smith 800 5 Blake 2850 1 Allen 1600 2 Turner 1500 3 Ward 1250 4 Martin 1250 5 James 950 6 14 rows have been selected. Statement 3: query the maximum salary of each department Select deptno, ename, Sal from (Select deptno, ename, Sal, row_number () over (partition by deptno order by Sal DESC) as sal_order From Scott. EMP) Where sal_order <2; Execution result: Deptno ename Sal ---------------------------------------- 10 King 5000 20 Scott 3000 30 Blake 2850 3 rows have been selected. Statement 4: Select deptno, Sal, rank () over (partition by deptno order by SAL) as rank_order from Scott. EMP order by deptno; Execution result: Deptno Sal rank_order ------------------------------ 10 1300 1 10 2450 2 10 5000 3 20 800 1 20 1100 2 20 2975 3 20 3000 4 20 3000 4 30 950 1 30 1250 2 30 1250 2 30 1500 4 30 1600 5 30 2850 6 14 rows have been selected. Statement 5: Select deptno, Sal, dense_rank () over (partition by deptno order by SAL) as dense_rank_order from Scott. EMP order by deptn; Execution result: Deptno Sal dense_rank_order ------------------------------------ 10 1300 1 10 2450 2 10 5000 3 20 800 1 20 1100 2 20 2975 3 20 3000 4 20 3000 4 30 950 1 30 1250 2 30 1250 2 30 1500 3 30 1600 4 30 2850 5 14 rows have been selected. Statement 6: Select deptno, ename, Sal, lag (ename, 1, null) over (partition by deptno order by ename) as lag _ from Scott. EMP order by deptno; Execution result:     Deptno ename Sal lag _ ------------------------------------------------------------ 10 Clark 2450 10 King 5000 Clark 10 Miller 1300 king 20 Adams 1100 20 Ford 3000 Adams 20 Jones 2975 Ford 20 Scott 3000 Jones 20 Smith 800 Scott 30 Allen 1600 30 Blake 2850 Allen 30 James 950 Blake 30 Martin 1250 James 30 Turner 1500 Martin 30 ward 1250 Turner 14 rows have been selected.