Oracle analytic function rank (), row_number (), LAG (), etc. _oracle

Row_number () over (PARTITION by COL1 ORDER by COL2)

Represents a grouping based on COL1, sorted by COL2 within a group
This value represents the sequential numbering of each group's internal sorting (a sequential unique within the group)

Rank () is similar, but rank is ranked in the same way as the rank, can be ranked 2 first place after the 3rd place

LAG indicates that after grouping, the next record in the group is less than the difference between the previous record and the first can return NULL

The most detailed, Btw:expert one on one, and a lot of related features, the document looks more laborious

Row_number () and rownum are similar, more powerful (can be sorted from 1 in each group)
Rank () is a jump sort, with two second names followed by fourth place (also within each group)
Dense_rank () L is a sequential sort, with two second names still followed by third.
In contrast, Row_number has no duplicate values.
Lag (ARG1,ARG2,ARG3):
Arg1 is an expression returned from another row
Arg2 is the offset of the current row partition that you want to retrieve. is a positive offset, which retrieves the number of previous rows backwards.
Arg3 is the value that is returned when the number of ARG2 represents exceeds the range of the grouping.


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.

Time used: 00:00:00.10
Sql> Select Deptno,row_number () over (partition by Deptno order by Sal) from the 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.

Time used: 00:00:00.41
Sql> Select Deptno,rank () over (partition by Deptno order by Sal) from the 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.

Time used: 00:00:00.21
Sql> Select Deptno,dense_rank () over (partition by Deptno order by Sal) from the 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.

Time used: 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,
---------- ---------- ---------- ----------
Ten CLARK 2450
KING 5000 CLARK
MILLER 1300 KING

ADAMS 1100
FORD 3000 ADAMS
JONES 2975 FORD
SCOTT 3000 JONES.
SMITH SCOTT

ALLEN 1600
BLAKE 2850 ALLEN
JAMES 950 BLAKE
MARTIN 1250 JAMES
TURNER 1500 MARTIN
WARD 1250 TURNER


14 rows have been selected.

Time used: 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,
---------- ---------- ---------- ----------
Ten CLARK 2450 Example
KING 5000 Example
MILLER 1300 CLARK

ADAMS 1100 Example
FORD 3000 Example
JONES 2975 ADAMS
SCOTT 3000 FORD
SMITH JONES

ALLEN 1600 Example
BLAKE 2850 Example
JAMES 950 ALLEN
MARTIN 1250 BLAKE
TURNER 1500 JAMES
WARD 1250 MARTIN

14 rows have been selected.