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”.
Temporal count
tcount(ttype): {tinti, tints}
SELECT tcount(NoEmps) FROM Department;
-- "{[1@2012-01-01, 2@2012-02-01, 1@2012-08-01, 1@2012-10-01)}"
Temporal and
tand(tbool): tbool
SELECT tand(NoEmps #> 6) FROM Department;
-- "{[t@2012-01-01, f@2012-04-01, f@2012-10-01)}"
Temporal or
tor(tbool): tbool
SELECT tor(NoEmps #> 6) FROM Department;
-- "{[t@2012-01-01, f@2012-08-01, f@2012-10-01)}"
Temporal minimum
tmin(ttype): {ttypei, ttypes}
SELECT tmin(NoEmps) FROM Department;
-- "{[10@2012-01-01, 4@2012-02-01, 6@2012-06-01, 6@2012-10-01)}"
Temporal maximum
tmax(ttype): {ttypei, ttypes}
SELECT tmax(NoEmps) FROM Department;
-- "{[10@2012-01-01, 12@2012-04-01, 6@2012-08-01, 6@2012-10-01)}"
Temporal sum
tsum(tnumber): {tnumi, tnums}
SELECT tsum(NoEmps) FROM Department;
-- "{[10@2012-01-01, 14@2012-02-01, 16@2012-04-01, 18@2012-06-01, 6@2012-08-01,
6@2012-10-01)}"
Temporal average
tavg(tnumber): {tfloati, tfloats}
SELECT tavg(NoEmps) FROM Department;
-- "{[10@2012-01-01, 10@2012-02-01), [7@2012-02-01, 7@2012-04-01),
[8@2012-04-01, 8@2012-06-01), [9@2012-06-01, 9@2012-08-01),
[6@2012-08-01, 6@2012-10-01)"
Window minimum
wmin(tnumber, interval): {tnumi, tnums}
SELECT wmin(NoEmps, interval '2 days') FROM Department;
-- "{[10@2012-01-01, 4@2012-04-01, 6@2012-06-03, 6@2012-10-03)}"
Window maximum
wmax(tnumber, interval): {tnumi, tnums}
SELECT wmax(NoEmps, interval '2 days') FROM Department;
-- "{[10@2012-01-01, 12@2012-04-01, 6@2012-08-03, 6@2012-10-03)}"
Window count
wcount(tnumber, interval): {tinti, tints}
SELECT wcount(NoEmps, interval '2 days') FROM Department;
-- "{[1@2012-01-01, 2@2012-02-01, 3@2012-04-01, 2@2012-04-03, 3@2012-06-01, 2@2012-06-03,
1@2012-08-03, 1@2012-10-03)}"
Window sum
wsum(tint, interval): {tinti, tints}
SELECT wsum(NoEmps, interval '2 days') FROM Department;
-- "{[10@2012-01-01, 14@2012-02-01, 26@2012-04-01, 16@2012-04-03, 22@2012-06-01,
18@2012-06-03, 6@2012-08-03, 6@2012-10-03)}"
Window average
wavg(tint, interval): {tfloati, tfloats}
SELECT wavg(NoEmps, interval '2 days') FROM Department;
-- "{[10@2012-01-01, 10@2012-02-01), [7@2012-02-01, 7@2012-04-01),
[8.66666666666667@2012-04-01, 8.66666666666667@2012-04-03),
[8@2012-04-03, 8@2012-06-01),
[7.33333333333333@2012-06-01, 7.33333333333333@2012-06-03),
[9@2012-06-03, 9@2012-08-03), [6@2012-08-03, 6@2012-10-03)}"
Temporal centroid
tcentroid(tgeompoint): tgeompoint
SELECT tcentroid(Trip) FROM Trips;
-- "{[POINT(0 0)@2012-01-01 08:00:00+00, POINT(1 0)@2012-01-01 08:05:00+00),
[POINT(0.5 0)@2012-01-01 08:05:00+00, POINT(1.5 0.5)@2012-01-01 08:10:00+00,
POINT(2 1.5)@2012-01-01 08:15:00+00),
[POINT(2 2)@2012-01-01 08:15:00+00, POINT(3 3)@2012-01-01 08:20:00+00)}"
Bounding box extent
extent(temp): {period, tbox, stbox}
SELECT extent(noEmps) FROM Department; -- "TBOX((4,2012-01-01 00:00:00+01),(12,2012-10-01 00:00:00+02))" SELECT extent(Trip) FROM Trips; -- "STBOX T((0,0,2012-01-01 08:00:00+01),(3,3,2012-01-01 08:20:00+01))"