Нарастающий итог и скользящее среднее

Задача
У вас есть ряд наблюдений, сделанных на протяжении какого-то периода времени, и вы хотите вычислить сумму нарастающим итогом (cumulative sum) для каждой точки измерения. Или же хотите получить скользящее среднее (running average) в каждой точке.

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

Обсуждение
В рецепте 12.12 было показано, как самосоединение может формировать относительные значения из абсолютных. Самосоединение может делать и обратное, формируя накапливаемые значения для каждого последовательного этапа серии наблюдений. В следующей таблице приведены результаты измерений количества осадков, сделанных в течение нескольких дней. Значения каждой строки представляют дату наблюдения и количество осадков в дюймах:mysql> SELECT date, precip FROM rainfall ORDER BY date;

+--------------+--------+
| date | precip |
+--------------+--------+
| 2002-06-01 | 1.50 |
| 2002-06-02 | 0.00 |
| 2002-06-03 | 0.50 |
| 2002-06-04 | 0.00 |
| 2002-06-05 | 1.00 |
+--------------+--------+

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


Например, совокупное количество осадков на 2002-06-03 определяется так:

mysql> SELECT SUM(precip) FROM rainfall WHERE date <= '2002-06-03';

+---------------+
| SUM(precip) |
+---------------+
| 2.00 |
+---------------+

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

mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum. precip'
-> FROM rainfall AS t1, rainfall AS t2
-> WHERE t1.date >= t2.date
-> GROUP BY t1.date;

+--------------+--------------+--------------+
| date | daily precip | cum. precip |
+--------------+--------------+--------------+
| 2002-06-01 | 1.50 | 1.50 |
| 2002-06-02 | 0.00 | 1.50 |
| 2002-06-03 | 0.50 | 2.00 |
| 2002-06-04 | 0.00 | 2.00 |
| 2002-06-05 | 1.00 | 3.00 |
+--------------+--------------+--------------+

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

mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum.


precip',-> COUNT(t2.precip) AS days,
-> AVG(t2.precip) AS 'avg. precip'
-> FROM rainfall AS t1, rainfall AS t2
-> WHERE t1.date >= t2.date
-> GROUP BY t1.date;

+--------------+--------------+--------------+------+--------------+
| date | daily precip | cum. precip | days | avg. precip |
+--------------+--------------+--------------+------+--------------+
| 2002-06-01 | 1.50 | 1.50 | 1 | 1.500000 |
| 2002-06-02 | 0.00 | 1.50 | 2 | 0.750000 |
| 2002-06-03 | 0.50 | 2.00 | 3 | 0.666667 |
| 2002-06-04 | 0.00 | 2.00 | 4 | 0.500000 |
| 2002-06-05 | 1.00 | 3.00 | 5 | 0.600000 |
+--------------+--------------+-------------+-------+--------------+

В предыдущем запросе количество прошедших дней и средний объем осадков можно легко получить при помощи COUNT() и AVG(), так как в таблице нет пропущенных дней. Если же было бы разрешено пропускать дни, вычисление усложнилось бы, так как в этом случае количество дней, прошедших до указанной даты, не было бы равно количеству записей.


Давайте удалим из таблицы записи для тех дней, в которые не было осадков, создав тем самым две «дыры»:

mysql> DELETE FROM rainfall WHERE precip = 0;
mysql> SELECT date, precip FROM rainfall ORDER BY date;

+--------------+--------+
| date | precip |
+--------------+--------+
| 2002-06-01 | 1.50 |
| 2002-06-03 | 0.50 |
| 2002-06-05 | 1.00 |
+--------------+--------+

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

mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum. precip',
-> COUNT(t2.precip) AS days,
-> AVG(t2.precip) AS 'avg. precip'
-> FROM rainfall AS t1, rainfall AS t2
-> WHERE t1.date >= t2.date
-> GROUP BY t1.date;

+--------------+--------------+--------------+------+--------------+
| date | daily precip | cum. precip | days | avg. precip |
+--------------+--------------+--------------+------+--------------+
| 2002-06-01 | 1.50 | 1.50 | 1 | 1.500000 |
| 2002-06-03 | 0.50 | 2.00 | 2 | 1.000000 |
| 2002-06-05 | 1.00 | 3.00 | 3 | 1.000000 |
+--------------+--------------+-------------+-------+--------------+

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


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

TO_DAYS(MAX(t2.date)) - TO_DAYS(MIN(t2.date)) + 1

Это значение должно применяться для столбца количества дней и для вычисления среднего. В результате запрос будет таким:

mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum. precip',
-> TO_DAYS(MAX(t2.date)) - TO_DAYS(MIN(t2.date)) + 1 AS days,
-> SUM(t2.precip) / (TO_DAYS(MAX(t2.date)) - TO_DAYS(MIN(t2.date)) + 1)
-> AS 'avg. precip'
-> FROM rainfall AS t1, rainfall AS t2
-> WHERE t1.date >= t2.date
-> GROUP BY t1.date;

+--------------+--------------+--------------+------+--------------+
| date | daily precip | cum. precip | days | avg. precip |
+--------------+--------------+--------------+------+--------------+
| 2002-06-01 | 1.50 | 1.50 | 1 | 1.5000 |
| 2002-06-03 | 0.50 | 2.00 | 3 | 0.6667 |
| 2002-06-05 | 1.00 | 3.00 | 5 | 0.6000 |
+--------------+--------------+--------------+------+---------------+

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


(В таблице rainfall это столбец date.) Значения такого столбца не должны быть последовательными и даже не обязаны быть числами. В этом отличие от вычисления разностей значений для накопленных значений, которое требует наличия в таблице столбца с непрерывной последовательностью.

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

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

mysql> SELECT stage, km, t FROM marathon ORDER BY stage;

+-------+----+----------+
| stage | km | t |
+-------+----+----------+
| 1 | 5 | 00:15:00 |
| 2 | 7 | 00:19:30 |
| 3 | 9 | 00:29:20 |
| 4 | 5 | 00:17:50 |
+-------+----+----------+

Чтобы вычислить накапливаемые расстояния в километрах для каждого этапа, выполним самосоединение:

mysql> SELECT t1.stage, t1.km, SUM(t2.km) AS 'cum. km'
-> FROM marathon AS t1, marathon AS t2
-> WHERE t1.stage >= t2.stage
-> GROUP BY t1.stage;

+-------+----+----------+
| stage | km | cum. km |
+-------+----+----------+
| 1 | 5 | 5 |
| 2 | 7 | 12 |
| 3 | 9 | 21 |
| 4 | 5 | 26 |
+-------+----+----------+

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

mysql> SELECT t1.stage, t1.km, t1.t,
-> SUM(t2.km) AS 'cum. km',
-> SEC_TO_TIME(SUM(TIME_TO_SEC(t2.t))) AS 'cum. t',
-> SUM(t2.km)/(SUM(TIME_TO_SEC(t2.t))/(60*60)) AS 'avg. km/hour'
-> FROM marathon AS t1, marathon AS t2
-> WHERE t1.stage >= t2.stage
-> GROUP BY t1.stage;

+-------+----+-----------+----------+-----------+-----------------+
| stage | km | t | cum. km | cum. t | avg. km/hour |
+-------+----+-----------+----------+-----------+-----------------+
| 1 | 5 | 00:15:00 | 5 | 00:15:00 | 20.0000 |
| 2 | 7 | 00:19:30 | 12 | 00:34:30 | 20.8696 |
| 3 | 9 | 00:29:20 | 21 | 01:03:50 | 19.7389 |
| 4 | 5 | 00:17:50 | 26 | 01:21:40 | 19.1020 |
+-------+----+-----------+----------+------------+----------------+

Видно, что бегун прибавил темп на втором этапе гонки, но потом, видимо, в результате усталости, только снижал его.

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

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