Introduction to Oracle lag and lead analysis functions ____ static functions

SOURCE Link Click here

Lag and the lead function are two analytic functions related to offsets, through which we can take the values of the current row and column offset n rows and columns lag can be seen as positive upward offset the lead can be considered a negative downward offset specifically let's look at a few examples: Let's take a look at two columns of Scott's EMP table: Select Deptno, Sal from Scott.emp order by Deptno Deptno Sal 10 2450.00 10 5000.00 10 1300.00 20 297 5.00 3000.00 1100.00 800.00 3000.00 1250.00 1500.00 A-1600.00-a-950.00 ok so now, for example, I have One such demand (we only look at Sal) I want to ask you what the last value of 2450 is. The answer is no, what is the last value of 5000? is: What is the last value of 2450 1300? Yes: 5000 OK and so on I want to get the previous value of the current value just like: 2450      xxx (XXX for null)--This value is the previous value of the previous column       5000.00 2450 & nbsp;     1300.00 5000       2975.00 1300       3000.00 2975        1100.00 3000            ...       1250.00   2850 OK that's what we're going to write with SQL now. Yes, you guessed right. Use the LAG analysis function: Select Deptno, Sal A, lag (Sal, 1, Sal) b over (order by Deptno)   from Scott.emp a B 10 2450.00 2450--ps: The reason this is 2450 is because lag (SAL, 1, sal) I let it give him a value of 10 50.00.00 2450 10 1300.00 5000 20 2975.00 1300 20 3000.00 2975 20 1100.00 3000 20 800.00 1100 20 3000.00 800 30 1250.00 3000-3 0 1500.00 1250 30 1600.00 1500 30 950.00 1600 30 2850.00 950 30 1250.00 2850 Yes it's that simple you see what's the connection between column A and column B? Relative a column B column is her last value about the lead she Just as opposed to lag. Select Deptno, Sal A, lead (SAL, 1, Sal) over (order by Deptno) b   from scott.emp Deptno a B 10 2450.0 0 5000 10 5000.00 1300 10 1300.00 2975 20 2975.00 3000 20 3000.00 1100 20 1100.00 800 20 800.00 3000 20 3000.00 1250 30-12 50.00 1500 30 1500.00 1600 30 1600.00 950 30 950.00 2850 30 2850.00 1250 30 1250.00 1250 relative a column B column is her next value the other offset value 1 can be arbitrarily taken if it is 2 That's the offset of two values. Select Deptno, Sal A, lag (Sal, 2,null) over (order by Deptno) b   from scott.emp Deptno a B 10 2450.00     -Note that this is null 5000.00 1300.00 2450  --a Column 1300 of the above two values are how much. 2450 Yes. 20 2975.00 5000 20 3000.00 1300 20 1100.00 2975 20 800.00 3000 20 3000.00 1100 30 1250.00 800 30 1500.00 3000 30 16 00.00 1250 950.00 1500-2850.00 1600 1250.00 950 OK that itsReal Lag,lead can also be combined with group offset select Deptno,        Sal A,        Lag (SAL, 1, NULL) over (PA Rtition by Deptno order by Deptno) b   from scott.emp deptno A b 10 2450.00 10 5000.00 2450 10 1300.00 5000 20 2975.00 20 3000.00 2975 20 1100.00 3000 20 800.00 1100 20 3000.00 800 30 1250.00 30 1500.00 1250 30 1600.00 1500 30-95 0.00 1600 30 2850.00 950 30 1250.00 2850 Note deptno The critical value between different groups you see, you got it.