PostgreSQL | Сумма с приращением (накоплением) | Нарастающий итог

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

Это из категории задач на смещение значений.

Представьте, что у вас есть последовательность чисел 1, 2, 5, 10. Их общая сумма будет равна 18. Но сумма с приращением будет выглядеть иначе = ( 1 ) + (1 + 2) + (3 + 5) + (8 + 10) = 1 + 3 + 8 + 18 = 30.

Мы начинаем с первого элемента — с единицы — 1. У неё нет предыдущего значения, поэтому можно сказать что мы её ни с чем не складываем. Затем мы берём число 2, но у него уже есть предыдущее — это 1. Поэтому мы складываем 2 + 1. Получаем 3. И вот именно эту тройку мы будем считать «предыдущим» значением для сложения со следующим числом. И всё это повторится до самого конца. И все эти суммы пар ещё потом можно сложить отдельно.

В итоге 1, 2, 5, 10 превращается в 1, 3, 8, 18. Это именно те значения, что нам нужны.

Пример реализации в PostgreSQL:

select
  *
  , sum(b_interval) over (rows between unbounded preceding and current row)
from (
  select 1 as b_interval
  union all
  select 2
  union all
  select 5
  union all
  select 10
) as qwe

Я объединением создаю столбец таблицы с числами.

Изначально функция sum() является агрегирующей и предполагает возврат только одной записи — одного значения. Но в связке с over() она начинает вести себя как оконная функция и будет проставляться в соседнем столбце для каждой записи таблицы.

И вот тут начинается магия. Мы используем выражение «rows between unbounded preceding and current row«. Что оно означает? Дословно на русский можно перевести так:

Просуммируй значения в записях, каждый раз начиная с самой первой записи до текущей записи и запиши эту сумму в ячейку справа.

Первая запись: (1).                          Справа 1

Вторая запись: (1+2).                     Справа 3

Третья запись: (1+2+5).                Справа 8

Четвёртая запись: (1+2+5+10).  Справа 18

Если начало_рамки задано как UNBOUNDED PRECEDING, рамка начинается с первой строки раздела, а если конец_рамки определён как UNBOUNDED FOLLOWING, рамка заканчивается последней строкой раздела.

По умолчанию рамка определяется как RANGE UNBOUNDED PRECEDING, что равносильно расширенному определению RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Действуют также следующие ограничения: в качестве начала_рамки нельзя задать UNBOUNDED FOLLOWING, в качестве конца_рамки не допускается UNBOUNDED PRECEDING и конец_рамки не может идти в показанном выше списке указаний начало_рамки AND конец_рамки перед началом_рамки.

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Официальный сайт клиента DBeaver для СУБД PostgreSQL — https://dbeaver.io

Вызовы оконных функций — https://postgrespro.ru/docs/postgresql/15/sql-expressions#SYNTAX-WINDOW-FUNCTIONS