"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
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
就是說lag將結果集的某個欄位進行偏移（offset)，需要指定要偏移的欄位，
偏移行數，預設值。注意格式是lag(欄位，偏移行數，預設值 ) over(partition by xx order by xx)。
[@more@]