An inter-row function that returns the value of an attribute in a previous row in the table or table partition.
LAG (value_expr) [, offset [, default]]) OVER ([PARTITION BY window partition] ORDER BY window ordering)
Parameter |
Description |
|---|---|
value_expr |
Table column or expression defining the offset data to return from the table. |
offset |
The number of rows above the current row, expressed as a non-negative exact numeric literal, or as a SQL variable with exact numeric data. The permitted range is 0 to 231. |
default |
The value to return if the offset value goes beyond the scope of the cardinality of the table or partition. |
window partition |
(Optional) One or more value expressions separated by commas indicating how you want to divide the set of result rows. |
window ordering |
Defines the expressions for sorting rows within window partitions, if specified, or within the result set if you did not specify a window partition. |
The LAG function requires an OVER (ORDER_BY) window specification. The window partitioning clause in the OVER (ORDER_BY) clause is optional. The OVER (ORDER_BY) clause must not contain a window frame ROWS/RANGE specification.
You cannot define an analytic expression in value_expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for value_expr.
You must enter a non-negative numeric data type for offset. Entering 0 returns the current row. Entering a negative number generates an error.
The default value of default is NULL. The data type of default must be implicitly convertible to the data type of the value_expr value or else SAP Sybase IQ generates a conversion error.
The following example returns salary data from the Employees table, partitions the data by department ID, and orders the data according to employee start date. The LAG function returns the salary from the previous row (a physical offset of one row) and displays it under the LAG (Salary) column:
SELECT DepartmentID dID, StartDate, Salary, LAG(Salary, 1) OVER(PARTITION BY dID ORDER BY StartDate) FROM Employees ORDER BY 1,2;
The returned result set is:
dID StartDate Salary Lag(Salary) ========= =========== ========== ============= 100 1984-08-28 45,700.000 NULL 100 1985-01-01 62,000.000 45,700.000 100 1985-06-17 57,490.000 62,000.000 100 1986-06-07 72,995.000 57,490.000 100 1986-07-01 48,023.690 72,995.000 ... 200 1985-02-03 38,500.000 NULL 200 1985-12-06 54,800.000 38,500.000 200 1987-02-19 39,300.000 54,800.000 200 1987-07-10 49,500.000 39,300.000 200 1988-10-04 54,600.000 49,500.000 200 1988-11-12 39,800.000 54,600.000 ...