MySQL / 12. Использование нескольких таблиц

Вычисление разности между последовательными строками

Задача
У вас есть таблица, строки которой содержат последовательные накапливаемые значения, и вы хотите вычислить разности между парами последовательных строк.

Решение
Используйте самосоединение, сопоставляющее пары соседних строк и вычисляющее разность между членами пары.

Обсуждение
Самосоединения полезны, если у вас есть набор абсолютных (или накопленных) значений, которые вы хотите преобразовать в относительные значения, представляющие разности последовательных пар строк. Например, если вы едете в путешествие на автомобиле и записываете количество миль на каждой остановке, то можете вычислить разность расстояний до последовательных точек, чтобы определить расстояние между ними. Рассмотрим таблицу, предствляющую остановки на пути из Сан-Антонио, штат Техас, в Мэдисон, штат Висконсин. Каждая строка содержит общее количество миль, пройденных до данной остановки:

mysql> SELECT seq, city, miles FROM trip_log ORDER BY seq;

+----+--------------------+-------+
| seq | city                     | miles |
+----+--------------------+-------+
| 1     | San Antonio, TX | 0      |
| 2     | Dallas, TX           | 263  |
| 3     | Benton, AR         | 566  |
| 4     | Memphis, TN       | 745  |
| 5     | Portageville, MO | 878  |
| 6     | Champaign, IL    | 1164 |
| 7     | Madison, WI       | 1412 |
+-----+-------------------+-------+

Самосоединение может преобразовать эти накапливаемые значения в последовательные разности, представляющие расстояния от каждого города до следующего. Напишем запрос, который использует номера последовательности в записях для нахождения пар соседних строк и вычисления разностей для каждой пары значений расстояний:

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, trip_log AS t2-> WHERE 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  |
+------+------+--------------------+--------------------+-------+--------+------+

Наличие столбца seq в таблице trip_log необходимо для вычисления последовательных разностей: с его помощью устанавливается, какая строка предшествует другой строке, и выполняется сопоставление строки с номером n строке n+1. При выполнении вычислений разностей абсолютных (или накапливаемых) значений подразумевается, что таблица должна содержать столбец последовательности без разрывов (gap). Если таблица содержит столбец последовательности с разрывами, перенумеруйте его. Если в таблице вообще нет такого столбца, добавьте его.

Более сложная ситуация возникает, если нужно вычислить последовательные разности для нескольких столбцов, а затем использовать результаты в вычислении. Таблица player_stats предлагает несколько суммарных показателей для бейсбольного игрока в конце каждого месяца игрового сезона: ab означает общее количество выступлений в качестве отбивающего (at-bat), а h – общее количество отбитых подач (hit) на указанную дату. (Первая запись относится к началу сезона игрока, поэтому значения ab и h равны нулю.)

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  | 2001-04-30 | 0     | 0   | 0.000 |
| 2  | 2001-05-31 | 38   | 13 | 0.342 |
| 3  | 2001-06-30 | 109 | 31 | 0.284 |
| 4  | 2001-07-31 | 196 | 49 | 0.250 |
| 5  | 2001-08-31 | 304 | 98 | 0.322 |
+----+------------+-----+----+-------+

Последний столбец результата запроса приводит среднее число очков отбивающего игрока (batting average) на каждую дату. Этот столбец не хранится в таблице, но легко вычисляется как отношение числа отбитых подач к числу выступлений в качестве отбивающего. Результат дает общее представление об изменении частоты попаданий игрока в течение сезона, но при этом не очень понятно, как она менялась от месяца к месяцу. Необходимо вычислить относительные разности между парами строк. Это легко сделать при помощи самосоединения, которое сопоставляет каждой строке n строку n +1 для вы-числения разностей между парами поданных и отбитых подач. Эти разности позволяют вычислить среднее число очков отбивающего за каждый месяц:

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, player_stats AS t2
-> WHERE t1.id+1 = t2.id
-> ORDER BY t1.id;

+---+-----+--------------+-----+-----+----+----+-------+------+-------+
| id1 | id2 | date             | ab1 | ab2 | h1 | h2 | abdiff | hdiff | ba      |
+---+-----+--------------+-----+-----+----+----+-------+------+-------+
| 1    | 2    | 2001-05-31 | 0     | 38   | 0   | 13 | 38      | 13     | 0.342 |
| 2    | 3    | 2001-06-30 | 38   | 109 | 13 | 31 | 71      | 18     | 0.253 |
| 3    | 4    | 2001-07-31 | 109 | 196 | 31 | 49 | 87      | 18     | 0.206 |
| 4    | 5    | 2001-08-31 | 196 | 304 | 49 | 98 | 108    | 49     | 0.453 |
+---+-----+---------------+-----+----+----+----+-------+------+-------+

Результаты гораздо нагляднее, чем исходная таблица, показывают, что игрок начал сезон хорошо, потом, особенно в июле, у него наблюдался резкий спад. А вот в августе его результаты стали просто замечательными.

Статьи по MySQL на эту тему:

Вставка записей в таблицу, включающую значения из другой
Выявление и удаление несвязанных записей
Нарастающий итог и скользящее среднее
Обновление одной таблицы на основе значений другой
Одновременное использование нескольких серверов MySQL