Database Reference
In-Depth Information
| 2014-06-01 | 1.50 | 1.50 |
| 2014-06-02 | 0.00 | 1.50 |
| 2014-06-03 | 0.50 | 2.00 |
| 2014-06-04 | 0.00 | 2.00 |
| 2014-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 |
+------------+--------------+-------------+--------------+-------------+
| 2014-06-01 | 1.50 | 1.50 | 1 | 1.500000 |
| 2014-06-02 | 0.00 | 1.50 | 2 | 0.750000 |
| 2014-06-03 | 0.50 | 2.00 | 3 | 0.666667 |
| 2014-06-04 | 0.00 | 2.00 | 4 | 0.500000 |
| 2014-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 permitted, the calculation becomes more compli‐
cated because the number of days elapsed for each calculation is no longer the same as
the number of rows. You can see this by deleting the rows for the days that had no
precipitation to produce “holes” in the table:
mysql>
DELETE FROM rainfall WHERE precip = 0;
mysql>
SELECT date, precip FROM rainfall ORDER BY date;
+------------+--------+
| date | precip |
+------------+--------+
| 2014-06-01 | 1.50 |
| 2014-06-03 | 0.50 |
| 2014-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 execute 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'
