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

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

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

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

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 |
+---+-----+---------------+-----+----+----+----+-------+------+-------+

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


А вот в августе его результаты стали просто замечательными..



Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вставка записей в таблицу, включающую значения из другой
Вывод списков для записей «главная-подчиненная» и итогов
Вычисление рейтинга команд
Выявление и удаление несвязанных записей
Заполнение пустых мест в списке с помощью соединения