Database Reference
In-Depth Information
apply the average function to them. If the durations of the whole trajectories
must be calculated, then the query would be as follows.
AVG( SELECT totDuration
FROM Trajectory j
WHERE EXISTS ( SELECT *
FROM Episode e, District d, Time t
WHERE e.trajectory=j.id AND e.district=d.id
AND e.time=t.id AND d.name='Lambrate'
AND t.date >= 1/10/2010 AND t.date <= 31/12/2010 )
AND totDuration = SUM( SELECT e.duration
FROM Episode e WHERE e.trajectory=j.id ) )
As can be seen in the examples above, an OLAP query is just a relational
calculus query with aggregation.
To characterize OLAP queries we consider a set of
base types
, namely
int
,
real
,
bool
,and
string
, with the usual interpretation, except that their value
may be undefined. In addition, we define an
identifier type
id
(introduced
in the examples above), which is used to identify dimension level members.
There are also
time types
, which are
instant
and
periods
, the latter being
a set of time intervals. Finally, there is a type constructor
range
(
α
), where
α
, which yields sets of intervals over
α
. Thus, the type
periods
is just a shorthand notation for
range(instant)
.
Base and time types have an associated set of operations, defined in Chapter
3
.
It can be proved that the language
∈{
int
,
string
,
bool
,
real
,
instant
}
Q
agg
, defined over the sets of base and time
types, has the same expressive power of the relational calculus extended with
aggregate functions. Based on this, it follows that the class of OLAP queries
includes all the queries that are expressible by
Q
agg
.
Therefore, a
data warehouse
is a data repository that supports OLAP queries.
4.4.2 Spatial OLAP
We consider now the
spatial data types
point
,
points
,
line
,and
region
,
with their associated operations. For example, the predicate
inside
can be used
to test whether a point is inside a region. To express the following query, we
need to extend
Q
agg
with spatial data types.
Query 4.3. “For roads intersecting the Lambrate district, give the number of
trajectories in the last quarter of 2010.”
SELECT r.name, nbTrajs
FROM Road r, District d
WHERE d.name='Lambrate'
AND intersects(r.geometry,d.geometry)
