The Oracle analysis functions RANK (), ROW_NUMBER (), and LAG () can be used. For more information, see.
The Oracle analysis functions RANK (), ROW_NUMBER (), and LAG () can be used. For more information, see.
ROW_NUMBER () OVER (partition by COL1 order by COL2)
Indicates grouping by COL1 and sorting by COL2 within the group
This value indicates the sequential number after sorting in each group (the continuous and unique in the group)
RANK () is similar. However, RANK sorting is the same as RANK ranking. It can be followed by two first places and then 3rd
LAG indicates that after grouping and sorting, the difference between the first record and the first record in the group is subtracted. The first record can return NULL.
BTW
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 name (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.
SQL> set pagesize 100;
SQL> select rownum from emp;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
14 rows have been selected.
Used time: 00: 00: 00.10
SQL> select deptno, row_number () over (partition by deptno order by sal) from emp order by deptno;
DEPTNO ROW_NUMBER () OVER (PARTITIONBYDEPTNOORDERBYSAL)
-------------------------------------------------------
10 1
2
3
20 1
2
3
4
5
30 1
2
3
4
5
6
14 rows have been selected.
Used time: 00: 00: 00.41
SQL> select deptno, rank () over (partition by deptno order by sal) from emp order by deptno;
Deptno rank () OVER (PARTITIONBYDEPTNOORDERBYSAL)
-------------------------------------------------
10 1
2
3
20 1
2
3
4
4
30 1
2
2
4
5
6
14 rows have been selected.
Used time: 00: 00: 00.21
SQL> select deptno, dense_rank () over (partition by deptno order by sal) from emp order by deptno;
DEPTNO DENSE_RANK () OVER (PARTITIONBYDEPTNOORDERBYSAL)
-------------------------------------------------------
10 1
2
3
20 1
2
3
4
4
30 1
2
2
3
4
5
14 rows have been selected.
Used time: 00: 00: 00.20
SQL> select deptno, ename, sal, lag (ename, 1, null) over (partition by deptno order by ename) from emp ord
Er by deptno;
Deptno ename sal lag (ENAME,
----------------------------------------
10 CLARK 2450
KING 5000 CLARK
MILLER 1300 KING
20 ADAMS 1100
FORD 3000 ADAMS
JONES 2975 FORD
SCOTT 3000 JONES
SMITH 800 SCOTT
30 ALLEN 1600
BLAKE 2850 ALLEN
JAMES 950 BLAKE
MARTIN 1250 JAMES
TURNER 1500 MARTIN
WARD 1250 TURNER
14 rows have been selected.
Used time: 00: 00: 00.31
SQL> select deptno, ename, sal, lag (ename, 2, 'example ') over (partition by deptno order by ename) from em
P order by deptno;
Deptno ename sal lag (ENAME,
----------------------------------------
10 CLARK 2450 example
KING 5000 example
MILLER 1300 CLARK
20 ADAMS 1100 example
FORD 3000 example
JONES 2975 ADAMS
SCOTT 3000 FORD
SMITH 800 JONES
30 ALLEN 1600 example
BLAKE 2850 example
JAMES 950 ALLEN
MARTIN 1250
TURNER 1500 JAMES
WARD 1250 MARTIN
14 rows have been selected.
