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 sum_by, calculates one statistic. A multi-aggregation, also called a combined aggregation, calculates several in one agg_by operation.

The groups come from key columns, which you pass in the by argument. 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.

Operation	Result rows	Result columns
Group (`group_by`)	One per group	The key columns, plus a vector of each group's values for every other column
Single aggregation (`sum_by`, `avg_by`, and others)	One per group	The key columns, plus the statistic's results (usually one column per non-key column, or a single count column for `count_by`)
Multi-aggregation (`agg_by`)	One per group	The key columns, plus any mix of aggregations you choose
Rolling aggregation (`update_by`)	The same rows as the source	The 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.

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.updateby import cum_sum

source = new_table(
    [
        string_col("Sym", ["A", "B", "A", "B", "A"]),
        int_col("Price", [10, 20, 30, 40, 50]),
    ]
)

grouped = source.group_by(by="Sym")
summed = source.sum_by(by="Sym")
running = source.update_by(ops=cum_sum(cols="Total = Price"), by="Sym")

Group

group_by 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 sum_by, avg_by, max_by, and first_by, apply it to every non-key column, so the result has one column for each input column.

count_by 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 sum_by 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 agg_by to choose the input columns. On a Boolean column, sum_by counts the true values.

agg_all_by also applies one statistic to every non-key column, but it takes that statistic as an aggregation from deephaven.agg, so you can use one that has no dedicated method, such as agg.pct.

Multi-aggregation

agg_by applies a list of aggregations, such as agg.sum_ or agg.avg, in one operation. Each aggregation can use different input columns, and agg.formula lets you write a custom calculation. Import the aggregations with from deephaven import agg. Because agg_by computes all of its aggregations in one pass, it is more efficient than running a separate single aggregation for each statistic.

Rolling aggregation

update_by 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 cum_sum), exponentially weighted (such as ema_tick), computed over a window of rows or time (such as rolling_avg_tick), or based on the previous row (such as forward_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:

partition_by returns a partitioned table that holds a separate sub-table for each group.
rollup returns a rollup table, an expandable hierarchy of aggregates for the UI.

Operations that return source values

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

head_by and tail_by return the first or last n rows of each group.
select_distinct 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:

If you need a value on every source row, use update_by:
- To fill null values with the last non-null value, or to calculate the change from the previous row, use forward_fill or delta.
- For a cumulative calculation, use a cum_* operation, such as cum_sum or cum_max.
- To weight recent values more heavily, use an exponentially weighted operation, such as ema_tick, ema_time, or ems_tick.
- Otherwise, use a rolling_*_time operation, such as rolling_avg_time, for a window sized by time, or a rolling_*_tick operation, such as rolling_avg_tick, for a window sized by row count.
If you need a separate table for each group, use partition_by.
If you need an expandable hierarchy in the UI, use rollup.
If you need each group's rows kept together as vectors, use group_by.
If you need more than one kind of statistic, different statistics for different columns, or a custom formula, use agg_by.
If you need the first or last row of each group, use first_by or last_by.
Otherwise, use a single aggregation such as sum_by, avg_by, or count_by.

Aggregations on ticking tables

On a ticking source table, these operations update incrementally.

group_by, single aggregations, and agg_by: 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 preserve_empty=True in agg_by.
- If a modified row changes its key value, the row moves from one group to another.
update_by: 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.
- agg_by and the single aggregations ignore the rows a blink table removes, so their results summarize every row seen since the operation started.
- group_by, partition_by, agg.group, agg.partition, and agg.formula raise an error on a blink table.
- rollup raises an error on a blink table if you set include_constituents=True or use a first, last, group, or formula aggregation.
- update_by with key columns raises 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 cum_sum starts over each cycle.