Database Reference
In-Depth Information
| 5 | Portageville, MO | 878 |
| 6 | Champaign, IL | 1164 |
| 7 | Madison, WI | 1412 |
+-----+------------------+-------+
A self-join can convert these cumulative values to successive differences that represent
the distances from each city to the next. The following statement shows how to use the
sequence numbers in the rows to match pairs of successive rows and compute the dif‐
ferences between each pair of mileage values:
mysql>
SELECT t1.seq AS seq1, t2.seq AS seq2,
->
t1.city AS city1, t2.city AS city2,
->
t1.miles AS miles1, t2.miles AS miles2,
->
t2.miles-t1.miles AS dist
->
FROM trip_log AS t1 INNER JOIN trip_log AS t2
->
ON t1.seq+1 = t2.seq
->
ORDER BY t1.seq;
+------+------+------------------+------------------+--------+--------+------+
| seq1 | seq2 | city1 | city2 | miles1 | miles2 | dist |
+------+------+------------------+------------------+--------+--------+------+
| 1 | 2 | San Antonio, TX | Dallas, TX | 0 | 263 | 263 |
| 2 | 3 | Dallas, TX | Benton, AR | 263 | 566 | 303 |
| 3 | 4 | Benton, AR | Memphis, TN | 566 | 745 | 179 |
| 4 | 5 | Memphis, TN | Portageville, MO | 745 | 878 | 133 |
| 5 | 6 | Portageville, MO | Champaign, IL | 878 | 1164 | 286 |
| 6 | 7 | Champaign, IL | Madison, WI | 1164 | 1412 | 248 |
+------+------+------------------+------------------+--------+--------+------+
The presence of the
seq
column in the
trip_log
table is important for calculating suc‐
cessive difference values. It's needed for establishing which row precedes another and
matching each row
n
with row
n
+1
. The implication is that to perform relative-difference
calculations using a table of absolute or cumulative values, it must include a sequence
column that has no gaps. If the table contains a sequence column but there are gaps,
renumber it (see
Recipe 13.5
). If the table contains no such column, add one (see
Recipe 13.9
).
A more complex situation occurs when you compute successive differences for more
than one column and use the results in a calculation. The following table,
play
er_stats
, shows some cumulative numbers for a baseball player at the end of each
month of his season.
ab
indicates the total at-bats, and
h
the total hits the player has had
as of a given date. (The first row indicates the starting point of the player's season, which
is why the
ab
and
h
values are zero.)
mysql>
SELECT id, date, ab, h, TRUNCATE(IFNULL(h/ab,0),3) AS ba
->
FROM player_stats ORDER BY id;
+----+------------+-----+----+-------+
| id | date | ab | h | ba |
+----+------------+-----+----+-------+
| 1 | 2013-04-30 | 0 | 0 | 0.000 |
| 2 | 2013-05-31 | 38 | 13 | 0.342 |
