Описание
В первой статье была описана задача распределения сумм затрат между отделами по заданным правилам, с учётом того что отделы могут передавать свои затраты на другие отделы. Задача этой и следующей статей — показать, как эту задачу можно реализовать относительно просто и легко, не прибегая к созданию больших приложений.
В данной статье будет описана структура PostgreSQL-базы данных и реализация данной задачи с помощью SQL в варианте PL/pgSQL. Реализация на Apache Spark будет описана в следующей статье.
В качестве примера будут созданы настройки распределения и входные данные для:
Примера из первой статьи, распределения одной входной суммы затрат 1000 на 3 отдела.
Распределение нескольких тысяч входных сумм затрат на произвольное количество отделов. Скрипт будет настраиваемый.
Структура базы данных
Общее описание
Все примеры будут делаться относительно данных описанных в первой статье.
Скрипт создания базы данных для данной задачи находится в репозитории.
Правила распределения
В базе данных предусмотрены наборы правил распределения сумм затрат. Список наборов правил распределения хранится в таблице rules_headers. В ней хранится список правил и сумма, начиная с которой пришедшие значения могут быть переданы на другие отделы.
Команду создания таблицы rules_headers можно найти в репозитории.
Содержимое таблицы rules_headers для примера описанного в первой статье показано в таблице:
department_name | id | limit |
|---|---|---|
Отдел 1 | 1 | 100 |
Отдел 2 | 2 | 100 |
Отдел 3 | 3 | [NULL] |
Пришедшие суммы должны распределяться по определенным формулам. Для хранения формул и связи их с набором правил создана таблица rules_rows. В ней хранится связь с набором правил (заголовком), признак отключения дальнейшего распределения пришедших сумм и ссылка на отдел, на который распределяется пришедшая сумма. В данной реализации отдел равен группе затрат.
Содержимое таблицы rules_rows для примера описанного в первой статье показано в таблице:
id | formula | disable_child_creation | rule_header_id | dest_rule_id |
|---|---|---|---|---|
1 | {X}*0.25 | true | 1 | 1 |
2 | {X}*0.25 | [NULL] | 1 | 2 |
3 | {X}*0.50 | [NULL] | 1 | 3 |
4 | {X}*0.30 | true | 2 | 2 |
5 | {X}*0.60 | [NULL] | 2 | 1 |
6 | {X}*0.10 | [NULL] | 2 | 3 |
7 | {X} | true | 3 | 3 |
Поле rule_header_id связывает правило распределения суммы с набором правил; в этом примере это синоним отдела.
Пришедшие суммы должны распределяться по формулам. Формулы описаны в простом формате где {X} заменяется на сумму для распределения на заголовок/отдел.
Поле disable_child_creation показывает, нужно или нет запускать дальнейшие распределения, если пришедшая сумма больше порога указанного в записи заголовка/отдела.
Поле dest_rule_id указывает, на какой отдел переносить рассчитанную сумму затрат.
Смысл формул можно посмотреть в первой статье в разделе «Графическое описание».
Таблица входных сумм для распределения
Расчет распределения сумм затрат нужно начинать с набора этих сумм. Сразу же нужно предусмотреть способ проверки результатов.
Для хранения набора пришедших для распределения затрат служит таблица costs_input.
Содержимое таблицы costs_input для примера описанного в первой статье показано в таблице:
id | department_name | calc_name | cost_sum |
|---|---|---|---|
1 | Отдел 1 | Пример из статьи | 1,000 |
Поле department_name содержит имя отдела для распределения затрат. Это поле аналогично полю department_name таблицы rules_headers.
Поле cost_sum содержит сумму, которую нужно распределить.
Результаты распределения
Результаты распределения затрат сохраняются в таблице costs_output.
Пример результатов распределения затрат для входных данных, описанных в первой статье, показан в таблице:
id | cost_input_id | rules_rows_id | parent_row_id | summ |
|---|---|---|---|---|
1 | 1 | 1 | [NULL] | 250 |
2 | 1 | 2 | [NULL] | 250 |
3 | 1 | 3 | [NULL] | 500 |
4 | 1 | 4 | 2 | 75 |
5 | 1 | 5 | 2 | 150 |
6 | 1 | 6 | 2 | 25 |
7 | 1 | 1 | 5 | 37.5 |
8 | 1 | 2 | 5 | 37.5 |
9 | 1 | 3 | 5 | 75 |
Поле cost_input_id содержит ссылку на строку входных сумм распределения. Наличие этой строки позволит в дальнейшем легко проверить результат распределения.
Поле rules_rows_id содержит ссылку на строку распределения из таблицы rules_rows.
Затраты распределяются иерархически до момента прихода суммы в строку затрат где стоит запрет на дальнейшее распределение затрат или при получении суммы затрат меньше, чем лимит, после которого затраты распределяются. Соответственно, нужно хранить связь между начальными и подчиненными затратами. Для этой цели служит поле parent_row_id, в котором содержится ссылка на предыдущий этап расчета затрат, если текущий уровень расчета второй и ниже. При расчете первого уровня (сумм из таблицы costs_input) в этом поле находится NULL.
Поле summ хранит сумму распределенных затрат.
Эта таблица содержит суммы, аналогичные суммам, описанным в примере распределения затрат из первой статьи.
Реализация распределения затрат с помощью SQL
При создании базы данных с примером распределения было обнаружено, что описанную задачу можно легко решить с помощью стандартных средств PostgreSQL.
Пример решения с помощью SQL приведён в скрипте репозитория.
Так как данное SQL-решение будет сравниваться с решением на Apache Spark, то при его реализации были задействованы преимущества SQL.
Краткое описание SQL решения
SQL-решение в скрипте работает следующим образом:
Из таблицы costs_input берутся входные данные для распределения и сразу же объединяются с правилами, что позволяет сразу же провести распределение первого уровня.
Для расчета суммы затрат берется формула распределения затрат из таблицы rules_rows, в ней заменяются символы {X} на пришедшую сумму и полученная формула вычисляется через SQL-команду.
Запускается цикл который выбирает строки самого нижнего уровня (у которых нет подчиненных строк через поле parent_row_id), сумма затрат по которым больше порога начала распределения затрат, и строка правил по которым не имеет признака остановки расчетов. Пока такие строки находятся их значения распределяются согласно формуле из таблицы rules_rows.
Так как в процессе применения формул распределения затрат неизбежно возникают ошибки округления они исправляются с помощью SQL-команды UPDATE, объединенной с логикой расчета ошибок округления через CTE. Это позволяет выполнить исправления ошибок округления по всем рассчитанным значениям одной командой. Логика поиска ошибок округления будет описана в разделе проверки расчетов.
Почему 4-й пункт нельзя считать «подгонкой результатов» под нужные значения. При исправлении ошибок округления ищется разница между входящей суммой затрат и уже распределенной суммой затрат по всем отделам. То есть сравнивается результат распределения с суммой исходных затрат. На момент исправления ошибок округления затраты уже распределены на произвольное количество отделов. И исправляется именно последний распределенный уровень затрат.
Проверка результатов
Полученные расчеты нужно проверить. Для проверки расчетов служит скрипт проверки tester.sql. У него следующая краткая логика работы:
Собираются полученные суммы результатов распределения для каждой строки входных сумм затрат.
Для каждой суммы входных затрат выбирается первое распределение.
Полученный результат объединяется с таблицей входных сумм.
Фильтруются строки в которых сумма пришедших затрат не равна сумме распределенных затрат привязанных к пришедшим затратам.
Описание скриптов загрузки данных и запуска расчетов
В SQL-решении представлены два скрипта загрузки исходных данных для расчета.
Скрипт, который загружает данные, аналогичные описанному примеру в первой статье.
Скрипт, позволяющий создать в базе несколько тысяч строк для распределения затрат и несколько тысяч правил их распределения с нужным количеством долей распределения на другие отделы.
Описание скрипта создания большого объема данных для расчета
Скрипт создания большого объема данных для расчета расположен в репозитории.
Переменная departments_count позволяет указать количество отделов и строк входных данных для распределения.
Порядок тестирования SQL-решения
Для запуска SQL решения необходимо выполнить следующие действия:
Создать пустую базу данных и выполнить в ней скрипт создания структуры базы данных.
Запустить скрипт создания примера из первой статьи или скрипт создания большого объема входных данных для расчета. Можно его назвать скрипт нагрузочного тестирования. При желании в скрипте нагрузочного тестирования можно увеличить или уменьшить переменную departments_count что позволит увеличить или уменьшить нагрузку на PostgreSQL-сервер.
Запустить скрипт распределения затрат. По сути этот скрипт и является SQL-решением для распределения затрат.
Запустить скрипт проверки результатов.
Выводы после создания SQL решения
Созданное SQL-решение показало, что решить задачу распределения затрат по правилам можно с помощью простых средств доступных в обычном PostgreSQL. На компьютере Core i5 10-й серии расчет затрат для 5000 входящих строк и распределения их на 5—10 других отделов что вызывает каскадное распределение затрат занимает меньше 10 секунд.
SQL-решение не потребовало использование рекурсивных SQL-запросов.
Исправление ошибок округления удалось выполнить с помощью одного SQL-запроса.
Однако есть и выявленные сложности. SQL-решение начинает работать значительно медленнее после 10 тысяч строк. При расчете распределения всегда используется только один процессор.
В целом можно сказать что PostgreSQL может использоваться при построении приложений распределения затрат при условии, что количество входящих данных не будет превышать десятки тысяч строк и правила не будут разветвляться более чем на 10 направлений.