Oracle analysis function RANK of ROW_NUMBER of LAG of etc

ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)

Represents groups based on COL1, sorted by COL2 within the group
And this value represents the sequential number of each group (the only one in a row within a group)

RANK() is similar, except that RANK ranks the same as the first one, and can tie for the second and then the third

LAG means that after grouping sort, the difference between the last record in the group and the previous record is reduced, and the first one can return NULL

BTW: EXPERT ONE ON ONE speaking the most detailed, there are a lot of relevant features, document look more difficult

row_number() is similar to rownum, but more powerful 1 point (sort from time to time within each group)
rank() is jump sort, and when there are two 2nd's then the 4th (again within each group)
dense_rank()l is sorted consecutively, still followed by the 3rd when there are two 2nd places.
In contrast, row_number has no duplicate value
lag (arg1, arg2 arg3) :
arg1 is an expression returned from another row
arg2 is the offset of the current forward partition that you want to retrieve. Is a positive offset, and is the number of previous rows retrieved backwards.
arg3 is the value returned when the number represented by arg2 exceeds the range of the group.


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 lines 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 lines 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 lines 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 lines 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 lines have been selected.

Available 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 BLAKE
TURNER 1500 JAMES
WARD 1250 MARTIN

14 lines have been selected.