Databases Reference
In-Depth Information
5
4
6
1
7
8
8
2
For each event, we want to compute the sum of all the durations of the events that
precede that event. This sum is a running sum.
19.1.1 Solution
One approach is to use the Cartesian product of the table with itself. In this way, we can
access all records whose Event number precedes that of a given record. For instance, for
the record with Event number 5, we need access to the records with Event numbers 1
through 4. The Cartesian product provides us with these records.
Here is the SQL statement that does the job:
SELECT Running.Event, Sum(RunningCopy.Duration) AS StartTime
FROM Running, Running AS RunningCopy
WHERE (RunningCopy.Event < Running.Event)
GROUP BY Running.Event
The
FROM
clause creates the Cartesian product of the table with itself. The
WHERE
clause
restricts the records to those for which:
RunningCopy.Event < Running.Event
that is, to the records that provide information about the records
preceding
each record in
Table 19-1. Finally, we
GROUP
BY
Event and compute the sum of the durations.
The problem is that Cartesian products are very inefficient and use a lot of resources. (If
Table 19-1 has 100,000 rows, then the Cartesian product has 100,000 x 100,000 =
10,000,000,000 rows!)
A more efficient solution is to use a nested
SELECT
statement, that is, to use a
SELECT
statement within the main
SELECT
statement. Recall that this is permitted in Access SQL,
provided that the internal
SELECT
statement returns
at most one record
.
In the following SQL statement, note the use of table aliases, which are needed because
we must refer to Table 19-1 in two contexts:
SELECT R1.Event,
(SELECT SUM(R2.Duration) FROM Running As R2 WHERE R2.Event < R1.Event)
AS StartTime
FROM Running As R1
The internal SQL statement:
