Как подсчитать накопительный итог?

Часто бывает необходимо для каждой строки упорядоченной таблицы подсчитать сумму значений числового столбца из строк, выше- или нижестоящих по порядку, включая данную. Это и есть задача расчета накопительного итога. Например, из таблицы, отсортированной по столбцу 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 будет таблица, полученная в результате выполнения сложного ресурсоёмкого запроса, то возможность избежать её соединения с собой (её второе вычисление) значительно повысит производительность. А данный пример взят для простоты изложения.