PostgreSQL рекурсивный запрос

Рассмотрим создание рекурсивного запроса на Postgresql .  

Рекурсивный запрос необходим, для вывода данных на основе предыдущих строк в выборке. Реализуется он с помощью оператора WITH.

Общая схема рекурсивного запроса:

WITH RECURSIVE t AS (
    нерекурсивная часть      (1)
    UNION ALL
    рекурсивная часть          (2)
)
SELECT * FROM t;                (3)

Чтобы не мучать Вас теорией, перейдем сразу к практике. С помощью рекурсивного запроса, можно вывести сумму чисел от 1 до 10.

 Также с помощью, рекурсивного запроса можно решать, более сложные математические задачи. Например выведем числа Фибоначчи

Таким образом, рекурсивный запрос, может помочь в решении, самых разнообразных задач. Осталось только, научиться их писать...

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

Приведу пример из моей практики: "Расчет сальдовки с помощью postgresql".  

Работал я еще тогда мидл-разработчиком, в достаточно известной фирме ООО "Эттон" в городе Казани. Занималась данная компания автоматизацией ЖКХ. Наша команда разрабатывала продукт "Регион", для ведения капитального ремонта. Я в частности отвечал, за модуль "Биллинг".

Модуль "Биллинг" обрабатывал данные по собственникам в БД под управлением PostgreSQL. Обработка данных происходила на стороне сервера, с помощью микросервисов. И чтобы получить данные по входящему и исходящему сальдо, для отчетов. Нам разработчикам, приходилось писать запрос, обрабатывать данные на сервере, и добавлять уже обработанные данные по расчитанной сальдовке

Все это приводило к тому, что тратили лишнее время на разработку функционала и производительность данных скриптов была крайне низкой.

Чтобы было, более понятно, приведу таблицу, как должен вестись расчет:

Период Входящее сальдо Начислено Оплачено Пени Исходящее сальдо
февраль 2017 0.00 100.00 60.00 0.00 40.00
март 2017 40.00 100.00 0.00 0.00 140.00
апрель 2017 140.00 100.00 0.00 3.00 243.00 

Первым решением, было хранить уже посчитанные данные, в аггрегирующей таблице.

Добавили триггеры, чтобы пересчитывать данные. Данные пересчитывались, опять же средствами микросервисов. Но при большом количестве изменении данных, сервера не выдерживали нагрузки. Расчет происходил медленно. Напомню, на самом загруженном регионе, у нас ежедневно добавлялось около 500 тысяч записей. 

В качестве эксперимента, попробовал сделать расчет с помощью рекурсивного запроса. Но расчет происходил еще медленнее. Но после того как оптимизировал запрос, добавил ряд индексов и изменил настройки postgresql. Все заработало. Радости не было предела, с помощью данного метода избавились, от множества проблем. Это была любовь по расчету =)

 Ниже приведу, с какими проблемами я столкнулся при написании запроса, и как их решил:

Приведу упрощенную схему структуры таблицы "billing_bill_account_operation", где хранятся данные по начислениям собствеников.

Для облегчения понимания, как писать запрос, разделим его на несколько этапов.

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

Получим следующие данные:

account_id period in_saldo credited paid peni out_saldo
1 2017-01-01 0 300.00 150.00 0.00 450.00
2 2017-01-01 0 240.00 0.00 0.00 240.00
3 2017-01-01 0 180.00 0.00 0.00 180.00

Вторым этапом, создадим запрос, для рекурсивной части, с одной итерацией.

 Данный запрос вернет данные за следующий месяц, то есть за февраль 2017 года.

account_id period credited paid peni
1 2017-02-01 0.00 50.00 0.00
2 2017-02-01 80.00 0.00 0.00
3 2017-02-01 60.00 0.00 0.00

А теперь, третьим этапом, попробуем совершить "магию", соединить нерекурсивную часть, с рекурсивной частью.

Для этого воспользуемся конструкцией WITH RECURSIVE. Итоговый запрос, будет выглядеть следующим образом.

 Здесь, самое сложное было, определить, как переходить на следующую запись. Решили данный вопрос переходом  на строчку со следующим месяцем. А заканчивается итерация после того, как заканчиваются записи сгрупированные по месяцам. Еще нужно иметь ввиду, если в нерекурсивной части запрос вернул например 3 записи, значит будет 3 отдельных итерации.

В итоге, получим следующие данные:

account_id period in_saldo credited paid peni out_saldo
1 2017-01-01 0 300.00 150.00 0.00 450.00
1 2017-02-01 450.00 0.00 50.00 0.00 500.00
2 2017-01-01 0 240.00 0.00 0.00 240.00
2 2017-02-01 240.00 80.00 0.00 0.00 320.00
2 2017-03-01 320.00 80.00 0.00 2.00 402.00
3 2017-01-01 0 180.00 0.00 0.00 180.00
3 2017-02-01 180.00 60.00 0.00 0.00 240.00

 

 

 

 

 

 При написании статьи, были использованы следующие ресурсы:

https://postgrespro.ru/docs/postgrespro/9.6/queries-with

https://habrahabr.ru/company/postgrespro/blog/318398/

Если вам помогла статья, пожалуйста перейдите по одному из рекламных блоков, расположенных на сайте. Таким образом вы поддержите проект. Спасибо

© Krenar 2020. All right reserved.