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.

These functions produce an aggregation over a literal value, an identifier directing AMPS to extract the value from the message, or the result of 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 /evenTensOrderCount</Field>
</Projection>
<Grouping>
   <Field>/oid</Field>
</Grouping>

AMPS will produce the following record:

{"oid":1,"totalOrderQty":45,"evenTensOrderCount":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.

Function	Description
AVG	Average over an expression. Returns the mean value of the values specified by the expression.
ANY	Returns one of the set of values in the expression.
COUNT	Count of values in an expression. Returns the number of values specified by the expression.
COUNT_DISTINCT	Count of the number of distinct values in an expression, ignoring NULL. Returns the number of distinct values in the expression. AMPS type conversion rules apply when determining distinct values.
GROUP_CONCAT	Create a list of the distinct values in the expression specified, using the second argument as the delimiter. If no second argument is provided, the delimiter defaults to , (a comma). For example, to create a list of the distinct values in the /names column for the group delimited by a | character, you would use: GROUP_CONCAT(/names, '|') This function does not guarantee the order of the values within the string produced. AMPS type conversion rules apply when determining distinct values. This function returns a string, regardless of the types of the values in the expression.
MIN	Minimum value. Returns the minimum out of the values specified by the expression.
MAX	Maximum value. Returns the maximum out of the values specified by the expression.
STDDEV_POP	Population standard deviation of an expression. Returns the calculated standard deviation.
STDDEV_SAMP	Sample standard deviation of an expression. Returns the calculated standard deviation.
SUM	Summation over an expression. Returns the total value of the values specified by the expression.
UNIQUE	Determine if all of the values in a given field match within the group. If all of the values match, returns the value. Otherwise, returns NULL.

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.