LAG() window function facilitates access to previous rows based on the offset argument. It can be particularly useful when a comparison of a previous value is necessary without the use of a self join. There is a similarity to the
LEAD() function with the difference being the accessible rows.
LEAD() accesses subsequent rows while
LAG() accesses previous rows.
LAG (expression [, offset] [, default]) OVER ( [ partition_by ] order_by )
LAG() accepts the following paramteres:
expression- The column value which will be referenced.
offset- A positive numeric indicator of the previous row to access that is relative to the current row. If not specified the default is 1.
default- The value that will be returned if the
offsetis out of range. This is an optional argument, if not specified NULL will be returned.
partition_by- Allows the result set to be grouped based on a column. This is an optional argument, if not specified the result set will be treated as a single group.
order_by- Determines the order of the result set. If
partition_byis specified, it will order the grouped data instead.
The example below uses the
LAG() function to create the new column
previous_age based on values in the previous row of the
SELECT *,LAG(age, 1) OVER (ORDER BY age ASC) AS previous_ageFROM Users;
The output is a table that features a new column
previous_age, which holds the values from the previous records. The first record is null because a default was not specified and the previous row would be out of range.
- Anonymous contributorAnonymous contributor2 total contributions
- Anonymous contributor