Introduction to Oracle lag and lead analysis functions

The lag and the lead function are two analytic functions that are related to the offset, through which we can take the value of the offset n rows of the current row and column, and the lag can be looked at as positive upward offset the lead can be considered a negative downward offset specifically we look at a few examples:

Let's look at two columns of Scott's EMP table data: Select Deptno, Sal from Scott.emp order by DeptnodeptnoSAL102450.00105000.00101300.00202975.00203000.00201100.0020800.00203000.00301250.00301500.00301600.0030950.00302850.00301250.00OK then now, for example, I have this requirement (we only look at Sal column) I want to ask you what is the last value of 2450? The answer is no, what's the last value of 5000? is: What is the previous value of 2450 1300? Yes: OK and so I want to get the previous value of the current value just like: 2450 xxx (XXX stands for null)-This value is the previous value of the previous column 5000.0024501300.00 the2975.0013003000.0029751100.00 the...       ... 1250.00 2850OK So how do we write with SQL now? Yes, you're right. The LAG analysis function: Select Deptno, Sal A, lag (Sal, 1, Sal) b over (order by Deptno) from Scott.empdeptnoA B 102450.00 2450--ps: The reason here is 2450 because of lag (Sal, 1, sal) I let it give him a value .105000.00 2450 101300.00 202975.00 1300 203000.00 2975 201100.00 20800.00 1100 203000.00 301250.00 301500.00 1250 301600.00 30950.00 302850.00 950 301250.00 2850 Yes, it's just that simple. Do you see any connection between column A and column B? Relative to column B is her last value about lead she's just the opposite of the lag. Select Deptno, Sal A, lead (SAL, 1, Sal)-Over (order by Deptno) b fro M Scott.empdeptnoA B 102450.00 105000.00 1300 101300.00 2975 202975.00 203000.00 1100 201100.00 20800.00 203000.00 1250 301250.00 301500.00 301600.00 950 30950.00 2850 302850.00 1250 301250.00 1250 Relative a column B column is her next value and the offset value 1 is random, if it's 2, that's the offset of two values. Select Deptno, Sal A, lag (Sal, 2,null) over (order by Deptno) b from SCOTT.EMPDEP TNOA B 102450.00 --note that this is null and empty. 105000.0010What is the top two value of 1300.00 2450--a column 1300? 2450, right? 202975.00 203000.00 1300 201100.00 2975 20800.00 203000.00 1100 301250.00 301500.00 301600.00 1250 30950.00 302850.00 301250.00 950 OK that actually lag,lead can also add a packet offset of select Deptno, Sal A, lag (SAL, 1, null) over (partition by DeptnoORDER by Deptno) b from Scott.empdeptnoA B Ten2450.00105000.00 2450 101300.00 -2975.00203000.00 2975 201100.00 20800.00 1100 203000.00 -1250.00301500.00 1250 301600.00 30950.00 302850.00 950 301250.00 2850 Notice the deptno of the different groupings. You see that?

Introduction to Oracle lag and lead analysis functions