Aggregating query results for dashboards within AQL

With this feature, queries generating a series of records (e.g. all interruptions for a given machine yesterday) can be summarised so that the output is immediately useful for display in a UI or on a dashboard:

FROM 'machine' & 'machine:4711'
FILTER _.status = 'interrupted'
AGGREGATE {
  count: SUM(1)
  total: SUM(_.duration)
  first: FIRST(_.start)
  last: LAST(_.end)
}
SELECT {
  average: _.total / _.count
  details: _
}

The last step illustrates that aggregation results can also be further transformed (and it points out that a ...spread operator like in JS might come in handy … ).

Specification

The syntax for the new transformation stage is

AGGREGATE <expr>

Within the given expression _ is not bound because there is no single event it could stand for. Instead, new keywords are introduced to compute aggregations:

  • SUM(<expr>) evaluates the expression for all inputs and adds the resulting values together — this implies that the expression needs to evaluate to a number
  • FIRST(<expr>) yields the expression result for the earliest input where the expression does return a result
  • LAST(<expr>) analogously returns the latest result — determined by event key sort order
  • MIN(<expr>) applies the expression to all inputs and takes the minimum; this is defined for all data types, but the result of this operator is undefined if the expression doesn’t evaluate to the same type for all inputs
  • MAX(<expr>) analogously computes the maximum; note that TRUE > FALSE, so these operators can be used to model “forall” and “exists” conditionals

Within the <expr> in all operators described above, _ is bound to the current input. The output of the AGGREGATE stage is a single value computed from the given expression after all its aggregation operators have returned their results.