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:
With a view definition that has a Projection
clause and Grouping
clause like the following:
AMPS will produce the following record:
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:
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