Database Reference
In-Depth Information
| 3 | 2013-06-30 | 109 | 31 | 0.284 |
| 4 | 2013-07-31 | 196 | 49 | 0.250 |
| 5 | 2013-08-31 | 304 | 98 | 0.322 |
+----+------------+-----+----+-------+
The last column of the query result also shows the player's batting average as of each
date. This column is not stored in the table but is easily computed as the ratio of hits to
at-bats. The result provides a general idea of how the player's hitting performance
changed over the course of the season, but it provides no picture of how the player did
during each individual month. To determine that, calculate relative differences between
pairs of rows. This is easily done with a self-join that matches row n with row n +1 to
calculate differences between pairs of at-bats and hits values. These differences enable
computation of batting average during each month:
mysql> SELECT
-> t1.id AS id1, t2.id AS id2,
-> t2.date,
-> t1.ab AS ab1, t2.ab AS ab2,
-> t1.h AS h1, t2.h AS h2,
-> t2.ab-t1.ab AS abdiff,
-> t2.h-t1.h AS hdiff,
-> TRUNCATE(IFNULL((t2.h-t1.h)/(t2.ab-t1.ab),0),3) AS ba
-> FROM player_stats AS t1 INNER JOIN player_stats AS t2
-> ON t1.id+1 = t2.id
-> ORDER BY t1.id;
+-----+-----+------------+-----+-----+----+----+--------+-------+-------+
| id1 | id2 | date | ab1 | ab2 | h1 | h2 | abdiff | hdiff | ba |
+-----+-----+------------+-----+-----+----+----+--------+-------+-------+
| 1 | 2 | 2013-05-31 | 0 | 38 | 0 | 13 | 38 | 13 | 0.342 |
| 2 | 3 | 2013-06-30 | 38 | 109 | 13 | 31 | 71 | 18 | 0.253 |
| 3 | 4 | 2013-07-31 | 109 | 196 | 31 | 49 | 87 | 18 | 0.206 |
| 4 | 5 | 2013-08-31 | 196 | 304 | 49 | 98 | 108 | 49 | 0.453 |
+-----+-----+------------+-----+-----+----+----+--------+-------+-------+
These results show much more clearly than the original table that the player started off
well but had a slump in the middle of the season, particularly in July. They also indicate
just how strong his performance was in August.
15.10. Finding Cumulative Sums and Running Averages
Problem
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.
Search WWH ::




Custom Search