Aggregations in Deephaven

An aggregation summarizes the values in a group of rows, such as an average or a count. Deephaven offers several table operations that group or aggregate data, and each returns a different kind of result. They work on live data as well as static data: when a ticking source table changes, each result updates with it, as described in Aggregations on ticking tables. This guide explains what each operation returns and how to choose among them.

What is an aggregation?

An aggregation breaks a table into groups of rows and calculates one or more summary values for each group, such as a sum, an average, or a count. A single aggregation, such as sumBy, calculates one statistic. A multi-aggregation, also called a combined aggregation, calculates several in one aggBy operation.

The groups come from key columns, which you pass to the operation by name, as strings. When you pass no key columns, the whole table is one group.

The table operations

The following table compares the operations that group or aggregate data. The sections that follow it describe each operation and link to its methods.

OperationResult rowsResult columns
Group (groupBy)One per groupThe key columns, plus a vector of each group's values for every other column
Single aggregation (sumBy, avgBy, and others)One per groupThe key columns, plus the statistic's results (usually one column per non-key column, or a single count column for countBy)
Multi-aggregation (aggBy)One per groupThe key columns, plus any mix of aggregations you choose
Rolling aggregation (updateBy)The same rows as the sourceThe source columns, plus new or replaced columns

The following example runs three of these operations on the same five-row table. grouped has one row per Sym that holds a vector of that symbol's prices. summed has one row per Sym that holds the total price. running keeps all five rows and adds a running total for each Sym.

Group

groupBy collects the rows of each group together. Every non-key column becomes a column of vectors. Use it when you want the group's values themselves, not a summary of them.

Single aggregation

A single aggregation calculates one statistic for each group. Most, such as sumBy, avgBy, maxBy, and firstBy, apply it to every non-key column, so the result has one column for each input column.

countBy is the exception: it returns one new column that holds the number of rows in each group, and you choose that column's name.

A numeric statistic such as sumBy raises an error if any non-key column has a type it can't compute, such as a string column. Drop those columns first, or use aggBy to choose the input columns. On a Boolean column, sumBy counts the true values.

aggAllBy also applies one statistic to every non-key column, but it takes that statistic as an AggSpec, so you can use one that has no dedicated method, such as AggSpec.percentile.

Multi-aggregation

aggBy applies a list of aggregations, such as AggSum or AggAvg, in one operation. Each aggregation can use different input columns, and AggFormula lets you write a custom calculation. The Groovy console imports these methods statically, so you can call them by name. Because aggBy computes all of its aggregations in one pass, it is more efficient than running a separate single aggregation for each statistic.

Rolling aggregation

updateBy keeps every row of the source table and adds columns calculated by one or more update-by operations. The calculations can be cumulative (such as CumSum), exponentially weighted (such as Ema), computed over a window of rows or time (such as RollingAvg), or based on the previous row (such as Fill or Delta). With key columns, each group is calculated independently, but the rows stay in their original positions.

Operations that return other table types

Two more operations group data but do not return a regular table:

Operations that return source values

Three more operations return values taken from the source rows instead of summaries of them:

  • headBy and tailBy return the first or last n rows of each group.
  • selectDistinct returns one row for each unique combination of values in the columns you name.

Choose an operation

The following figure presents a flowchart to help choose the right operation for your query.

The same questions in text form, in order:

  1. If you need a value on every source row, use updateBy:
    • To fill null values with the last non-null value, or to calculate the change from the previous row, use Fill or Delta.
    • For a cumulative calculation, use CumSum, CumMax, or a related operation.
    • To weight recent values more heavily, use an exponentially weighted operation, such as Ema, Ems, or EmStd.
    • Otherwise, use a rolling operation such as RollingAvg. Pass a timestamp column and a duration to size the window by time, or a row count to size it by rows.
  2. If you need a separate table for each group, use partitionBy.
  3. If you need an expandable hierarchy in the UI, use rollup.
  4. If you need each group's rows kept together as vectors, use groupBy.
  5. If you need more than one kind of statistic, different statistics for different columns, or a custom formula, use aggBy.
  6. If you need the first or last row of each group, use firstBy or lastBy.
  7. Otherwise, use a single aggregation such as sumBy, avgBy, or countBy.

Aggregations on ticking tables

On a ticking source table, these operations update incrementally.

  • groupBy, single aggregations, and aggBy: The engine updates only the groups that contain added, removed, or modified source rows.
    • A new key value adds a result row.
    • A group that has no rows left is removed from the result, unless you set preserveEmpty to true in aggBy.
    • If a modified row changes its key value, the row moves from one group to another.
  • updateBy: Added and removed rows pass through to the result. A change to one source row can also modify other rows, because a row's value can depend on the rows in its cumulative or rolling window.
  • Blink tables: A blink table holds only the rows added in the current update cycle.
    • aggBy and the single aggregations ignore the rows a blink table removes, so their results summarize every row seen since the operation started.
    • groupBy, partitionBy, AggGroup, AggPartition, and AggFormula throw an error on a blink table.
    • rollup throws an error on a blink table if you pass true for includeConstituents or use a first, last, group, or formula aggregation.
    • updateBy with key columns throws an error on a blink table. Without key columns, it sees only the rows in the current update cycle, so a cumulative operation such as CumSum starts over each cycle.