Описание
В первой статье была описана задача распределения сумм затрат между отделами по заданным правилам, с учётом того что отделы могут передавать свои затраты на другие отделы. Задача этой и следующей статей — показать, как эту задачу можно реализовать относительно просто и легко, не прибегая к созданию больших приложений.
В данной статье будет описана структура 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 направлений.
ссылка на оригинал статьи https://habr.com/ru/articles/1040376/