Aggregates

Most DataBlade® module functions operate on single values and return one result for each time they are called. Aggregates, however, are functions that are called repeatedly, with different values, and collect their results until no more arguments are supplied.

An example of an aggregate is the built-in AVG aggregate. This aggregate computes the average value of all its arguments. For example, an SQL user could issue the following query:

SELECT AVG(population) FROM cities;

The query processing engine calls the supporting function for AVG repeatedly with population values from the cities table. After all the populations have been passed to AVG one at a time, it returns the average population. You can extend the aggregates that are built into the database server by overloading their operator functions for an extended data type. For more information, see the HCL OneDB™ DataBlade API Programmer's Guide.

You can define new aggregates that implement user-defined functions. For example, one common spatial operation is to compute a minimum bounding rectangle that contains a collection of other rectangles. A user might write the following query by using a user-defined aggregate called BOUNDING:

SELECT BOUNDING(boundary) FROM cities;

The BOUNDING aggregate takes all the polygons, one at a time, from the cities table and returns the smallest rectangle that contains them all. The query processing engine supplies records to the aggregate for computation; the aggregate only collects information over the arguments it is passed.

Like ordinary functions, aggregates might appear anywhere in the query, including in the target list and the qualification. Aggregates in the qualification are most useful in queries that also do grouping.

If you have a data type over which summary or statistical analyses are valuable, consider defining an aggregate.

When you design a DataBlade module, ask yourself the following question: Is it useful to compute a summary over values that the DataBlade module supports?