Mega Code Archive
The self-join can be extended to display the number of days elapsed at each date
<![CDATA[
mysql>
mysql> CREATE TABLE rainfall
-> (
-> date DATE NOT NULL,
-> precip FLOAT(10,2) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO rainfall (date, precip)
-> VALUES
-> ('2002-06-01', 1.5),
-> ('2002-06-02', 0),
-> ('2002-06-03', 0.5),
-> ('2002-06-04', 0),
-> ('2002-06-05', 1.0)
-> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM rainfall;
+------------+--------+
| date | precip |
+------------+--------+
| 2002-06-01 | 1.50 |
| 2002-06-02 | 0.00 |
| 2002-06-03 | 0.50 |
| 2002-06-04 | 0.00 |
| 2002-06-05 | 1.00 |
+------------+--------+
5 rows in set (0.00 sec)
mysql>
mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum. precip',
-> COUNT(t2.precip) AS days,
-> AVG(t2.precip) AS 'avg. precip'
-> FROM rainfall AS t1, rainfall AS t2
-> WHERE t1.date >= t2.date
-> GROUP BY t1.date;
+------------+--------------+-------------+------+-------------+
| date | daily precip | cum. precip | days | avg. precip |
+------------+--------------+-------------+------+-------------+
| 2002-06-01 | 1.50 | 1.50 | 1 | 1.500000 |
| 2002-06-02 | 0.00 | 1.50 | 2 | 0.750000 |
| 2002-06-03 | 0.50 | 2.00 | 3 | 0.666667 |
| 2002-06-04 | 0.00 | 2.00 | 4 | 0.500000 |
| 2002-06-05 | 1.00 | 3.00 | 5 | 0.600000 |
+------------+--------------+-------------+------+-------------+
5 rows in set (0.00 sec)
mysql>
mysql> drop table rainfall;
Query OK, 0 rows affected (0.00 sec)
]]>