Categories: PostgreSQL

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

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

Сумма с приращением в PostgreSQL

Изначально функция 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

admin

Share
Published by
admin

Recent Posts

PostgreSQL | Как получить первый элемент из ячейки типа record?

При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…

1 год ago

PostgreSQL | Как отфильтровать JSONB-массив объектов по логическому значению ключа?

-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…

1 год ago

PostgreSQL | Как отбросить нули в типе numeric?

Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…

1 год ago

JavaScript | Как получить следующий день?

Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…

1 год ago

PostgreSQL | Агрегация JSONB-объектов в один объект

У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…

1 год ago

PostgreSQL | Группировка по 10 записей

Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…

1 год ago