[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.

[SQL] view plain copy--1, lead basic usage Select E.ename, E.deptno, E.hiredate, lead (E.hi   Redate,1,null) over (partition by E.deptno ORDER by E.hiredate) as next_hiredate from EMP e where e.deptno=30; [SQL] view plain copy

2, the above query can also not add partition by

[SQL] view plain copy--2, a lead function that does not contain partition by SELECT E.ename, E.deptno, E.hiredate, Lead (E.hiredate) over (order by 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.

[SQL] view plain copy 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.

[SQL] view plain copy