In advanced mode, the LAG function returns data from a preceding row in an Expression transformation. Use this function to compare values in the current row with values in a preceding row.
To use the LAG function, you must configure partition and order keys as window properties and in the Expression transformation.
Syntax
LAG ( column_name, offset, default )
Argument
Required/
Optional
Description
column_name
Required
The target column or expression that the function operates on.
offset
Required
Integer data type. The number of rows before the current row from which to obtain a value.
default
Optional
The default value to return if the offset is outside the bounds of the partition or table. Default is NULL.
You can specify a default argument that is the same data type as the input value or goes with the offset argument.
You cannot specify a default argument that contains a complex data type or a SYSTIMESTAMP argument.
Return Value
The data type of the specified column_name.
Default if the return value is outside the bounds of the specified partition.
NULL if default is omitted or set to NULL.
Example 1
The following expression returns the date that the previous order was placed:
LAG ( ORDER_DATE, 1, NULL )
The following table shows the order information for this command:
ORDER_DATE
ORDER_ID
RETURN VALUE
2017/09/25
1
NULL
2017/09/26
2
2017/09/25
2017/09/27
3
2017/09/26
2017/09/28
4
2017/09/27
2017/09/29
5
2017/09/28
2017/09/30
6
2017/09/29
The lag value of the first row is outside the partition, so the function returns the default value of NULL.
Example 2
Your organization receives GPS pings from vehicles that include trip and event IDs and a time stamp. You want to calculate the time difference between each ping.
The following expression calculates the time difference between the current row and the previous row for two separate trips:
DATE_DIFF( EVENT_TIME, LAG ( EVENT_TIME, 1, NULL ), 'ss' )
You partition the data by trip ID and order by event ID.
The following tables shows trip information for this command:
TRIP_ID
EVENT_ID
EVENT_TIME
RETURN VALUE
101
1
2017-05-03 12:00:00
NULL
101
2
2017-05-03 12:00:34
34
101
3
2017-05-03 12:02:00
86
102
1
2017-05-03 12:00:00
NULL
102
2
2017-05-03 12:01:56
116
102
3
2017-05-03 12:02:00
4
The lag values of the first and fourth row are outside of the specified partition, so the function returns two default NULL values.