Recipe 12.10. Finding Cumulative Sums and Running Averages
ProblemYou 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. SolutionUse a self-join to produce the sets of successive observations at each measurement point, and then apply aggregate functions to each set of values to compute its sum or average. DiscussionSection 12.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 the amount of precipitation in inches: mysql> SELECT date, precip FROM rainfall ORDER BY date; +------------+--------+ | date | precip | +------------+--------+ | 2006-06-01 | 1.50 | | 2006-06-02 | 0.00 | | 2006-06-03 | 0.50 | | 2006-06-04 | 0.00 | | 2006-06-05 | 1.00 | +------------+--------+ To calculate cumulative rainfall for a given day, add that day's precipitation value with the values for all the previous days. For example, determine the cumulative rainfall as of 2006-06-03 like this: mysql> SELECT SUM(precip) FROM rainfall WHERE date <= '2006-06-03'; +-------------+ | SUM(precip) | +-------------+ | 2.00 | +-------------+ If you want the cumulative figures for all days that are represented in the table, it would be tedious to compute the value for each of them separately. A self-join can do this for all days with a single statement. 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 | +------------+--------------+-------------+ | 2006-06-01 | 1.50 | 1.50 | | 2006-06-02 | 0.00 | 1.50 | | 2006-06-03 | 0.50 | 2.00 | | 2006-06-04 | 0.00 | 2.00 | | 2006-06-05 | 1.00 | 3.00 | +------------+--------------+-------------+ The self-join can be extended to display the number of days elapsed at each date, as well as the running averages for amount of precipitation each day: mysql> SELECT t1.date, t1.precip AS 'daily precip', -> SUM(t2.precip) AS 'cum. precip', -> COUNT(t2.precip) AS 'days elapsed', -> AVG(t2.precip) AS 'avg. precip' -> FROM rainfall AS t1 INNER JOIN rainfall AS t2 -> ON t1.date >= t2.date -> GROUP BY t1.date; +------------+--------------+-------------+--------------+-------------+ | date | daily precip | cum. precip | days elapsed | avg. precip | +------------+--------------+-------------+--------------+-------------+ | 2006-06-01 | 1.50 | 1.50 | 1 | 1.500000 | | 2006-06-02 | 0.00 | 1.50 | 2 | 0.750000 | | 2006-06-03 | 0.50 | 2.00 | 3 | 0.666667 | | 2006-06-04 | 0.00 | 2.00 | 4 | 0.500000 | | 2006-06-05 | 1.00 | 3.00 | 5 | 0.600000 | +------------+--------------+-------------+--------------+-------------+ In the preceding statement, the number of days elapsed and the precipitation running averages can be computed easily using COUNT( ) and AVG( ) because there are no missing days in the table. If missing days are allowed, the calculation becomes more complicated, because the number of days elapsed for each calculation no longer will be the same as the number of rows. You can see this by deleting the rows for the days that had no precipitation to produce a couple of "holes" in the table: mysql> DELETE FROM rainfall WHERE precip = 0; mysql> SELECT date, precip FROM rainfall ORDER BY date; +------------+--------+ | date | precip | +------------+--------+ | 2006-06-01 | 1.50 | | 2006-06-03 | 0.50 | | 2006-06-05 | 1.00 | +------------+--------+ Deleting those rows doesn't change the cumulative sum or running average for the dates that remain, but it does change how they must be calculated. If you try the self-join again, it yields incorrect results for the days-elapsed and average precipitation columns: mysql> SELECT t1.date, t1.precip AS 'daily precip', -> SUM(t2.precip) AS 'cum. precip', -> COUNT(t2.precip) AS 'days elapsed', -> AVG(t2.precip) AS 'avg. precip' -> FROM rainfall AS t1 INNER JOIN rainfall AS t2 -> ON t1.date >= t2.date -> GROUP BY t1.date; +------------+--------------+-------------+--------------+-------------+ | date | daily precip | cum. precip | days elapsed | avg. precip | +------------+--------------+-------------+--------------+-------------+ | 2006-06-01 | 1.50 | 1.50 | 1 | 1.500000 | | 2006-06-03 | 0.50 | 2.00 | 2 | 1.000000 | | 2006-06-05 | 1.00 | 3.00 | 3 | 1.000000 | +------------+--------------+-------------+--------------+-------------+ To fix the problem, it's necessary to determine the number of days elapsed a different way. Take the minimum and maximum date involved in each sum and calculate a days-elapsed value from them using the following expression: DATEDIFF(MAX(t2.date),MIN(t2.date)) + 1 That value must be used for the days-elapsed column and for computing the running averages. The resulting statement is as follows: mysql> SELECT t1.date, t1.precip AS 'daily precip', -> SUM(t2.precip) AS 'cum. precip', -> DATEDIFF(MAX(t2.date),MIN(t2.date)) + 1 AS 'days elapsed', -> SUM(t2.precip) / (DATEDIFF(MAX(t2.date),MIN(t2.date)) + 1) -> AS 'avg. precip' -> FROM rainfall AS t1 INNER JOIN rainfall AS t2 -> ON t1.date >= t2.date -> GROUP BY t1.date; +------------+--------------+-------------+--------------+-------------+ | date | daily precip | cum. precip | days elapsed | avg. precip | +------------+--------------+-------------+--------------+-------------+ | 2006-06-01 | 1.50 | 1.50 | 1 | 1.500000 | | 2006-06-03 | 0.50 | 2.00 | 3 | 0.666667 | | 2006-06-05 | 1.00 | 3.00 | 5 | 0.600000 | +------------+--------------+-------------+--------------+-------------+ As this example illustrates, calculation of cumulative values from relative values requires only a column that enables rows to be placed into the proper order. (For the rainfall table, that's the date column.) Values in the column need not be sequential, or even numeric. This differs from calculations that produce difference values from cumulative values (Section 12.9), which require that a table have a column that contains an unbroken sequence. The running averages in the rainfall examples are based on dividing cumulative precipitation sums by number of days elapsed as of each day. When the table has no gaps, the number of days is the same as the number of values summed, making it easy to find successive averages. When rows are missing, the calculations become more complex. What this demonstrates is that it's necessary to consider the nature of your data and calculate averages appropriately. The next example is conceptually similar to the previous ones in that it calculates cumulative sums and running averages, but it performs the computations 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 that looks 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 a little more involved. It's necessary to 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 | | 4 | 5 | 00:17:50 | 26 | 01:21:40 | 19.1020 | +-------+----+----------+---------+----------+--------------+ We can see from this that the runner's average pace increased a little during the second stage of the race, but then (presumably as a result of fatigue) decreased thereafter. |
