[Oracle] Detailed description of the use of the lead and lag offset Functions

Oracle has two functions: Lead and lag, both of which are used to calculate the offset analysis functions. The two functions share the same usage.

The Oracle official documentation explains:

LEADIs an analytic function. It provides access to more than one row of a table at the same time without a self join.
Given a series of rows returned from a query and a position of the cursor,LEADProvides access to a row at
Given physical offset beyond that position.

The lead function is an analysis function that can simultaneously access multiple rows of data in a table without self-joining. Query the returned data rows and cursors. The lead function accesses another row of data in the table based on the offset at the current position.

Let's take a look at the specific usage of this function based on the query example.

1. The lead function first groups the data according to the deptno department and sorts the data according to hiredate. next_hiredate indicates the next offset of hiredate (the default offset is 1 ).

For example, if Allen's hiredate is "1981-2-20", Allen's next_hiredate value is "1981-2-22" (that is, Ward's hiredate value );

Similarly, if Ward's hiredate value is "1981-2-22", Ward's next_hiredate value is "1981-5-1" (that is, the hiredate value of Blake );

James cannot find the next offset, so the default value of next_hiredate is null.

-- 1. Basic usage of lead: select e. ename, E. deptno, E. hiredate, lead (E. hiredate, 1, null) over (partition by E. deptno order by E. hiredate) as next_hiredatefrom EMP ewhere E. deptno = 30;

2. The above query can also be left with partition

-- 2. Select E. ename, E. deptno, E. hiredate, lead (E. hiredate) over (order by E. hiredate) as next_hiredatefrom emp e;

3. Oracle 11g enhanced the syntax of lead and lag functions by adding {RESPECT|IGNORE}Nulls. If the lead expression result is null, the returned result is also null. See the following example.

update emp eset e.hiredate=NULLwhere e.empno='7698';commit;select e.empno,       e.ename,       e.deptno,       e.hiredate,       lead(e.hiredate,1,NULL) over(partition by e.deptno order by e.empno) as next_hiredatefrom emp ewhere e.deptno=30;

Because the hiredate value of 7698-blke is empty, the next_hiredate value of all Martin is empty. The default value is respect nulls. the result after ignore nulls is added is as follows.

select e.empno,       e.ename,       e.deptno,       e.hiredate,       lead(e.hiredate ignore nulls) over(partition by e.deptno order by e.empno) as next_hiredatefrom emp ewhere e.deptno=30;

The new ignore nulls function can ignore null results and find another result that meets the conditions.





4. The offset determines the N locations before or after the query. The default offset of the function is 1. We can also change it to a custom offset.

-- 4, offset, default value = 1, manually set to 2 select e. empno, E. ename, E. deptno, E. hiredate, lead (E. hiredate, 2, null) over (partition by E. deptno order by E. hiredate) as next_hiredatefrom EMP ewhere deptno = 30;




5. If the lead offset exceeds the table range, default returns NULL by default. You can manually set a fixed return value. The data type of this value must be the same as that of the lead expression.

-- 5. Select E. empno, E. ename, E. deptno, E. hiredate, lead (E. hiredate, 1, to_date ('1970-01-01 ', 'yyyy-mm-dd') over (partition by E. deptno order by E. hiredate) as next_hiredatefrom EMP ewhere deptno = 30;




Lag Analysis Function usage of basic and lead function type, but the opposite effect, you can in your own machine hands-on, more detailed information please refer to: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions086.htm#SQLRF00656