Join Aggregate

Edit this page

The joinaggregate transform extends the input data objects with aggregate values in a new field. Aggregation is performed and the results are then joined with the input data. This transform can be helpful for creating derived values that combine both raw data and aggregate calculations, such as percentages of group totals. This transform is a special case of the window transform where the frame is always [null, null]. Compared with the regular aggregate transform, joinaggregate preserves the original table structure and augments records with aggregate values rather than summarizing the data in one record for each group.

Documentation Overview

Join Aggregate Field Definition

// Any View Specification
{
  ...
  "transform": [
    {
      // Join Aggregate Transform
      "joinaggregate": [{
          "op": ...,
          "field": ...,
          "as": ...
      }],
      "groupby": [
        "..."
      ]
    }
     ...
  ],
  ...
}

Join Aggregate Transform Definition

Property Type Description
joinaggregate JoinAggregateFieldDef[]

Required. The definition of the fields in the join aggregate, and what calculations to use.
groupby String[]

The data fields for partitioning the data objects into separate groups. If unspecified, all data points will be in a single group.

Join Aggregate Transform Field Definition

Property Type Description
op String

Required. The aggregation operation to apply (e.g., "sum", "average" or "count"). See the list of all supported operations here.
field String

The data field for which to compute the aggregate function. This can be omitted for functions that do not operate over a field such as "count".
as String

Required. The output name for the join aggregate operation.

Examples

Below are some common use cases for the join aggregate transform.

Percent of Total

Here we use the join aggregate transform to derive the global sum so that we can calculate percentage.

Difference from Mean

One example is to show the “exemplar” movies from a movie collection. Here “exemplar” is defined by having a score of 2.5 points higher than the global average.

Another example is to show the “exemplar” movies based on the release year average. Here “exemplar” is defined by having a score 2.5 points higher than the annual average for its release year (instead of the global average).

Rather than filtering the above two examples we can also calculate a residual by deriving the mean using the join aggregate transform first.

Text Color with Contrast

Here, we layer text on a table heatmap. The text is black or white depending on the values of num_cars. One issue with this specification is that we have to know the range of num_cars ahead of time to determine a suitable threshold (e.g. 40).

We can use a joinaggregate with a calculate to determine the threshold dynamically.