[Oracle] Lead and lag offset functions use the detailed

Oracle has two functions: lead and lag, which are the analytic functions used to compute offsets, and the two functions are the same in use.

The Oracle official documentation explains:

The lead is a 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, leads provides access to a row at a Given physic Al offset beyond that position.

The lead function is an analytic function that can access multiple rows of data from one table at a time without a connection. A series of data rows and cursors returned by a query that access another row of data in the table based on the offset at the current position.

Let's look at the specific usage of this function in conjunction with the query example.

1. The lead function is first grouped according to the DEPTNO department and sorted by HireDate, Next_hiredate represents the next offset of hiredate (offset by default of 1).

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

Similarly, Ward's hiredate value is "1981-2-22", then Ward's next_hiredate value is "1981-5-1" (i.e. Blake's hiredate value);

Finally James cannot find the next offset, so the default value for Next_hiredate is null.

--1, lead basic usage
Select E.ename,
       e.deptno,
       e.hiredate, lead
       (e.hiredate,1,null) over (partition by E.deptno ORDER by E.hiredate) as Next_hiredate from
emp e
where e.deptno=30;

2, the above query can also not add partition by

--2, lead function without partition by
SELECT e.ename,
       E.deptno,
       e.hiredate, leads
       (e.hiredate) over E.hiredate) as next_hiredate from
EMP e;

3. Oracle 11g enhanced lead and lag function syntax, added {respect | IGNORE} NULLS, if the lead's expression result is empty, the result returned is NULL, see the example below.

Update emp e
set e.hiredate=null
where 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_hiredate from
emp e
where e.deptno=30;

Because the value of 7698-blake's HireDate is empty, the value of all Martin's next_hiredate is also empty, the system defaults to respect NULLS, and the result of adding ignore NULLS is as follows.

Select E.empno,
       e.ename,
       E.deptno,
       e.hiredate, leads (E.hiredate ignore
       ) over (nulls by E.deptno ORDER by E.empno) as Next_hiredate from
emp e
where e.deptno=30;

The new Ignore Nulls feature allows you to ignore null results and find another result that satisfies the criteria.

4, the offset determines the query when the forward or after the cheap n position, the default offset of the function is 1, we can also modify the custom offset.

--4, offset offset, default = 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_hiredate from
emp e
where deptno=30;

5, the offset of the lead if the table is out of range, default returns NULL, and we can manually set a fixed return value that must be the same data type as the lead expression type.

--5, the default parameter of the lead function
Select E.empno,
       e.ename,
       E.deptno,
       e.hiredate, lead
       (e.hiredate,1,to_ Date (' 2013-01-01 ', ' Yyyy-mm-dd ') over (partition by E.deptno ORDER by E.hiredate) as Next_hiredate from
EMP e
where deptno=30;

Lag analysis functions and the use of basic and lead function type, but the opposite, we can in their own machine to practice, more detailed information please refer to: http://docs.oracle.com/cd/E11882_01/server.112/ e26088/functions086.htm#sqlrf00656