Skip to main content
Version: Java (Groovy)

updateBy

updateBy creates a table with additional columns calculated from window-based aggregations of columns in the source table.

The aggregations are defined by the provided operations, which support incremental aggregations over the corresponding rows in the source table. The aggregations will apply position or time-based windowing and compute the results over the entire table or each row group as identified by the provided key columns.

Syntax​

result = source.updateBy(operation)
result = source.updateBy(operations)
result = source.updateBy(control, operations)
result = source.updateBy(operation, byColumns...)
result = source.updateBy(operations, byColumns...)
result = source.updateBy(operations, byColumns)
result = source.updateBy(control, operations, byColumns)

Parameters​

ParameterTypeDescription
operationUpdateByOperation

An UpdateByOperation that will produce a calculation from one or more given columns in a source table. The following UpdateByOperations are available:

operationsCollection<UpdateByOperation>

A collection of one or more UpdateByOperations that will produce calculation(s) from one or more given columns in a source table.

controlUpdateByControl

An interface to control the behavior of an updateBy table operation.

byColumnsString...

One or more key columns that group rows of the table.

Returns​

A new table with rolling window operations applied the the specified column(s).

Examples​

In the following example, a source table is created. The source table contains two columns: Letter and X. An updateBy is applied to the source table, which calculates the cumulative sum of the X column. The Letter column is given as the by column. Letter is A when X is even, and B when odd. Thus, the result table contains a new column, SumX, which contains the cumulative sum of the X column, grouped by Letter.

source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = i")

result = source.updateBy(CumSum("SumX = X"), "Letter")

The following example takes the same source data, but instead computes a row-based rolling sum using RollingSum. The rolling sum is calculated given a window of two rows back, and two rows ahead. Thus, SumX has the windowed sum of a five-row window, where each value is at the center of the window. Rows at the beginning and end of the table don't have enough data above and below them, respectively, so their summed values are smaller.

source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = i")

result = source.updateBy(RollingSum(3, 2, "SumX = X"), "Letter")

The following example builds on the previous examples by adding a second data column, Y, to the source table. The CumSum UpdateByOperation is then given two columns, so that the cumulative sum of the X and Y columns are both calculated.

source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = i", "Y = randomInt(0, 10)")

result = source.updateBy(CumSum("SumX = X", "SumY = Y"), "Letter")

The following example modifies the previous example by perfoerming two separate UpdateByoperations. The first uses CumSum on the X column like the previous example, but instead performs a tick-based rolling sum on the Y column with RollingSum.

source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = i", "Y = randomInt(0, 10)")

result = source.updateBy([CumSum("SumX = X"), RollingSum(2, 1, "RollingSumY = Y")], "Letter")

The following example builds on previous examples by adding a second key column, Truth, which contains boolean values. Thus, groups are defined by unique combinations of the Letter and Truth columns.

source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "Truth = randomBool()", "X = i", "Y = randomInt(0, 10)")

result = source.updateBy([CumSum("SumX = X"), RollingSum(2, 1, "RollingSumY = Y")], "Letter", "Truth")