Aggregating items in projections

An aggregate function calculates a single value over a window. If a select expression contains any aggregate functions, then references to the input item can appear only in the arguments to those aggregate functions. Any EPL expression can appear in the arguments to the function, but other aggregate functions may not. EPL provides several built-in aggregate functions and you can define additional ones. See Defining custom aggregate functions and Built-in aggregate functions.

In a select clause, when you do not specify a group by clause any aggregate functions in the projection operate on all values in the window. This is true even if you partitioned the window. To group the items in the window into one or more separate groups and to calculate an aggregate value for each group of items, use the group by clause. The syntax of the group by clause is as follows:

Each groupByExpr is an expression that returns a value of a comparable type. See Comparable types.

These expressions form the group key, which determines which group each output item is a part of. Any aggregate functions in the select expression operate over each group separately.

In an aggregate projection, you can refer to any group key expressions anywhere in the select expression. However, you can refer to a query input item only in an aggregate function argument. For example:

from t in all Tick() within 30.0
group by t.symbol select TickAverage(t.symbol, mean(t.price));

Whenever a lot arrives, this query updates one or more groups. Every group that is updated outputs a TickAverage event, and all TickAverage events are in the same lot. Each TickAverage event contains the symbol and the average price for that symbol over the last thirty seconds. If a group is not updated, it does not output a TickAverage event.

You typically use a group by clause in a stream query in conjunction with a partition by clause. In the following example, the window contains up to 10 events for each stock symbol. The aggregate projection calculates the average price separately for each symbol and each average is based on up to 10 events:

For each query, there are items that have been added to the window in a given query activation and items that have been removed (they were previously in the window, but are no longer in the window). By default, a simple, non-aggregate projection returns the items that have been added to the window. This is the insert stream (istream). To obtain the items that have been removed from the window, add the rstream keyword to the select clause.

For aggregate projections, obtaining the remove stream is not meaningful, and therefore the rstream keyword is not allowed in aggregate projections.