LAG

Purpose

This function computes the result of expr on the row that is precisely offset rows prior to the current row in the partition.

Usage Notes

• LAG is only available as an analytic function (i.e., in combination with an over_clause). For more information, refer to Analytic Functions section.
• The over_clause has to contain an order_clause and must not contain a window_frame_clause.
• If the order_clause does not define an unique sort order, the result is non-deterministic.
• The offset parameter has to be a numeric value greater than or equal to 0. Without offset, Exasol uses the default value 1.
• If there are less than offset number of rows before the current row in the partition, LAG returns the default value for the row. Without specified default, Exasol uses NULL.
• If the function contains IGNORE NULLS, Exasol skips rows with NULL during the search for the previous row in the partition. It does not skip any rows with RESPECT NULLS. If the function contains neither IGNORE NULLS nor RESPECT NULLS the default is RESPECT NULLS. The use of IGNORE NULLS is computationally more expensive than RESPECT NULLS.
• To access following rows you can use the function LEAD.

Example

Analytic Functions

SELECT 
    id, department, hire_date, 
    LAG(id, 1) OVER (PARTITION BY department ORDER BY hire_date) LAG 
FROM employee_table ORDER BY department, hire_date;

Result