Database Reference
In-Depth Information
in that it calculates cumulative sums and running averages, but performs the compu‐
tations yet another way.
The following table shows a marathon runner's performance at each stage of a 26-
kilometer run. The values in each row show the length of each stage in kilometers and
how long the runner took to complete the stage. In other words, the values pertain to
intervals within the marathon and thus are relative to the whole:
mysql>
SELECT stage, km, t FROM marathon ORDER BY stage;
+-------+----+----------+
| stage | km | t |
+-------+----+----------+
| 1 | 5 | 00:15:00 |
| 2 | 7 | 00:19:30 |
| 3 | 9 | 00:29:20 |
| 4 | 5 | 00:17:50 |
+-------+----+----------+
To calculate cumulative distance in kilometers at each stage, use a self-join like this:
mysql>
SELECT t1.stage, t1.km, SUM(t2.km) AS 'cum. km'
->
FROM marathon AS t1 INNER JOIN marathon AS t2
->
ON t1.stage >= t2.stage
->
GROUP BY t1.stage;
+-------+----+---------+
| stage | km | cum. km |
+-------+----+---------+
| 1 | 5 | 5 |
| 2 | 7 | 12 |
| 3 | 9 | 21 |
| 4 | 5 | 26 |
+-------+----+---------+
Cumulative distances are easy to compute because they can be summed directly. The
calculation for accumulating time values is more involved: convert times to seconds,
total the resulting values, and convert the sum back to a time value. To compute the
runner's average speed at the end of each stage, take the ratio of cumulative distance
over cumulative time. Putting all this together yields the following statement:
mysql>
SELECT t1.stage, t1.km, t1.t,
->
SUM(t2.km) AS 'cum. km',
->
SEC_TO_TIME(SUM(TIME_TO_SEC(t2.t))) AS 'cum. t',
->
SUM(t2.km)/(SUM(TIME_TO_SEC(t2.t))/(60*60)) AS 'avg. km/hour'
->
FROM marathon AS t1 INNER JOIN marathon AS t2
->
ON t1.stage >= t2.stage
->
GROUP BY t1.stage;
+-------+----+----------+---------+----------+--------------+
| stage | km | t | cum. km | cum. t | avg. km/hour |
+-------+----+----------+---------+----------+--------------+
| 1 | 5 | 00:15:00 | 5 | 00:15:00 | 20.0000 |
| 2 | 7 | 00:19:30 | 12 | 00:34:30 | 20.8696 |
| 3 | 9 | 00:29:20 | 21 | 01:03:50 | 19.7389 |
