Сколько деталей нужно для заказа: раскрываем спецификацию в SQL

Для трёх комплектов нужны панели и коробки. Панели собираются на болтах, коробки тоже используют болты. Закупщик видит один и тот же артикул на разных уровнях и спрашивает, сколько заказать всего. Сумма количеств из справочника не поможет: четыре болта на панель нужно умножить и на число панелей в комплекте, и на количество заказанных комплектов.

Почему обычного JOIN недостаточно

Один JOIN раскрывает только ближайших детей изделия. Второй раскроет ещё один уровень, но завтра в справочнике появится промежуточный узел. Рекурсивный запрос повторяет один шаг: взять текущую потребность и заменить её потребностями дочерних компонентов. На каждом переходе количество умножается на норму расхода из спецификации. Когда дочерних строк больше нет, получается покупной компонент.

Модель здесь простая: одна активная спецификация, без отходов, альтернатив и собственного остатка полуфабрикатов. Каждая строка означает количество дочернего артикула на одну единицу родителя. Артикулы не содержат символа /. Это условие понадобится для проверки пути. Нельзя смешивать в одном поле штуки, килограммы и упаковки без приведения единиц.

Два маршрута к одному артикулу

В комплект входят две панели и одна коробка. Панель требует четыре болта, коробка — два. Потребность одного комплекта составляет десять болтов, а трёх — тридцать. Один артикул достигается двумя путями, и обе ветви должны участвовать в потребности. UNION ALL сохраняет кратность строк рекурсивного обхода. В этом примере path различает пути через panel и box, поэтому обычный UNION тоже сохранит эти две строки. Выбор UNION ALL прямо выражает правило: каждый маршрут расхода должен внести свой вклад; очистка дублей справочника выполняется отдельно.

Пример выполняется в SQLite; функции и синтаксис ниже относятся к этому диалекту.

WITH RECURSIVE
bom(parent,child,qty) AS (VALUES
 ('kit','panel',2),('kit','box',1),('panel','bolt',4),('box','bolt',2)),
need(item,qty,path) AS (
 VALUES ('kit',3,'/kit/')
 UNION ALL
 SELECT b.child,n.qty*b.qty,n.path||b.child||'/'
 FROM need n JOIN bom b ON b.parent=n.item
 WHERE instr(n.path,'/'||b.child||'/')=0
)
SELECT item,SUM(qty) AS required
FROM need n WHERE NOT EXISTS(SELECT 1 FROM bom b WHERE b.parent=n.item)
GROUP BY item ORDER BY item;

Результат содержит одну строку bolt, 30. Узлы kit, panel и box не попадают в финальную таблицу: для них есть собственная спецификация. Условие NOT EXISTS выбирает листья. Если панель покупается и одновременно может производиться, такого признака недостаточно: нужен явный способ обеспечения и выбранный маршрут на дату заказа.

Что означает защита от цикла

Столбец path хранит пройденные артикулы с разделителями. Проверка instr запрещает повторно войти в уже встреченный артикул на том же пути. Она останавливает бесконечный обход при ошибке kit → panel → kit. Однако остановка сама по себе не делает расчёт корректным: циклическая ветка окажется обрезана. Перед выпуском заявки на закупку циклы нужно выявлять отдельным контрольным результатом и блокировать расчёт.

При этом повторение bolt в разных ветвях нормально. Проверяется текущий путь, а не глобальный список посещённых узлов. Глобальная дедупликация приняла бы общий компонент за ошибку и потеряла бы часть потребности. Для крупных спецификаций полезно дополнительно ограничить глубину и выводить конкретный путь, на котором превышен допустимый уровень.

От валовой потребности к закупке

Полученные тридцать болтов — валовая потребность. Если на складе лежат восемь свободных болтов, простая чистая потребность равна двадцати двум. Но остатки полуфабрикатов меняют сам обход: готовая панель заменяет выпуск панели и потребность в её болтах. Поэтому нельзя сначала раскрыть всё до листьев, а затем вычесть только болты и считать задачу полностью решённой.

Разделите два отчёта. Первый объясняет нормативную потребность без запасов и используется для проверки состава. Второй учитывает уже зарезервированные остатки, незавершённое производство и открытые заказы. В нём важны сроки: поступление через месяц не обеспечивает сборку завтра. Для каждого уменьшения потребности должна сохраняться строка источника покрытия.

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

Следующий шаг

Если следующий отчёт требует обхода нескольких уровней и сверки промежуточных результатов, пригодится системная практика рекурсивных CTE. В платном «Продвинутом SQL» разбираются деревья и графы, сложные соединения и цельные кейсы с автоматической проверкой. Можно отработать технику обхода, прежде чем переносить её в производственную спецификацию. Откройте программу курса.