LAG and LEAD Analytic Functions

“如果你可以使用一句SQL解决的需求，就使用一句SQL；如果不可以，就考虑PL/SQL是否可以；如果PL/SQL实现不了，就考虑Java存储过程是否可以；如果这些都不可能实现，那么就需要考虑你是否真的需要实现这个需求。”

LAG and LEAD Analytic Functions

The LAG and LEAD analytic functions were introduced in 8.1.6 to give access to multiple rows within a table, without the need for a self-join.(不需要表的自连接),

1 LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
2 LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

1、value_expression - Can be a column or a built-in function, except for other analytic functions.

2、offset - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.

3、default - The value returned if the offset is outside the scope of the window. The default value is NULL.
(默认值) 例如:LAG(salary, 1, 0)

LAG

The LAG function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.

1 SELECT EMPNO,
2        ENAME,
3        JOB,
4        SAL,
5        LAG(SAL, 1, 0) OVER(ORDER BY SAL) AS SAL_PREV,
6        SAL - LAG(SAL, 1, 0) OVER(ORDER BY SAL) AS SAL_DIFF
7   FROM EMP;

LEAD

The LEAD function is used to return data from the next row. The following query returns the salary from the next row to calulate the difference between the salary of the current row and the following row.

1 SELECT EMPNO,
2        ENAME,
3        JOB,
4        SAL,
5        LEAD(SAL, 1, 0) OVER(ORDER BY SAL) AS SAL_NEXT,
6        LEAD(SAL, 1, 0) OVER(ORDER BY SAL) - SAL AS SAL_DIFF
7   FROM EMP;