Aggregate Functions

Aggregate functions operate on multiple records to calculate one value from a group of values.

The groups or rows are formed using the GROUP BY clause of the SELECT statement. The GROUP FILTER and GROUP ORDER BY clauses are used in conjunction with the GROUP BY clause to limit the rows in the group and to order the rows in the group respectively.

Aggregate functions, such as sum(), min() etc are allowed only in the select list and in the HAVING clause of a SELECT statement. Aggregate functions cannot be specified in the GROUP BY, GROUP ORDER BY, GROUP FILTER and WHERE clauses of the SELECT statement.

All aggregate functions ignore NULL values when performing their aggregate calculations. However, when all input passed to an aggregate function is NULL the function returns a NULL except for the count() function, which returns a 0.

Certain aggregate functions namely count(), sum(), avg() and valueInserted() are considered additive functions.  Additive functions can compute its value only based upon the column values in the current event without having to look at the rest of the events in the group.  A projection that uses ONLY additive functions allows the server to optimize the aggregation so that additional aggregation indexes are not maintained.  This improves the performance of the aggregation operation considerably.

Note: Aggregate functions cannot be nested i.e. an aggregate function cannot be applied over an expression containing another aggregate function.

Example

In general, the following example shows how the aggregate functions are incorporated into CCL code:

 CREATE INPUT WINDOW Trades 
SCHEMA (TradeId LONG, Symbol, STRING, Price FLOAT, Volume LONG, TradeTime DATE) 
PRIMARY KEY (TradeId);     

CREATE OUTPUT WINDOW
TradeSummary PRIMARY KEY DEDUCED 
AS SELECT trd.Symbol,  max(trd.Price) MaxPrice, min(trd.Price) MinPrice, sum(trd.Volume) 
TotalVolume FROM Trades trd 
GROUP BY trd.Symbol;
Parent topic: CCL Functions