Trajectory Data Warehouses - Data Warehouse Systems: Design and Implementation - page 490

In the query above, we use the global Temperature field shown in Fig. 12.8 .
Otherwise, we would need to make the union of the fields in attribute Temp
of level State for all states traversed by the truck. Function AtMGeometry
projects the temporal field to the movement track of the segment, resulting
in a temporal real. In other words, the function computes the position of
the trajectory segment at each time instant, and then from the field valid at
that instant, it obtains the temperature. Finally, function TMax obtains the
maximum temperature value during the segment.

Our next example query involves field aggregation.

Query 12.8. Average temperature by month and by state.
Here, we need an auxiliary function that, given a month and a year, returns
the period composed of all the days of the month. The function, denoted
PeriodMonth , is defined as follows:
CREATE OR REPLACE FUNCTION PeriodMonth(Month int, Year int)
RETURNS PERIOD AS $PeriodMonth$
DECLARE
PerStart CHAR(10);
PerEnd CHAR(10);
BEGIN
PerStart = CAST($2 as CHAR(4)) || ' - ' || CAST($1 as CHAR(2)) || ' -01 ' ;

IF $1 < 12 THEN
PerEnd = CAST($2 as CHAR(4)) || ' - ' || CAST($1+1 as CHAR(2)) || ' -01 ' ;

ELSE
PerEnd = CAST($2+1 as CHAR(4)) || ' -01-01 '

END IF;
RETURN PERIOD(PerStart,PerEnd);
END;
$PeriodMonth$ LANGUAGE plpgsql;
Then, the query is as follows:

WITH Month AS (
SELECT DISTINCT MonthNo, Year
FROM Time T )
SELECT S.StateName, M.MonthNo, M.Year,
FAvg(Avg S(AtPeriod(S.Temp,PeriodMonth(M.MonthNo, M.Year))))
FROM State S, Month M

Here, it is supposed that the period of time covered by the time dimension
is the same as the one in which the temperature temporal field is defined. In
the query, a temporary table Month is defined in the WITH clause containing
all months of the Time dimension. The main query starts by combining each
state with each month. Then, the temperature field of the state is projected
to the corresponding month with function AtPeriod . Function Avg S is then
used to compute the average of the temperature values during the month
at each point in the state, resulting in a nontemporal field. Finally, function
FAvg obtains the average temperature over the nontemporal field, which is
areal.