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
Join Aggregate Transform Definition
- Join Aggregate Transform Field Definition
Examples

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.