Mega Code Archive
To compute the runners average speed at the end of each stage
<![CDATA[
mysql>
mysql> CREATE TABLE marathon
-> (
-> stage INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> km INT UNSIGNED NOT NULL, # length of stage, in km
-> t TIME NOT NULL, # elapsed time for stage
-> PRIMARY KEY (stage)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO marathon (km, t)
-> VALUES
-> (5,'00:15:00'),
-> (7,'00:19:30'),
-> (9,'00:29:20'),
-> (5,'00:17:50')
-> ;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
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, marathon AS t2
-> WHERE 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 |
| 4 | 5 | 00:17:50 | 26 | 01:21:40 | 19.1020 |
+-------+----+----------+---------+----------+--------------+
4 rows in set (0.00 sec)
mysql>
mysql> drop table marathon;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>