AggFormula

AggFormula returns an aggregator that computes a user-defined formula aggregation across specified columns. This allows you to apply custom formulas to groups of rows, leveraging built-in, mathematical, or user-defined functions.

Syntax

AggFormula(formula, paramToken, columnNames...)

Parameters

ParameterTypeDescription
formulaString

The user-defined formula to apply to each group. This formula can contain:

  • Built-in functions such as min, max, etc.
  • Mathematical operations such as *, +, /, etc.
  • User-defined functions

If paramToken is not null, the formula can only be applied to one column at a time, and it is applied to the specified paramToken. If paramToken is null, the formula is applied to any column or literal value present in the formula. The use of paramToken is deprecated.

Key column(s) can be used as input to the formula. When this happens, key values are treated as scalars.

paramTokenString

The parameter name within the formula. If paramToken is each, then formula must contain each. For example, max(each), min(each), etc. Use of this parameter is deprecated.

columnNamesString...

The source column(s) for the calculations.

  • "X" applies the formula to each value in the X column for each group.
  • "Y = X" applies the formula to each value in the X column for each group and renames it to Y.
  • "X, A = B" applies the formula to each value in the X column for each group and the formula to each value in the B column while renaming it to A.

Caution

If an aggregation does not rename the resulting column, the aggregation column will appear in the output table, not the input column. If multiple aggregations on the same column do not rename the resulting columns, an error will result, because the aggregations are trying to create multiple columns with the same name. For example, in table.aggBy([agg.AggSum("X"), agg.AggAvg("X")]), both the sum and the average aggregators produce column X, which results in a name conflict error.

Returns

An aggregator that computes a user-defined formula within an aggregation group, for each input column.

Examples

The following example uses AggFormula to calculate several formula aggregations by the Letter column. Since paramToken is null, the formulas are applied to any column or literal value present in the formula. The specified formulas operate on zero, one, two, and three different columns at a time.

source = emptyTable(20).update("X = i", "Y = 2 * i", "Z = 3 * i", "Letter = (X % 2 == 0) ? `A` : `B`")

result = source.aggBy([
    AggFormula("OutA = sqrt(5.0)"),
    AggFormula("OutB = min(X)"),
    AggFormula("OutC = min(X) + max(Y)"),
    AggFormula("OutD = sum(X + Y + Z)"),
], "Letter")

The following example uses AggFormula to calculate the aggregate minimum across several columns by the Letter column. Since paramToken is not null, the formula is applied to the specified paramToken.

import static io.deephaven.api.agg.Aggregation.AggFormula

source = emptyTable(20).update("X = i", "Y = 2 * i", "Z = 3 * i", "Letter = (X % 2 == 0) ? `A` : `B`")

result = source.aggBy([AggFormula("min(each)", "each", "MinX = X", "MinY = Y", "MinZ = Z")], "Letter")

In this example, AggFormula is used to calculate the aggregated average across several column by the Letter and Color columns. Since paramToken is not null, the formula is applied to the specified paramToken.

import static io.deephaven.api.agg.Aggregation.AggFormula

colors = ["Red", "Green", "Blue"]

source = emptyTable(40).update("X = 0.1 * i", "Y1 = Math.pow(X, 2)", "Y2 = Math.sin(X)", "Y3 = Math.cos(X)", "Letter = (i % 2 == 0) ? `A` : `B`", "Color = (String)colors[i % 3]")

myAgg = [AggFormula("avg(k)", "k", "AvgY1 = Y1", "AvgY2 = Y2", "AvgY3 = Y3")]

result = source.aggBy(myAgg, "Letter", "Color")

In this example, AggFormula is used to calculate the aggregate sum of squares across each of the X, Y and Z columns by the Letter column. Since paramToken is not null, the formula is applied to the specified paramToken.

import static io.deephaven.api.agg.Aggregation.AggFormula

source = emptyTable(20).update("X = i", "Y = 2 * i", "Z = 3 * i", "Letter = (X % 2 == 0) ? `A` : `B`")

myAgg = [AggFormula("sum(each * each)", "each", "SumSqrX = X", "SumSqrY = Y", "SumSqrZ = Z")]

result = source.aggBy(myAgg, "Letter")

In this example, AggFormula calls a user-defined closure to compute the range of the X, Y, and Z columns by the Letter column. Since paramToken is not null, the formula is applied to the specified paramToken.

Note

The output of an AggFormula is of type java.lang.Object unless an explicit typecast is made in the formula string.

import static io.deephaven.api.agg.Aggregation.AggFormula

source = emptyTable(20).update("X = i", "Y = 2 * i", "Z = 3 * i", "Letter = (X % 2 == 0) ? `A` : `B`")

rangeX = {x -> x.max() - x.min()}

myAgg = [AggFormula("(int)rangeX(each)", "each", "RangeX = X", "RangeY = Y", "RangeZ = Z")]

result = source.aggBy(myAgg, "Letter")