Oracle Analytics function Lag lead

LAGis an analytic function. IT provides access to more than one row of a table in the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a Given physi Cal offset prior to that position.

offsetfor the optional argument, specify a integer is greater than zero. If you don't specify, then its offset default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you don't specify, then its default default is null.

{ RESPECT | IGNORE } NULLS Determines whether null values of is value_expr included in or eliminated from the calculation. The default is RESPECT NULLS .

You cannot nest analytic functions by using the or any other LAG analytic function for value_expr . However, you can use the other built-in function expressions for value_expr .

For example, there is a requirement, sorted by employee number. Shows how much of the previous SAL is for each employee's Sal.

SELECT Empno,ename, Sal, LAG (sal,1,sal) over (ORDER by empno) as LaStone from EMP

Results

1 7369 SMITH 800.00 800
27499allen1600.00800
37521ward1250.001600
47566jones2975.001250
57654martin1250.002975
67698blake2850.001250
77782clark2450.002850
87788scott3000.002450
97839king5000.003000
107844turner1500.005000
117876adams1100.001500
127900james950.001100
137902ford3000.00950
147934miller1300.003000

The lead is the next, such as lag

Oracle Analytics function Lag lead