Как подсчитать накопительный итог?
Красовский Е.А. (05-02-2011)
Часто бывает необходимо для каждой строки упорядоченной таблицы подсчитать сумму значений числового столбца из строк, выше- или нижестоящих по порядку, включая данную. Это и есть задача расчета накопительного итога. Например, из таблицы, отсортированной по столбцу time:
time var 1 15 2 10 4 13 7 11
нужно получить следующую таблицу:
time var total_sum 1 15 15 2 10 25 4 13 38 7 11 49
Для лучшего понимания поставим более конкретную задачу для базы данных "Окраска":
Задача 1
Для каждого момента времени, когда происходила окраска квадрата с q_id = 10, найти суммарное количество потраченной на него краски к этому моменту времени.
Таблица utB включает столбцы b_datetime, b_vol. Для каждого значения X из столбца b_datetime, необходимо подсчитать сумму b_vol по всем строкам, где момент времени b_datetime ≤ X.
Решим задачу двумя самыми распространенными методами.
1) Подзапрос в предложении SELECT
В этом методе предполагается вычисление накопительного итога подзапросом. Поспешив, можно написать следующий запрос:
SELECT b_datetime,
(
SELECT SUM(T1.b_vol)
FROM utB T1
WHERE T1.b_datetime <= T2.b_datetime
AND T1.b_q_id = 10
) total_vol
FROM utB T2
WHERE b_q_id = 10;
Однако этот запрос неверный! Дело в том, что квадрат с номером 10 одновременно могли окрашивать разные баллончики, в итоге при таких окрасках получим дубликаты строк:
b_datetime total_vol 2003-01-01 01:12:31.000 255 2003-01-01 01:12:31.000 255 2003-01-01 01:12:33.000 265 2003-01-01 01:12:34.000 275 2003-01-01 01:12:35.000 520 2003-01-01 01:12:36.000 765
Ошибка ликвидируется добавлением DISTINCT в первый SELECT, но тогда для каждой строки с одинаковыми значениями b_datetime будет вычисляться подзапрос, а лишь затем устраняться дубликаты. Поэтому в данном случае лучше исключить дубликаты заранее. Например, так:
SELECT b_datetime,
(
SELECT SUM(T1.b_vol)
FROM utB T1
WHERE T1.b_datetime <= T2.b_datetime
AND T1.b_q_id = 10
) total_vol
FROM
(
SELECT DISTINCT b_datetime --исключаем дубликаты
FROM utB
WHERE b_q_id = 10
) T2;
2) Декартово произведение
Метод заключается в том, что таблица соединяется сама с собой по условию X >= b_datetime. Затем считается сумма b_vol с группировкой по b_datetime. При этом значения X не должны повторяться, иначе в результате одни и те же строки попадут в итоговую сумму несколько раз! Выглядит это следующим образом:
SELECT T2.b_datetime, SUM(T1.b_vol) total_vol
FROM utB T1
INNER JOIN
(
SELECT DISTINCT b_datetime --исключаем дубликаты
FROM utB
WHERE b_q_id = 10 --рассматриваем только квадрат с b_q_id = 10
) T2
ON T1.b_datetime <= T2.b_datetime
WHERE T1.b_q_id = 10
GROUP BY T2.b_datetime;
Если в таблице Т2 убрать DISTINCT, то получим следующий ошибочный результат:
b_datetime total_vol 2003-01-01 01:12:31.000 510 2003-01-01 01:12:33.000 265 2003-01-01 01:12:34.000 275 2003-01-01 01:12:35.000 520 2003-01-01 01:12:36.000 765
Рассмотренный пример специально подобран немного выходящий за рамки темы. Потому, как почти всегда, параллельно с вычислением накопительного итога, необходимо следить за разного рода нюансами. Что и было проделано.
Дополнение
Оба метода требуют нескольких чтений из таблицы. Иногда этого можно избежать, используя генерацию числовой последовательности. Для дальнейших рассуждений переформулируем задачу.
Задача 2
Каждому моменту времени, когда совершалась окраска квадрата с q_id = 10, сопоставить номер окраски в порядке возрастания b_datetime. Для каждого такого номера найти суммарное количество потраченной на квадрат краски к этому моменту времени.
Теперь, если бы мы использовали первый метод, то соединяли бы таблицы с условием не на время, а на номер. Часто так и бывает. И реализация первого метода выглядела бы так:
SELECT T2.rn, SUM(T1.b_vol) total_vol
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY b_datetime) rn,
SUM(b_vol)b_vol --достаточно вычислить только для одной таблицы
FROM utB WHERE b_q_id = 10
GROUP BY b_datetime --исключаем дубликаты
)T1
INNER JOIN
(
SELECT ROW_NUMBER() OVER(ORDER BY b_datetime) rn
FROM utB WHERE b_q_id = 10
GROUP BY b_datetime
)T2
ON T1.rn <= T2.rn
GROUP BY T2.rn;
Здесь использована функция Transact-SQL ROW_NUMBER() для нумерации строк.
Заметим, что таблица T2 представляет собой последовательность натуральных чисел, и для её создания вовсе не обязательно производить чтение из utB! Достаточно просто сгенерировать числовую последовательность. Единственное, что препятствует этому - мы не знаем, сколько членов в последовательности нам понадобится. Зато мы знаем, что b_vol - целое, больше нуля, а количество краски в квадрате не превышает 765. Поэтому достаточно сгенерировать 765 членов. Количество членов можно выяснить и подзапросом, в некоторых случаях это полезно. Всё зависит от задачи. В итоге получим следующий запрос:
SELECT T2.rn, SUM(T1.b_vol) total_vol
FROM
(
SELECT rn, b_vol, COUNT(*)OVER()cnt_rec -- вычисляем количество строк
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY b_datetime) rn,
SUM(b_vol)b_vol
FROM utB WHERE b_q_id = 10
GROUP BY b_datetime
)T
)T1
INNER JOIN
(
SELECT a + 10*b + 100*c rn
FROM
(SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
UNION SELECT 9 UNION SELECT 10)AA,
(SELECT 0 b UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9)BB,
(SELECT 0 c UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7)CC
)T2 -- последовательность (1 .. 800)
ON T1.rn <= T2.rn
AND T2.rn <= T1.cnt_rec -- ограничиваем количество строк T2 количеством строк T1
GROUP BY T2.rn;
В данном случае применение такого приема конечно не оправдано. Но если вместо T1 будет таблица, полученная в результате выполнения сложного ресурсоёмкого запроса, то возможность избежать её соединения с собой (её второе вычисление) значительно повысит производительность. А данный пример взят для простоты изложения.
