Multi-aggregation

This guide shows you how to collect summary information for groups of data using multi-aggregation, also called combined aggregation: several aggregations applied in a single operation.

When working with data, you often need to break the data into subgroups and then perform calculations on each group. For example, a large multinational corporation may want to know its average employee salary by country, or a teacher might want to analyze test scores for various classes.

Breaking a table into subgroups and then performing several calculations on each subgroup in one operation is known as "combined aggregation." The name joins two ideas: each calculation is an aggregation, which usually summarizes the data within a group, and several of them run together in one operation (combined).

To compare multi-aggregation with the other ways Deephaven summarizes data, and to choose between them, see Aggregations in Deephaven.

Why use combined aggregations?

Deephaven provides many dedicated aggregations, such as maxBy and minBy. These are good options when you need only one type of aggregation. When you need more than one aggregation, or a custom calculation with AggFormula, combined aggregations are more flexible, and more efficient because they group the data once instead of once per calculation.

Syntax

Define the aggregations in a list, such as aggList, then apply them to data with the aggBy method:

Most aggregations take the columns to aggregate as strings. Write a column as "OutputColumn = InputColumn" to put the result in a new column named OutputColumn, or give only the input column's name to keep that name. Every argument after the list is a column that defines the groups.

What aggregations are available?

A number of built-in aggregations are available. The following list covers the most common ones. For the full set, see the Aggregation Javadoc.

  • AggAbsSum - Sum of absolute values for each group.
  • AggApproxPct - Approximate percentile of values for each group.
  • AggAvg - Average value for each group.
  • AggCount - Number of rows for each group.
  • AggCountDistinct - Number of unique values for each group.
  • AggCountWhere - Number of rows for each group that pass a set of filters.
  • AggDistinct - Vector of unique values for each group.
  • AggFirst - First value for each group.
  • AggFormula - Custom formula for each group.
  • AggGroup - Vector of values for each group.
  • AggLast - Last value for each group.
  • AggMax - Maximum value for each group.
  • AggMed - Median value for each group.
  • AggMin - Minimum value for each group.
  • AggPartition - Subtable of the rows in each group.
  • AggPct - Percentile of values for each group.
  • AggSortedFirst - First value for each group after sorting by the sort column in ascending order.
  • AggSortedLast - Last value for each group after sorting by the sort column in ascending order.
  • AggStd - Sample standard deviation for each group.
  • AggSum - Sum of values for each group.
  • AggUnique - The single unique non-null value for each group, or null if the group has more than one distinct value or only nulls.
  • AggVar - Sample variance for each group.
  • AggWAvg - Weighted average for each group.
  • AggWSum - Weighted sum for each group.

Example data

The examples on this page use the following table of math and science test results for classes in periods 1 and 2. The Weight column holds the weight of each test in the final grade, which the weighted average example uses.

Apply a single aggregation

Although designed for multiple, simultaneous aggregations, aggBy can also apply a single aggregation. Unlike avgBy, which averages every column except the grouping columns, aggBy lets you choose the column to average and name the result, so it works even when other columns hold strings.

The following example uses aggBy to group and average the test scores in three ways:

  • By Period, to see if students perform better in one period or the other.
  • By Subject.
  • By Subject and Period, to see the average test score for each combination of subject and period.

Combine first and last values

This example finds the first and last test results for each subject and period. AggFirst returns the first test value, and AggLast returns the last test value. Grouping by Subject and Period produces four rows, one for each combination.

Combine weighted and unweighted averages with a count

In this example, each test carries a different weight in the final grade.

  • The weights are in the Weight column.
  • AggWAvg takes the weight column, Weight, as its first argument and the column to average, Test, as its second. It computes the weighted average test score into the WAvg column.
  • AggAvg computes the unweighted average test score into the Avg column.
  • AggCount has no input column. Its argument names the output column, NumTests, which holds the number of tests in each group.
  • aggBy groups the rows by Period and applies the three aggregations to each group.