Rolling aggregations

This guide shows how to use the updateBy table operation for rolling calculations and aggregations. It returns a new table that adds columns calculated by one or more UpdateByOperation objects. The calculations can be cumulative, windowed by rows (ticks) or by time, or exponentially weighted.

To compare rolling aggregations with the other ways Deephaven summarizes data, and to choose between them, see Aggregations in Deephaven.

updateBy takes one or more UpdateByOperations and zero or more key columns. The result contains every source column plus any new columns the operations create. An operation whose output column has the same name as an existing column replaces that column. Without key columns, each operation runs over all rows. With key columns (also called grouping columns), each operation runs separately on each unique group.

For the full list of UpdateByOperations you can pass to updateBy, see the updateBy parameters reference.

updateBy vs updateby

This document refers to updateBy and updateby throughout. They are not identical:

• updateBy always refers to the updateBy table operation. You always call it as a method on a table:

• updateby is the Java package that defines the operations you pass to updateBy. Its UpdateByOperation interface provides these operations, for example EmMax and RollingFormula.

Keep this distinction in mind throughout the guide.

What are rolling aggregations?

Rolling aggregations differ from dedicated and combined aggregations in two ways: they preserve data history, and they can calculate over a window of rows instead of the whole group.

Preservation of data history

Most dedicated and combined aggregations output a single row for each group. The exceptions are headBy and tailBy, which output up to n rows for each group. In contrast, rolling aggregation tables have the same number of rows as the source table, so they preserve the data history that other aggregations discard.

To illustrate this, consider a cumulative sum on a single grouping column with two key values. Notice how resultDedicated and resultCombined have two rows — one for each group. In contrast, resultRolling has 20 rows — one for each row in the source table. You can see how the cumulative sum increases over time in the latter case.

Windowing

Dedicated and combined aggregations calculate each group's aggregated value over all of the group's rows. In contrast, rolling aggregations can be cumulative, windowed, or exponentially weighted.

A windowed aggregation calculates over a subset of the table called a window. You define the window as a number of rows (ticks) or as a span of time. For example, a windowed sum can add up the previous 10 rows. When a new row arrives in a ticking (live, updating) table, Deephaven updates the result to include the new row and drop the row that left the window.

To illustrate this, consider the following example, which calculates a cumulative sum and a rolling sum with updateBy. The rolling sum uses a 3-row window (the current row and the two rows before it), so the RollingSumValue column differs from CumSumValue:

Basic usage examples

The following examples demonstrate the fundamental patterns for using updateBy.

A single UpdateByOperation with no grouping columns

The following example calculates the tick-based rolling sum of the X column in the source table. RollingSum takes revTicks and an optional fwdTicks as its first arguments. Omitting fwdTicks is the same as passing 0. RollingSum(3, 0, ...) sets revTicks = 3 and fwdTicks = 0, so the window is the current row and the two previous rows. Tick-based vs time-based windows explains both parameters. The example passes no key columns, so all rows of the table form a single group.

Multiple UpdateByOperations with no grouping columns

The following example builds on the single-operation example by performing two UpdateByOperations in a single updateBy. It calculates the cumulative minimum and maximum, then derives the range from them with update.

Multiple UpdateByOperations with a single grouping column

The following example builds on the ungrouped multiple-operation example by specifying a grouping column. The grouping column is Letter, which contains alternating letters A and B. As a result, updateBy calculates the cumulative minimum, maximum, and range separately for each letter. The example splits result by letter with where to show this.

Multiple UpdateByOperations applied to multiple columns with multiple grouping columns

The following example builds on the single-grouping-column example by applying each UpdateByOperation to multiple columns and specifying multiple grouping columns. The grouping columns, Letter and Truth, contain alternating letters and random true/false values. Each unique combination of letter and boolean forms a group. The example splits result by letter and truth value to show the groups.

Applying an UpdateByOperation to all columns

The following example uses Fill to fill null values with the most recent previous non-null value. The example passes no columns to Fill, so it fills all columns in source except the key columns. The forward-filled columns replace the original columns of the same name, such as X and Y, in result.

Cumulative aggregations

Cumulative aggregations are the simplest operations in the updateby package. They are statistics computed over all previous data points in a series. Deephaven supports the following cumulative statistics:

The count-where operations, such as CumCountWhere, count the rows that match a filter you supply, rather than aggregating a column's values.

Cumulative sum

To illustrate a cumulative statistic, consider the cumulative sum. For each row, this operation calculates the sum of all previous values in a column, including the current row's value. The following illustration shows this:

The code for the illustration above looks like this:

Cumulative average

The updateby package has no method that computes the cumulative average of a column. However, you can still compute the cumulative average with a single CumSum operation that sums both the column and a column of ones, then divide:

This demonstrates the flexibility of the updateBy table operation. When no single operation computes the statistic you need, you can often combine an updateBy call with update, as shown here.

Windowed aggregations

Windowed aggregations operate on a finite window of rows rather than on all previous rows, as introduced in Windowing. This section shows how to define a window by rows (ticks) or by time, and lists the operations that use one.

Tick-based vs time-based windows

Tick-based windows aggregate values over a fixed number of rows. Tick-based operations take revTicks and an optional fwdTicks (default 0) as their first arguments, for example RollingSum(revTicks, fwdTicks, "Out=In") or RollingSum(revTicks, "Out=In"). The parameters set the window as follows:

• revTicks: The number of rows the window extends backward, counting the current row. A value of N includes the current row and the N-1 preceding rows.
• fwdTicks: The number of subsequent rows to include in the window.
• A negative fwdTicks ends the window before the current row, and a revTicks of 0 or less starts the window after the current row. Use them to build windows that lie entirely before or entirely after the current row.

Here are some examples of tick-based windows:

• 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 and excludes the current row.
• revTicks = 10, fwdTicks = 10: Contains the previous 9 rows, the current row, and the following 10 rows.
• revTicks = 10, fwdTicks = -5: Contains 5 rows, from 9 rows before to 5 rows before the current row (inclusive).
• revTicks = 11, fwdTicks = -1: Contains 10 rows, from 10 rows before to 1 row before the current row (inclusive).
• revTicks = -5, fwdTicks = 10: Contains 5 rows, from 6 rows after to 10 rows after the current row (inclusive).

Cumulative operators like CumSum are special cases of tick-based operators, where the window begins at the first row of the group and continues through to the current row.

Time-based windows aggregate values over time durations with variable window sizes defined by the revTime and fwdTime parameters:

• revTime: Duration to look back in time, either a Duration such as parseDuration("PT5s") or a number of nanoseconds.
• fwdTime: Duration to look forward in time.
• A negative fwdTime ends the window before the current row's timestamp, and a negative revTime starts the window after it.
• Time-based windows require a timestamp column, which is the first argument.

Here are some examples of time-based windows:

• revTime = parseDuration("PT0s"), fwdTime = parseDuration("PT0s"): Contains rows that exactly match the current timestamp.
• revTime = parseDuration("PT10m"), fwdTime = parseDuration("PT0s"): Contains rows from 10 minutes earlier through the current timestamp (inclusive).
• revTime = parseDuration("PT0s"), fwdTime = parseDuration("PT10m"): Contains rows from the current timestamp through 10 minutes following the current timestamp (inclusive).
• revTime = 60_000_000_000L, fwdTime = 60_000_000_000L: Contains rows from 1 minute earlier through 1 minute following the current timestamp (inclusive).
• revTime = parseDuration("PT10m"), fwdTime = parseDuration("-PT5m"): Contains rows from 10 minutes earlier through 5 minutes before the current timestamp (inclusive). This is a purely backward-looking window.
• revTime = -5_000_000_000L, fwdTime = 10_000_000_000L: Contains rows from 5 seconds following through 10 seconds following the current timestamp (inclusive). This is a purely forward-looking window.

Tick-based windowed calculations

The following example:

• Creates a static source table with two columns.
• Calculates the rolling sum of X grouped by Letter.
  • Uses three windows: one before, one containing, and one after the current row.
• Splits the result table by letter via where to show that each group gets its own windowed calculation.

Time-based windowed calculations

The following example uses the time-based window parameters described in Tick-based vs time-based windows. It:

• Creates a static source table with three columns.
• Calculates the rolling sum of X grouped by Letter.
  • Uses three windows: one before, one containing, and one after the current row's timestamp.
• Splits the result table by letter via where to show that each group gets its own windowed calculation.

Simple moving aggregations

Simple moving aggregations are statistics computed over a finite, moving window of data. These operations don't weight data points by their distance from the current row. Every point in the window counts equally, except in the weighted average, where a weight column you supply sets each point's weight. Deephaven supports the following simple moving statistics:

To illustrate a simple moving aggregation, consider the rolling average. For each row, this operation calculates the average of all values within a specified window. The following illustration shows this:

The code for the illustration above looks like this:

To compare window sizes, pass several RollingAvg operations at once. This example computes 2-, 3-, and 5-row backward-looking averages by group, using Letter as the key column:

For a time-based window, pass a timestamp column and a Duration to RollingAvg. This example computes 2-, 3-, and 5-second moving averages:

Additional rolling operations

Deephaven offers two more rolling operations that don't compute a fixed statistic. One groups values, and the other applies a custom formula:

RollingGroup collects the values in each window into a vector. RollingFormula applies a formula you write to the values in each window.

Rolling groups

The following examples create rolling groups with tick-based windows, then with time-based windows. To work with the resulting vectors, see how to work with arrays.

Use RollingGroup to create tick-based rolling groups, where each row's vector holds the values from a window of rows set by revTicks and fwdTicks. Here's an example that collects the three previous rows and the current row:

To create groups that include data after the current row, use the fwdTicks parameter. This example creates a group that consists of the two previous rows, the current row, and the next four rows:

Pass a timestamp column and a Duration to RollingGroup to create time-based rolling groups:

A time-based rolling group contains only the rows whose timestamps fall inside the window, so the vector's size varies from row to row. When no other row with the same Letter falls inside the window, the vector holds only the current row's value. A backward-looking tick-based vector reaches a fixed size once enough rows exist.

Use fwdTime to include rows after the current row. The following example groups every row with the same Letter whose timestamp is within five seconds of the current row:

Rolling formulas

A rolling formula can use any of Deephaven's built-in functions, arithmetic operators, or user-defined Groovy functions.

Use RollingFormula to create custom tick-based rolling formulas. Here's an example that computes the rolling geometric mean of a column X by group. The formula names the output column (GeomMeanX) and refers to the input column (X) directly. Within the formula, X is the vector of the window's values:

RollingFormula also creates custom time-based rolling formulas. You must supply a timestamp column, and you can make the time window backward-looking, forward-looking, or both. Here's an example that computes the 5-second rolling geometric mean of a column X by group:

Exponential moving aggregations

Exponential moving aggregations are moving statistics that, unlike windowed aggregations, use all preceding data points. However, they assign more weight to recent data and exponentially down-weight older data. This means distant observations have little effect on the result, while closer observations have a greater influence. A decay parameter controls how fast the weight decreases. Deephaven supports the following exponential moving statistics:

Each operation can take an optional OperationControl as its first argument to configure how it handles erroneous data. The decay parameter comes next. Tick-based operations take tickDecay. Time-based operations take a timestamp column followed by durationDecay (a Duration) or timeDecay (nanoseconds).

Tick-based decay (tickDecay):

• Smaller values decay faster and put more weight on recent data.
• tickDecay = 1: Each row has ~37% of the weight of the row after it.
• tickDecay = 10: Each row has ~90% of the weight of the row after it.
• Higher values create smoother, slower-responding averages.

Time-based decay (durationDecay):

• Controls how quickly older data loses influence over time.
• durationDecay = parseDuration("PT1s"): Data from 1 second ago has ~37% of the current row's weight.
• durationDecay = parseDuration("PT10s"): Data from 10 seconds ago has ~37% of the current row's weight.
• Longer decay times create more stable, less responsive averages.

To visualize an exponential moving statistic, consider the exponential moving average (EMA). The following illustration shows this:

The code for the illustration above looks like this:

The following example uses Ema to show the effect that different decay rates have on the exponential moving average:

To decay by time instead of by ticks, pass a timestamp column and a Duration to Ema. This version of the decay-rate example uses 2-, 4-, and 6-second decay times:

A row's weight falls to ~37% after one durationDecay, so EmaX2Sec responds fastest and EmaX6Sec responds slowest.

Bollinger Bands

Bollinger Bands are a technical-analysis tool, common in finance, that uses a moving average and a moving standard deviation to draw bands around a price series.

To compute Bollinger Bands:

1. Compute the moving average.
2. Compute the moving standard deviation.
3. Compute the upper and lower envelopes: the moving average plus and minus w moving standard deviations, where w sets the width of the bands.

Tick-based Bollinger Bands using simple moving statistics

This example passes RollingAvg and RollingStd to updateBy to compute the moving average and standard deviation, then computes the envelope with update. revTicks sets the size of the rolling window in ticks.

Time-based Bollinger Bands using simple moving statistics

Using the source table, w, and plotBollinger from the tick-based example, this example passes RollingAvg and RollingStd to updateBy to compute the moving average and standard deviation, then computes the envelope with update. revTime sets the length of both rolling windows.

Tick-based Bollinger Bands using exponential moving statistics

Using the source table, w, and plotBollinger from the tick-based example, this example passes Ema and EmStd to updateBy to compute the moving average and standard deviation, then computes the envelope with update. tickDecay sets the decay rate in ticks, which controls how much weight earlier rows get.

Time-based Bollinger Bands using exponential moving statistics

Using the source table, w, and plotBollinger from the tick-based example, this example passes Ema and EmStd to updateBy to compute the moving average and standard deviation, then computes the envelope with update. durationDecay sets the decay rate as a duration, which controls how much weight earlier rows get.

Sequential difference and forward fill

updateBy also supports two operations that are not rolling aggregations: Delta and Fill.

Sequential difference

The Delta operation calculates the difference between a row's value and the previous row's value in a given column. This is useful for calculating the rate of change. The following example demonstrates this:

You can calculate sequential differences per group, like all other updateBy operations:

Detrend time-series data

Sequential differencing is a common first step in detrending time-series data, which means removing a long-term upward or downward trend so that short-term changes are easier to see. The Delta operation makes this easy. The following example plots Price and its sequential difference, DiffPrice, for each ticker:

Null handling with DeltaControl

To control how Delta treats null values, pass a DeltaControl as its optional first argument, control. The following behaviors are available:

• DeltaControl.NULL_DOMINATES: A valid value following a null value returns null.
• DeltaControl.VALUE_DOMINATES: A valid value following a null value returns the valid value.
• DeltaControl.ZERO_DOMINATES: A valid value following a null value returns zero.

Because X contains nulls, the delta columns differ on the rows where a valid value follows a null. DefaultDeltaX matches NullDomDeltaX, because Delta uses NULL_DOMINATES by default.

Forward fill

Fill fills in null values with the most recent non-null value, which is useful for filling in missing data points in a time series. The example in Applying an UpdateByOperation to all columns fills every column. This example fills a single column per group:

Handling erroneous data

It's common for tables to contain null, NaN, or other erroneous values. Each family of updateBy operations has a default way of handling them, and some operations accept a control argument that changes it. To replace nulls with the most recent non-null value, use Fill.

Default null handling behavior

By default, updateBy operations handle null values as follows:

• Cumulative operations (CumSum, CumMax, etc.): Skip null values and continue with the last valid result.
• Rolling statistics (RollingAvg, RollingSum, etc.): Exclude null values from window calculations.
• Count-where operations (CumCountWhere, RollingCountWhere): Evaluate their filter on every row, so a row with a null value is counted when the filter matches it.
• Rolling groups and formulas (RollingGroup, RollingFormula): Keep null values in the window's vector, so your formula decides how to treat them.
• Exponential operations (Ema, EmMax, etc.): Skip null values unless configured otherwise via OperationControl.

Operations with explicit error handling controls

You can configure some operations to handle erroneous data through control parameters:

• Exponential moving operations (Ema, Ems, EmMin, EmMax, and EmStd): Pass an OperationControl as the first argument, control.
• Delta: Pass a DeltaControl as the first argument, control. See Null handling with DeltaControl.

For detailed configuration options, see the OperationControl reference guide.

Choose the right aggregation

The following questions help you choose between dedicated aggregations, combined aggregations, and updateBy operations.

Do you need to preserve data history?

As shown in Preservation of data history, dedicated and combined aggregations and updateBy differ in how many rows they return:

• Use aggBy (or sumBy, avgBy, etc.) when you want a single aggregated result per group.
• Use updateBy when you need to see how the aggregated value progresses row by row.
• Use a dedicated or combined aggregation with a time-bucket key column when you want one row per group per interval, which gives a partial history. See Downsampling.

For example, the following groups rows into buckets of five with a TimeGroup key column, which stands in for a time interval. (int) keeps the bucket number an integer, because dividing two integers in a query string returns a double:

What type of aggregation window do you need?

• Cumulative aggregations (CumSum, CumMax, etc.): Use when you want to aggregate all data from the beginning up to the current row.
• Windowed aggregations: Use when you want to consider only a window of rows before, around, or after the current row:
  • Tick-based windows (RollingSum, etc.): Use when your window should be defined by a fixed number of rows.
  • Time-based windows (RollingSum with a timestamp column, etc.): Use when your window should be defined by a time duration.

How should recent vs distant data be weighted?

• Simple moving aggregations (RollingAvg, RollingSum, etc.): Use when a data point's weight shouldn't depend on how far it is from the current row.
• Exponential moving aggregations (Ema, EmMax, etc.): Use when recent data should have more influence than older data, with weights that decay exponentially by rows or by time.

Do you need custom calculations?

• Standard operations: Use built-in methods such as RollingSum, RollingAvg, and CumMax for common statistical calculations.
• Custom formulas: Use RollingFormula when you need to apply custom mathematical expressions to windowed data.
• Grouping operations: Use RollingGroup when you need to collect data into vectors for further processing.

Do you need to handle missing or erroneous data?

See Handling erroneous data.

Related documentation

• Aggregations in Deephaven
• Create an empty table
• Create a time table
• Dedicated aggregations
• Combined aggregations
• Downsampling
• Select, view, and update
• Handle nulls, infs, and NaNs
• How to create XY series plots
• How to work with arrays
• Formulas
• updateBy
• aggBy
• RollingFormula
• RollingGroup
• Delta
• Fill