Aggregate Functions for Temporal Types

The temporal aggregate functions generalize the traditional aggregate functions. Their semantics is that they compute the value of the function at every instant t in the union of the temporal extents of the values to aggregate.

For all temporal types, the function tcount generalize the traditional function count. The temporal count can be used to compute at each point in time the number of available or reporting objects (for example, number of cars in an area). For Boolean types, the functions tand and tor generalize the traditional functions and and or. For numeric types, two types of temporal aggregate functions are available. The functions tmin, tmax, tsum, and tavg generalize the traditional functions min, max, sum, and avg. Furthermore, the functions wmin, wmax, wcount, wsum, and wavg are window (or cumulative) versions of the traditional functions that, given a time interval w, compute the value of the function at an instant t by considering the values during the interval [t-w, t]. All window aggregate functions are available for temporal integers, while for temporal floats only window minimum and maximum are meaningful. For temporal text, the functions Finally, for temporal points the function tcentroid, generalizes the corresponding function ST_Centroid provided by PostGIS. For example, given set of objects that move together (that is, a convoy or a flock) the temporal centroid will produce a temporal point that represents at each instant the geometric center (or the center of mass) of all the moving objects.

In addition to the above, function extent returns a bounding box that encloses a set of temporal values. Depending on the base type, the result of this function can be a period, a tbox or an stbox. This function is an “aggregate” function in SQL terminology since it operates on lists of data, in the same way the SUM() and AVG() functions do.

In the examples that follow, we suppose the tables Department and Trip contain the two tuples introduced in the section called “Examples of Temporal Types”.