Aggregate Functions

AMPS provides a set of aggregation functions that can be used in a Field constructor for a view and in the projection option of an aggregated subscription. These functions return a single value for each distinct group of messages, as identified by distinct combinations of values in the Grouping clause.

Each of these functions takes a single value as an argument. That value is typically provided by a single message within a group. There are no special limitations on the value, and the value can be a literal value, an identifier directing AMPS to extract the value from the message, or a function.

For example, given a set of messages like the following:

{"id":1, "item":1,"qty":10, "oid":1, ...}
{"id":2, "item":2,"qty":10, "oid":1, ...}
{"id":3, "item":3,"qty":25, "oid":1, ...}

With a view definition that has a Projection clause and Grouping clause like the following:

<Projection>
   <Field>/oid</Field>
   <Field>SUM(/qty) AS /totalOrderQty</Field>
   <Field>SUM(IF((/qty % 10) == 0,1,0)) AS /evenOrderCount</Field>
</Projection>
<Grouping>
   <Field>/oid</Field>
</Grouping>

AMPS will produce the following record:

{"oid":1,"totalOrderQty":45,"evenOrderCount":2}

Notice that the first SUM() function simply extracts the value of the /qty from each message, while the second SUM() function uses the output of the IF statement for each message.

Since aggregate functions operate over groups of messages, these functions are only available when constructing fields for aggregate purposes, either in a view or an aggregated subscription. The functions described in this section are not available to filters, and are not available for constructing fields during SOW topic enrichment.

The set of functions provided in AMPS have been chosen to be efficient to compute over high volumes of rapidly changing data.

Null values are not included in aggregate expressions with AMPS, nor in ANSI SQL. COUNT will count only non-null values, SUM will add only non-null values, AVG will average only non-null values, and MIN and MAX ignore NULL values, and so on.

MIN and MAX can operate on either numbers or strings, or a combination of the two. AMPS compares values using the principles described for comparison operators. For MIN and MAX, AMPS determines order based on these rules:

  • Numbers sort in numeric order.

  • String values sort in ASCII order.

  • When comparing a number to a string, convert the string to a number, and use a numeric comparison. If that is not successful, the value of the string is higher than the value of the number.

For example, given a field that has the following values across a set of messages:

24, 020, 'cat', 75, 1.3, 200, '75', '42'

MIN will return 1.3, MAX will return 'cat'. Notice that different message types may have different support for converting strings to numeric values: AMPS relies on the parsing done by the message type to determine the numeric value of a string.

Last updated

Copyright 2013-2024 60East Technologies, Inc.