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

Documentation Overview
Join Aggregate Field Definition
Join Aggregate Transform Definition
- Join Aggregate Transform Field Definition
Examples
- Percent of Total
- Difference from Mean

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.
frame	Any
ignorePeers	Any
groupby	FieldName[]	The data fields for partitioning the data objects into separate groups. If unspecified, all data points will be in a single group.
sort	Any

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	FieldName	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	FieldName	*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.