Table of Contents
The temporal aggregate functions generalize the traditional aggregate functions. Their semantics is that they compute the value of the function at every instant in the union of the temporal extents of the values to aggregate. In contrast, recall that all other functions manipulating temporal types compute the value of the function at every instant in the intersection of the temporal extents of the arguments.
The temporal aggregate functions are the following ones:
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 objects (for example, number of cars in an area).
For all temporal types, 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 tstzspan
, a tbox
or an stbox
.
For the temporal Boolean type, the functions tAnd
and tOr
generalize the traditional functions and
and or
.
For temporal numeric types, there are two types of temporal aggregate functions. 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 the temporal text type, the functions tMin
y tMax
generalize the traditional functions min
and max
.
Finally, for temporal point types, the function tCentroid
generalizes the 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 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) → {tintSeq,tintSeqSet}
SELECT tCount(NoEmps) FROM Department; -- {[1@2001-01-01, 2@2001-02-01, 1@2001-08-01, 1@2001-10-01)}
Bounding box extent
extent(temp) → {tstzspan,tbox,stbox}
SELECT extent(noEmps) FROM Department; -- TBOX XT((4,12),[2001-01-01,2001-10-01]) SELECT extent(Trip) FROM Trips; -- STBOX XT(((0,0),(3,3)),[2001-01-01 08:00:00+01, 2001-01-01 08:20:00+01))
Temporal and
tAnd(tbool) → tbool
SELECT tAnd(NoEmps #> 6) FROM Department; -- {[t@2001-01-01, f@2001-04-01, f@2001-10-01)}
Temporal or
tOr(tbool) → tbool
SELECT tOr(NoEmps #> 6) FROM Department; -- {[t@2001-01-01, f@2001-08-01, f@2001-10-01)}
Temporal minimum
tMin(ttype) → ttype
SELECT tMin(NoEmps) FROM Department; -- {[10@2001-01-01, 4@2001-02-01, 6@2001-06-01, 6@2001-10-01)}
Temporal maximum
tMax(ttype) → ttype
SELECT tMax(NoEmps) FROM Department; -- {[10@2001-01-01, 12@2001-04-01, 6@2001-08-01, 6@2001-10-01)}
Temporal sum
tSum(tnumber) → {tnumberSeq,tnumberSeqSet}
SELECT tSum(NoEmps) FROM Department; /* {[10@2001-01-01, 14@2001-02-01, 16@2001-04-01, 18@2001-06-01, 6@2001-08-01, 6@2001-10-01)} */
Temporal average
tAvg(tnumber) → {tfloatSeq,tfloatSeqSet}
SELECT tAvg(NoEmps) FROM Department; /* {[10@2001-01-01, 10@2001-02-01), [7@2001-02-01, 7@2001-04-01), [8@2001-04-01, 8@2001-06-01), [9@2001-06-01, 9@2001-08-01), [6@2001-08-01, 6@2001-10-01) */
Window minimum
wMin(tnumber,interval) → {tnumberSeq,tnumberSeqSet}
SELECT wMin(NoEmps, interval '2 days') FROM Department; -- {[10@2001-01-01, 4@2001-04-01, 6@2001-06-03, 6@2001-10-03)}
Window maximum
wMax(tnumber,interval) → {tnumberDiscSeq,tnumberSeqSet}
SELECT wMax(NoEmps, interval '2 days') FROM Department; -- {[10@2001-01-01, 12@2001-04-01, 6@2001-08-03, 6@2001-10-03)}
Window count
wCount(tnumber,interval) → {tintSeq,tintSeqSet}
SELECT wCount(NoEmps, interval '2 days') FROM Department; /* {[1@2001-01-01, 2@2001-02-01, 3@2001-04-01, 2@2001-04-03, 3@2001-06-01, 2@2001-06-03, 1@2001-08-03, 1@2001-10-03)} */
Window sum
wSum(tint,interval) → {tintSeq,tintSeqSet}
SELECT wSum(NoEmps, interval '2 days') FROM Department; /* {[10@2001-01-01, 14@2001-02-01, 26@2001-04-01, 16@2001-04-03, 22@2001-06-01, 18@2001-06-03, 6@2001-08-03, 6@2001-10-03)} */
Window average
wAvg(tint,interval) → {tfloatSeq,tfloatSeqSet}
SELECT wAvg(NoEmps, interval '2 days') FROM Department; /* {[10@2001-01-01, 10@2001-02-01), [7@2001-02-01, 7@2001-04-01), [8.66666666666667@2001-04-01, 8.66666666666667@2001-04-03), [8@2001-04-03, 8@2001-06-01), [7.33333333333333@2001-06-01, 7.33333333333333@2001-06-03), [9@2001-06-03, 9@2001-08-03), [6@2001-08-03, 6@2001-10-03)} */
Temporal centroid
tCentroid(tgeompoint) → tgeompoint
SELECT tCentroid(Trip) FROM Trips; /* {[POINT(0 0)@2001-01-01 08:00:00+00, POINT(1 0)@2001-01-01 08:05:00+00), [POINT(0.5 0)@2001-01-01 08:05:00+00, POINT(1.5 0.5)@2001-01-01 08:10:00+00, POINT(2 1.5)@2001-01-01 08:15:00+00), [POINT(2 2)@2001-01-01 08:15:00+00, POINT(3 3)@2001-01-01 08:20:00+00)} */