오라클 LEAD, LAG 함수 사용 (게시판 이전글, 다음글 구현시 사용)

LEAD

Syntax

Description of the illustration lead.gif

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions, including valid forms of value_expr

Purpose

LEAD is 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, LEAD provides access to a row at a given physical offset beyond that position.

If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the table. If you do not specify default, then its default value is null.

You cannot use LEAD or any other analytic function for value_expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for value_expr.

See Also:

"About SQL Expressions" for information on valid forms of expr and LAG

Examples

The following example provides, for each employee in the employees table, the hire date of the employee hired just after:

SELECT last_name, hire_date, 

   LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired" 

   FROM employees WHERE department_id = 30;

LAST_NAME                 HIRE_DATE NextHired

------------------------- --------- ---------

Raphaely                  07-DEC-94 18-MAY-95

Khoo                      18-MAY-95 24-JUL-97

Tobias                    24-JUL-97 24-DEC-97

Baida                     24-DEC-97 15-NOV-98

Himuro                    15-NOV-98 10-AUG-99

Colmenares                10-AUG-99

 

 

LAG

Syntax

Description of the illustration lag.gif

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions, including valid forms of value_expr

Purpose

LAG is 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, LAG provides access to a row at a given physical offset prior to that position.

If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default is null.

You cannot use LAG or any other analytic function for value_expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for value_expr.

See Also:

"About SQL Expressions" for information on valid forms of expr and LEAD

Examples

The following example provides, for each salesperson in the employees table, the salary of the employee hired just before:

SELECT last_name, hire_date, salary,

   LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal

   FROM employees

   WHERE job_id = 'PU_CLERK';

LAST_NAME                 HIRE_DATE     SALARY   PREV_SAL

------------------------- --------- ---------- ----------

Khoo                      18-MAY-95       3100          0

Tobias                    24-JUL-97       2800       3100

Baida                     24-DEC-97       2900       2800

Himuro                    15-NOV-98       2600       2900

Colmenares                10-AUG-99       2500       2600