Trajectory Data Warehouses - Mobility Data - page 69

4.4.1 OLAP Queries
We use a functional SQL-like query language for expressing OLAP queries.
This language, denoted by Q agg , is based on the well-known relational calculus
with aggregate functions proposed by Klug. We show next what a Q agg query
looks like, using our running example.
Query 4.1. “Give by zone the total number of episodes performed by diesel
cars in February 2011.”
SELECT z.number, nbrEpisodes
FROM Zone z
WHERE nbrEpisodes = COUNT( SELECT e.id
FROM Episode e, Car c, Time t, District d
WHERE e.car = c.id AND e.time = t.id
AND e.district = d.id AND d.zone = z.id
AND c.fuel = 'diesel' AND t.date >= 1/2/2011
AND t.date < 1/3/2011 )
For each zone, the inner query counts the number of trajectories in the zone
satisfying the conditions in the query, and the result is stored in the variable
nbrEpisodes . Notice that the inner query performs in the WHERE clause a dice
operator by selecting facts with diesel cars in February 2011. The only attribute
in the SELECT clause of the inner query is the identifier of the episodes. This
corresponds to a series of slice operators removing all dimensions associated
with the facts. Finally, the correlation between the inner and the outer queries
through districts performs a roll-up operator.
The query just presented involved the fact table Episode .Wegivenextan
example of an OLAP query involving the Trajectory dimension.
Query 4.2. “Give the average duration of trajectories that traversed the Lambrate
district in the last quarter of 2010.”
AVG( SELECT j.duration
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 ) )
Here, for each instance of the Trajectory dimension, the inner query verifies
that at least one episode of the trajectory is related to the Lambrate district and
occurred on the last quarter of 2010. Notice that the durations of the trajectories
are precomputed in the Trajectory dimension and therefore it is possible to