Oracle Docs define Lead and Lag as follows: The LAG and LEAD functions are useful for comparing values when the relative positions of rows can be known reliably. They work by specifying the count of rows which separate the target row from the current row. Because the functions provide access to more than one row of a table at the same time without a self-join, they can enhance processing speed. The LAG function provides access to a row at a given offset prior to the current position, and the LEAD function provides access to a row at a given offset after the current position.
Suppose you wanted to create a cumulative column of data of say the salary of a number of employees. And your data looked like the following:
But you wanted an output like this
The solution would entail accessing the value of the previous row on the current row and summing them together and repeating that all the way down. Unfortunately, we don't have analytic functions (just simple aggregation functions) in Talend DI. Because data manipulation is typically done in a row-by-row fashion in data integration jobs, it's a bit tricky to implement a solution that gives you access to the value on the previous row or any number of rows as an offset (essentially the logic embedded in the LAG function). The following is a simple solution for doing just that. It's implemented in a tJavaRow, but the same logic can be implemented in a tMap. If the number of offsets is other than 1 (previous row only), we could leverage the Numeric.Sequence function to create a sequence that we can use to determine how many records to offset by. We sort the fields by Fname to make sure they're consecutive and the logic works right.