Formulas

This guide covers formulas in the context of query strings. Formulas in Deephaven work like formulas in math and computer science. In a query string, a formula is an expression that defines the values of a column based on other columns, constants, functions, and more.

A formula in a query string creates a new column in a table, or replaces an existing column that has the same name. Formulas work on both static and real-time tables. When a real-time table updates, the columns its formulas create update with it.

On a real-time table, the engine rejects some formulas that use the built-in variables i, ii, and k, or a column array variable such as Column_. Refreshing table restrictions lists the cases that are safe.

The following table operations use formulas to create columns:

select
view
update
update_view
lazy_update
select_distinct
agg_by with agg.formula
update_by with rolling_formula_tick or rolling_formula_time

What makes up a formula?

Every formula has three parts, in this order:

The left-hand side names the output column.
The assignment operator separates the two sides.
The right-hand side is the expression that computes each row's value.

The left-hand side (LHS)

The LHS of a formula contains only the name of the column the formula creates or replaces. The name must follow these rules:

It must start with an ASCII letter (A–Z or a–z), an underscore (_), or a dollar sign ($).
After the first character, it can contain only ASCII letters, digits, underscores, and dollar signs. That rules out spaces, symbols such as @, #, %, +, -, and =, and non-ASCII characters such as é or €, even though Java identifiers can contain non-ASCII characters.
It cannot be a Java reserved keyword or literal (true, false, null).
It cannot be in, not, or one of the built-in variables i, ii, and k. The query language reserves those variables for row positions and row keys.

Deephaven recommends PascalCase, or upper camel case, for column names.

The assignment operator

Every formula must contain the assignment operator (=) to assign the value of the RHS to the LHS column. Don't confuse it with the equality operator (==), which compares two values. Only the first = in a formula assigns. Any later single = on the RHS is an equality test, the same as ==.

The right-hand side (RHS)

The RHS of a formula can be any valid expression that evaluates to a value. It can include:

A simple formula

Consider the following formula:

Fahrenheit = (Celsius * 9 / 5) + 32

You may recognize this formula: it converts a temperature in Celsius to Fahrenheit. From left to right, its parts are:

Fahrenheit is the name of the output column.
= is the assignment operator.
( and ) are parentheses used to group operations.
Celsius is the name of another column.
* is the multiplication operator.
9 is a numeric literal.
/ is the division operator.
5 is another numeric literal.
+ is the addition operator.
32 is another numeric literal.

The following example uses the formula in update_view to add a Fahrenheit column to a new table called result:

from deephaven import empty_table

source = empty_table(10).update("Celsius = randomInt(0, 100)")
result = source.update_view("Fahrenheit = (Celsius * 9 / 5) + 32")

Several formulas in one operation

One table operation can take a list of formulas. Deephaven evaluates them in order, so a formula can use a column that an earlier formula in the same call created. A formula whose LHS names an existing column replaces that column.

The following example passes three formulas to update. The second formula uses the Celsius column that the first formula creates. The third formula replaces the Fahrenheit column with its value rounded to the nearest whole number:

from deephaven import empty_table

result = empty_table(5).update(
    [
        "Celsius = i * 7.5",
        "Fahrenheit = (Celsius * 9 / 5) + 32",
        "Fahrenheit = round(Fahrenheit)",
    ]
)

A more complex formula

Consider the following formula:

RandomIsPositive = (randomDouble(-1, 1) > 0) ? `Positive` : `Negative`

This formula uses the ternary conditional operator (?) to determine the value of the RandomIsPositive column based on the value of a random double. Its parts are:

RandomIsPositive is the name of the output column.
= is the assignment operator.
( and ) are parentheses used to group operations.
randomDouble(-1, 1) is a call to the built-in randomDouble function, which generates a random double value from -1 (inclusive) to 1 (exclusive).
> is the greater-than comparison operator.
0 is a numeric literal.
? is the ternary conditional operator.
`Positive` is a string literal (note the backticks).
: is the separator between the value used when the condition is true and the value used when it is false.
`Negative` is another string literal (note the backticks).

The following example uses the formula in update. Each row is Positive or Negative, depending on the random value generated for that row:

from deephaven import empty_table

result = empty_table(10).update(
    "RandomIsPositive = (randomDouble(-1, 1) > 0) ? `Positive` : `Negative`"
)

Strings that look like formulas but aren't

Filters, joins, and most aggregations also take strings that contain =. These strings look like formulas, but they aren't:

In filters, such as those passed to where, the string is a boolean expression that decides which rows to keep. In a filter, a single = is an equality test, the same as ==, not an assignment. For example, X = 5 keeps the rows where X is 5.
In exact and relational joins and time-series and range joins, both sides of A = B must be column names. In the match columns, A = B matches left-table column A to right-table column B. In the columns to add, A = B adds right-table column B to the result under the name A. Most join methods take the match columns as the on argument and the columns to add as the joins argument.
In combined aggregations and rolling aggregations, operations that compute a fixed statistic, such as agg.sum_ and rolling_sum_tick, take A = B strings. Both sides must be column names: A is the output column, and B is the input column it aggregates.

To aggregate the result of an expression, use agg.formula. For a rolling window, use rolling_formula_tick or rolling_formula_time. Each of these takes a full formula.

For example, the following query tries to sum the product of two columns with agg.sum_. It fails with InvalidNameException: Invalid column name "Price * Qty", because the RHS of an agg.sum_ string must be a column name:

from deephaven import new_table, agg
from deephaven.column import string_col, double_col, int_col

source = new_table(
    [
        string_col("Sym", ["A", "A", "B", "B"]),
        double_col("Price", [10.0, 11.0, 20.0, 21.0]),
        int_col("Qty", [1, 2, 3, 4]),
    ]
)

result = source.agg_by(agg.sum_("Total = Price * Qty"), by="Sym")

agg.formula takes a full formula, so it can aggregate the expression:

from deephaven import new_table, agg
from deephaven.column import string_col, double_col, int_col

source = new_table(
    [
        string_col("Sym", ["A", "A", "B", "B"]),
        double_col("Price", [10.0, 11.0, 20.0, 21.0]),
        int_col("Qty", [1, 2, 3, 4]),
    ]
)

result = source.agg_by(agg.formula("Total = sum(Price * Qty)"), by="Sym")

Formulas on partitioned tables

The table operations that take formulas, such as update and agg_by, work on ordinary tables. To apply one of them to every constituent table of a partitioned table, use the partitioned table's proxy or transform:

The proxy supports every operation listed at the top of this page except lazy_update.
A transform can apply any of them.