Oracle LAG and LEAD analysis functions

The Lag and Lead functions can retrieve the data of the first N rows and the value of the last N rows of the same field in a query. This operation can be achieved through table join for the same table, but LAG and LEAD are more efficient.

The lag syntax is as follows:

The lead syntax is as follows:

The syntax of lead and lag is similar to that of lag!
Lag (exp_str, offset, defval) over ()
Exp_str is the field to be compared
Offset is the offset of the exp_str field. For example, if offset is 2, compare the first row of exp_str with the third row, the second row and the fourth row, and so on. The default value of offset is 1!
Defval is the value returned when no value is available for this function. The usage of the Lead function is similar.
The following are examples of lag and lead.
SCOTT @ www.bkjia.com> set pagesize 10000
SCOTT @ www.bkjia.com> select ename, job, sal, lag (sal) over (order by sal) last_sal from emp;
Ename job sal LAST_SAL
---------------------------------------
Smith clerk 800 -- if the default value is not set at this time, the value is null.
James clerk 950 800
Adams clerk 1100 950
Ward salesman 1250 1100
Martin salesman 1250 1250
Miller clerk 1300 1250
Turner salesman 1500 1300
Allen salesman 1600 1500
Clark manager 2450 1600
Blake manager 2850 2450
Jones manager 2975 2850
Scott analyst 3000 2975
Ford analyst 3000 3000
King president 5000 3000
14 rows selected.
The value corresponding to the first row is 500 after the default value is set.
SCOTT @ www.bkjia.com> select ename, job, sal, lag (sal, 1,500) over (order by sal) last_sal from emp;
Ename job sal LAST_SAL
---------------------------------------
Smith clerk 800 500
James clerk 950 800
Adams clerk 1100 950
Ward salesman 1250 1100
Martin salesman 1250 1250
Miller clerk 1300 1250
Turner salesman 1500 1300
Allen salesman 1600 1500
Clark manager 2450 1600
Blake manager 2850 2450
Jones manager 2975 2850
Scott analyst 3000 2975
Ford analyst 3000 3000
King president 5000 3000
14 rows selected.
When the offset value is 2
SCOTT @ www.bkjia.com> select ename, job, sal, lag (sal, 2) over (order by sal) last_sal from emp;
Ename job sal LAST_SAL
---------------------------------------
Smith clerk 800
James clerk 950
Adams clerk 1100 800
Ward salesman 1250 950
Martin salesman 1250 1100
Miller clerk 1300 1250
Turner salesman 1500 1250
Allen salesman 1600 1300
Clark manager 2450 1500
Blake manager 2850 1600
Jones manager 2975 2450
Scott analyst 3000 2850
Ford analyst 3000 2975
King president 5000 3000
14 rows selected.
The offset value is 3.
SCOTT @ www.bkjia.com> select ename, job, sal, lag (sal, 3) over (order by sal) last_sal from emp;
Ename job sal LAST_SAL
---------------------------------------
Smith clerk 800
James clerk 950
Adams clerk 1100
Ward salesman 1250 800
Martin salesman 1250 950
Miller clerk 1300 1100
Turner salesman 1500 1250
Allen salesman 1600 1250
Clark manager 2450 1300
Blake manager 2850 1500
Jones manager 2975 1600
Scott analyst 3000 2450
Ford analyst 3000 2850
King president 5000 2975
14 rows selected.

1 2 Next Page