Skip to main content
Version: Java (Groovy)

RollingFormula

The RollingFormula method creates a rolling formula column for the supplied column name pairs, using ticks or time as the windowing unit.

Ticks are row counts. You may specify the reverse and forward window as the number of rows to include. The current row is considered to belong to the reverse window but not the forward window. Negative values are allowed and can be used to generate completely forward or completely reverse windows. Here are some examples of window values:

  • revTicks = 1, fwdTicks = 0 - contains only the current row.
  • revTicks = 10, fwdTicks = 0 - contains 9 previous rows and the current row.
  • revTicks = 0, fwdTicks = 10 - contains the following 10 rows, but excludes the current row.
  • revTicks = 10, fwdTicks = 10 - contains the previous 9 rows, the current row, and the 10 rows following.
  • revTicks = 10, fwdicks = -5 - contains 5 rows, beginning at 9 rows before, ending at 5 rows before the current row (inclusive).
  • revTicks = 11, fwdTicks = -1 - contains 10 rows, beginning at 10 rows before, ending at 1 row before the current row (inclusive).
  • revTicks = -5, fwdTicks = 10 - contains 5 rows, beginning 5 rows following, ending at 10 rows following the current row (inclusive).

When windowing by time, this function accepts nanoseconds or time strings as the reverse and forward window parameters. Here are some examples of window values:

  • revTime = 0, fwdTime = 0 - contains rows that exactly match the current row timestamp.
  • revTime = 600_000_000_000, fwdDuration = 0 - contains rows from 10m before through the current row timestamp (inclusive).
  • revTime = 0, fwdTime = 600_000_000_000 - contains rows from the current row through 10m following the current row timestamp (inclusive).
  • revDuration = “PT00:10:00”, fwdDuration = “PT00:10:00” - contains rows from 10m before through 10m following the current row timestamp (inclusive).
  • revDuration = “PT00:10:00”, fwdDuration = “-PT00:05:00” - contains rows from 10m before through 5m before the current row timestamp (inclusive), this is a purely backward-looking window.
  • revDuration = “-PT00:05:00”, fwdDuration = “PT00:10:00” - contains rows from 5m following through 10m following the current row timestamp (inclusive), this is a purely forward-looking window.

A row containing a null in the timestamp column belongs to no window and will not be considered in the windows of other rows; its output will be null.

Syntax​

RollingFormula(revTicks, fwdTicks, formula, paramToken, pairs...)
RollingFormula(revTicks, formula, paramToken, pairs...)
RollingFormula(timestampCol, revDuration, fwdDuration, formula, paramToken, pairs...)
RollingFormula(timestampCol, revDuration, formula, paramToken, pairs...)
RollingFormula(timestampCol, revTime, fwdTime, formula, paramToken, pairs...)
RollingFormula(timestampCol, revTime, formula, paramToken, pairs...)

Parameters​

ParameterTypeDescription
revTickslong

The look-behind window size in ticks (rows). If positive, it defines the maximum number of rows before the current row that will be used. If negative, it defines the minimum number of rows after the current row that will be used.

fwdTickslong

The look-forward window size in ticks (rows). If positive, it defines the maximum number of rows after the current row that will be used. If negative, it defines the minimum number of rows before the current row that will be used.

formulaString

The user-defined formula to apply to the group. The formula can contain a combination of any of the following:

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

The parameter name for the input column's vector within the formula. If formula is max(each), then each is the paramToken.

pairsString...

The input/output column name pairs.

timestampColString

The name of the timestamp column.

revDurationDuration

The look-behind window size in Duration.

fwdDurationDuration

The look-forward window size in Duration.

revTimelong

The look-behind window size in nanoseconds.

fwdTimelong

The look-forward window size in nanoseconds.

Returns​

An UpdateByOperation to be used in an updateBy table operation.

Examples​

The following example performs a time-based and a tick-based rolling formula updateBy operation on the prices table, grouped by the Ticker column.

prices = emptyTable(20).update("Timestamp = '2024-02-23T09:30:00 ET' + ii * SECOND", "Ticker = (i % 2 == 0) ? `NVDA` : `GOOG`", "Price = randomDouble(100.0, 500.0)")

formulaTick = RollingFormula(5, "sum(x * x)", "x", "SumPriceSquared_Tick = Price")
formulaTime = RollingFormula("Timestamp", 10_000_000, "sum(x * x)", "x", "SumPriceSquared_Time = Price")

result = prices.updateBy([formulaTick, formulaTime], "Ticker")