You have a set of observations measured over time and want to compute the cumulative sum of the observations at each measurement point. Or you want to compute a running average at each point.

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.

Recipe 12.13 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 the amount of precipitation in inches:

mysql>+------------+--------+ | 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 | +------------+--------+`SELECT date, precip FROM rainfall ORDER BY date;`

To calculate cumulative rainfall for a given day, sum that
day’s precipitation value with the values for all
the previous days. For example, the cumulative rainfall as of
`2002-06-03`

is determined like this:

mysql>+-------------+ | SUM(precip) | +-------------+ | 2.00 | +-------------+`SELECT SUM(precip) FROM rainfall WHERE date <= '2002-06-03';`

If you want the cumulative figures for all days that are represented in the ...

Start Free Trial

No credit card required