LAG
This section contains reference documentation for the LAG function.
Returns the value from a preceding row in the same result set, based on a specified physical offset. It can be used to compare values in the current row with values in a previous row.
Signature
Arguments
expression: The column or calculation from which the value is to be returned.
offset: The number of rows before the current row from which to retrieve the value. The default is 1 if not specified.
default: The value to return if the offset goes beyond the scope of the window. If not specified, NULL is returned.
Example
This example calculates the difference in sales between the current day and the previous day.
Retrieve the previous payment amount for comparison.
Identify trends by comparing current data with historical data.
Calculate the difference in sales between the current day and the previous day This example shows how to use the LAG function to find the sales difference between consecutive days.
Output:
2023-02-14
200
NULL
NULL
2023-02-15
180
200
-20
2023-02-16
220
180
40
Retrieve the previous payment amount for comparison This query retrieves the last payment amount for each payment to see if the amount is increasing or decreasing.
Output:
2023-02-14 21:21:59.996577
2.99
NULL
2023-02-14 21:23:39.996577
4.99
2.99
2023-02-14 21:29:00.996577
4.99
4.99
Identify trends by comparing current data with historical data Use the LAG function to compare the current month's data with the same month from the previous year to identify trends or significant changes.
Output:
1
2023
150
NULL
1
2024
170
150
Use with CTE: