Statistical Techniques - MySQL - page 534

Solution
Use a self-join to produce the sets of successive observations at each measurement point,
then apply aggregate functions to each set of values to compute its sum or average.
Discussion
Recipe 15.9 illustrates how a self-join can produce relative values from absolute values.
A self-join can do the opposite as well, producing cumulative values at each successive
stage of a set of observations. The following table shows a set of rainfall measurements
taken over a series of days. The values in each row show the observation date and pre‐
cipitation in inches:
mysql> SELECT date, precip FROM rainfall ORDER BY date;
+------------+--------+
| date | precip |
+------------+--------+
| 2014-06-01 | 1.50 |
| 2014-06-02 | 0.00 |
| 2014-06-03 | 0.50 |
| 2014-06-04 | 0.00 |
| 2014-06-05 | 1.00 |
+------------+--------+
To calculate cumulative rainfall for a given day, add that day's precipitation value to the
values for all the previous days. For example, determine the cumulative rainfall as of
2014-06-03 like this:
mysql> SELECT SUM(precip) FROM rainfall WHERE date <= '2014-06-03';
+-------------+
| SUM(precip) |
+-------------+
| 2.00 |
+-------------+
To get the cumulative figures for all days represented in the table, it's tedious to compute
the value separately for each day. A self-join can do this for all days with a single state‐
ment. Use one instance of the rainfall table as a reference, and determine for the date
in each row the sum of the precip values in all rows occurring up through that date in
another instance of the table. The following statement shows the daily and cumulative
precipitation for each day:
mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum. precip'
-> FROM rainfall AS t1 INNER JOIN rainfall AS t2
-> ON t1.date >= t2.date
-> GROUP BY t1.date;
+------------+--------------+-------------+
| date | daily precip | cum. precip |
+------------+--------------+-------------+