Aggregate

Overview

Aggregating combines all records for each unique join-key on a foreign table in some manner, and creates that entity as a field on the primary side of the join.

Consider the relationship above, each customer may have multiple transactions on the transactions table.

Here, the customer with id 1000 has 3 transactions.

We can create a field on the customer table for total profit above with the definition above. This is a sum aggregation.

The JSON look like

{
  "method": "BuildAggregate",
  "project": "MyProject",
  "targetTable": "customer",
  "source": "transactions.profit",
  "overwrite": true,
  "name": "total profit",
  "function": "sum"
}

function can be count, min, max, avg or sum

The transactions table can be pre-filtered with a DataSet, only those transactions included in this DataSet will contribute to the calculation.

So setting a DataSet of product-id like "G*" for a sum aggregate called "G product profit"

{
  "method": "BuildAggregate",
  "targetTable": "customer",
  "source": "transactions.profit",
  "overwrite": true,
  "name": "G product profit",
  "function": "sum",
  "project": "MyProject",
  "dataSet": {
    "logic": "or",
    "name": "DataSet",
    "strict": true,
    "set": [
      {
        "logic": "and",
        "stype": "FIELD",
        "entity": {
          "type": "field",
          "name": "transactions.product-id",
          "table": "transactions",
          "valueFilter": []
        },
        "op": "like",
        "values": [
          "G*"
        ]
      }
    ]
  }
}

Aggregates can be combined with other engineering functions to product other useful entities on the primary table.

For Example, if we create a "first purchase" and a "last purchase" field on the customer table, by performing a min and max aggregate on the transaction date

we can use an Expression Field "[last purchase]-[first purchase]" to produce a lifetime in days.

This could be divided by the number of transactions (count aggregate) to produce a "frequency of purchase" field.