1.5.2 Aggregate Functions

SQL standard functions

The table below shows the SQL standard aggregate functions that can be used by the complex event processing language.

Function	Syntax	Explanation
AVEDEV	avedev([all \| distinct] expression)	Returns a double value showing the mean deviation of the expression value.
AVG	avg([all \| distinct] expression)	Returns a double value showing the expression value average.
COUNT	count([all \| distinct] expression)	Returns a long value showing the number of expression values that are not null.
COUNT	count(*)	Returns a long value showing the number of events.
MAX	max([all \| distinct] expression)	Returns the maximum value of the expression value.
MEDIAN	median([all \| distinct] expression)	Returns a double value showing the expression value median value. Non-numeric values (Not-a-Number: NaN) are ignored when calculating the median value.
MIN	min([all \| distinct] expression)	Returns the minimum value of the expression value.
STDDEV	stddev([all \| distinct] expression)	Returns a double value showing the standard deviation of the expression value.
SUM	sum([all \| distinct] expression)	Returns the sum of the expression values.

If distinct is specified, duplicated values are not included in calculations.

Data window aggregate functions

The table below shows the aggregate functions for data windows.

Function	Syntax	Explanation
FIRST	first (* \| eventStreamName.* \| valueExpression [, indexExpression])	Returns the property of the first event in the data window, in event arrival sequence, or the evaluation value at valueExpression. If multiple event streams are joined or if subqueries are included, use eventStreamName to specify the event stream for which you want properties returned. If indexExpression is specified, the property of the event at the expression value position, counting from the first event, is returned.
LAST	last(* \| eventStreamName.* \| valueExpression [,indexExpression])	Returns the property of the most recent event in the data window, in event arrival sequence, or the evaluation value at valueExpression. If multiple event streams are joined or if subqueries are included, use eventStreamName to specify the event stream for which you want properties returned. If indexExpression is specified, the property of the event at the expression value position, counting from the most recent event, is returned.
WINDOW	window(* \| eventStreamName.* \| valueExpression)	Returns the properties of all events in the data window, or the evaluation value at valueExpression. If multiple event streams are joined or if subqueries are included, use eventStreamName to specify the event stream for which you want properties returned.

Function

Syntax

Explanation

FIRST

first (* | eventStreamName.* | valueExpression [, indexExpression])

Returns the property of the first event in the data window, in event arrival sequence, or the evaluation value at valueExpression. If multiple event streams are joined or if subqueries are included, use eventStreamName to specify the event stream for which you want properties returned.

If indexExpression is specified, the property of the event at the expression value position, counting from the first event, is returned.

LAST

last(* | eventStreamName.* | valueExpression [,indexExpression])

Returns the property of the most recent event in the data window, in event arrival sequence, or the evaluation value at valueExpression. If multiple event streams are joined or if subqueries are included, use eventStreamName to specify the event stream for which you want properties returned.

If indexExpression is specified, the property of the event at the expression value position, counting from the most recent event, is returned.

WINDOW

window(* | eventStreamName.* | valueExpression)

Returns the properties of all events in the data window, or the evaluation value at valueExpression. If multiple event streams are joined or if subqueries are included, use eventStreamName to specify the event stream for which you want properties returned.

The differences between the FIRST, LAST, and WINDOW aggregate functions and the PREVTAIL, PREV, and PREVWINDOW functions are that aggregate functions can operate using GROUP BY, and that the aggregate functions are based on the event arrival sequence rather than the sort sequence.