Join Aggregate
Edit this pageThe 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. |
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 |
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.