Skip to main content
Version: Java (Groovy)

Multi-aggregation

This guide will show you how to collect summary information for groups of data using combined aggregations.

Often when working with data, you will want to break the data into subgroups and then perform calculations on the grouped data. For example, a large multi-national corporation may want to know their average employee salary by country, or a teacher might want to analyze test scores for various classes.

The process of breaking a table into subgroups and then performing one or more calculations on the subgroups is known as "combined aggregation." The term comes from most operations creating a summary of data within a group (aggregation), and from more than one operation being computed at once (combined).

Why use combined aggregations?​

Deephaven provides many dedicated aggregations, such as maxBy and minBy. These are good options if only one type of aggregation is needed. If more than one aggregation is needed or if you have a custom aggregation, combined aggregations are a more efficient and more flexible solution.

Syntax​

Aggregators are applied to data by the aggBy method:

The general syntax follows:

import static io.deephaven.api.agg.Aggregation.AggAvg
import static io.deephaven.api.agg.Aggregation.AggLast

agg_list = [
    AggAvg(sourceColumns),                 // first aggregation
    AggLast("inputColumn = outputColumn")  // second aggregation
]

result = source.aggBy(agg_list, groupingColumns...) // apply the aggregations to data .aggBy

What aggregations are available?​

A number of built-in aggregations are available:

  • AggAbsSum - Sum of absolute 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.
  • AggDistinct - Array of unique values for each group.
  • AggFirst - First value for each group.
  • AggFormula - Custom formula for each group.
  • AggGroup - Array 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 - Creates partition for the aggregation group.
  • AggPct - Percentile of values for each group.
  • AggSortedFirst - First value of each column within an aggregation group, sorted.
  • AggSortedLast - Last value of each column within an aggregation group, sorted.
  • AggStd - Standard deviation for each group.
  • AggSum - Sum of values for each group.
  • AggUnique - Returns one single value for a column, or a default.
  • AggVar - Variance for each group.
  • AggWAvg - Weighted average for each group.
  • AggWSum - Weighted sum for each group.

Example 1​

In this example, we have math and science test results for classes during periods 1 and 2. We want to summarize this information to see if students perform better in one period or the other.

Although designed for multiple, simultaneous aggregations, Aggregation can also be used for a single aggregation. In this first example, we group and average the test scores by Period.

import static io.deephaven.api.agg.Aggregation.AggAvg

source = newTable(
    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),
    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),
    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),
)

result = source.aggBy([AggAvg("AVG = Test")], "Period")

The data can also be grouped and averaged by Subject.

import static io.deephaven.api.agg.Aggregation.AggAvg

source = newTable(
    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),
    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),
    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),
)

result = source.aggBy([AggAvg("AVG = Test")], "Subject")

We can also group the data by Subject and Period to see the total average in a period and subject.

import static io.deephaven.api.agg.Aggregation.AggAvg

source = newTable(
    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),
    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),
    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),
)

result = source.aggBy([AggAvg("AVG = Test")], "Subject", "Period")

Example 2​

In this example, we want to know the first and last test results for each subject and period. To achieve this, we can use AggFirst to return the first test value and AggLast to return the last test value. The results are grouped by Subject and Period, so there are four results in this example.

import static io.deephaven.api.agg.Aggregation.AggFirst
import static io.deephaven.api.agg.Aggregation.AggLast

source = newTable(
    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),
    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),
    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),
)

agg_list = [
    AggFirst("FirstTest = Test"),
    AggLast("LastTest = Test")
]

result = source.aggBy(agg_list, "Subject", "Period")

Example 3​

In this example, tests are weighted differently in computing the final grade.

  • The weights are in the Weight column.
  • AggWAvg is used to compute the weighted average test score, stored in the WAvg column.
  • AggAvg is used to compute the unweighted average test score, stored in the Avg column.
  • AggCount is used to compute the number of tests in each group.
  • Test results are grouped by Period.
import static io.deephaven.api.agg.Aggregation.AggWAvg
import static io.deephaven.api.agg.Aggregation.AggAvg
import static io.deephaven.api.agg.Aggregation.AggCount

source = newTable(
    stringCol("Period", "1", "2", "2", "2", "1", "2", "1", "2", "1"),
    stringCol("Subject", "Math", "Math", "Math", "Science", "Science", "Science", "Math", "Science", "Math"),
    intCol("Test", 55, 76, 20, 90, 83, 95, 73, 97, 84),
    intCol("Weight", 1, 2, 1, 3, 2, 1, 4, 1, 2),
)

agg_list = [
    AggWAvg("Weight", "WAvg = Test"),
    AggAvg("Avg = Test"),
    AggCount("NumTests")
]

result = source.aggBy(agg_list, "Period")