четверг, 23 февраля 2017 г.

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

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

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