Проверка товарного справочника в SQL: сироты и циклы категорий

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

Задайте допустимую структуру

В простой модели у категории один parent_id, корневая категория имеет NULL. ID уникален, ссылка ведёт на существующую категорию. Допускается несколько корней, если это согласовано с каталогом. Самоссылка недопустима, как и более длинная замкнутая цепочка.

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

Начните обход с каждого узла

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

Пример рассчитан на SQLite и целочисленные ID. Разделители / вокруг каждого числа не дают спутать ID 1 и 11. Для произвольных строковых ключей потребуется безопасное представление пути, в котором разделитель не может появиться без экранирования.

WITH RECURSIVE categories(id,parent_id) AS (
 VALUES (1,NULL),(2,1),(3,4),(4,3),(5,99)
), walk(start_id,node,parent_id,path,is_cycle) AS (
 SELECT id,id,parent_id,'/'||id||'/',0 FROM categories
 UNION ALL
 SELECT w.start_id,c.id,c.parent_id,w.path||c.id||'/',
        instr(w.path,'/'||c.id||'/') > 0
 FROM walk w JOIN categories c ON c.id=w.parent_id
 WHERE w.is_cycle=0
), issues AS (
 SELECT 'orphan' AS issue,c.id FROM categories c
 LEFT JOIN categories p ON p.id=c.parent_id
 WHERE c.parent_id IS NOT NULL AND p.id IS NULL
 UNION
 SELECT 'cycle_path',start_id FROM walk WHERE is_cycle=1
)
SELECT issue,id FROM issues ORDER BY issue,id;

Прочитайте результат как задания владельцу

Категории 3 и 4 образуют цикл, поэтому обе исходные цепочки получают cycle_path. Категория 5 ссылается на отсутствующий ID 99 и попадает в orphan. У категорий 1 и 2 нарушения нет.

Если к циклу ведёт ещё один дочерний узел, его цепочка тоже получит cycle_path. Это полезно: такая категория также не достигает корня. Для выделения только членов самого цикла понадобится дополнительный разбор повторившейся части пути. Не называйте все затронутые узлы непосредственными участниками цикла.

Исправляйте связь у источника

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

В большом справочнике обход от каждого узла может быть дорогим. Начните с плана выполнения и индекса уникального ID, затем измерьте объём рекурсивного результата. Ограничение глубины пригодно как защитный контроль, но его достижение должно стать отдельной ошибкой, а не успешным завершением проверки.

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

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

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

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

Откройте программу платного курса «Продвинутый SQL: сложные запросы и задачи для собеседований».

Продолжить разбор: Рекурсивный CTE и цикл в иерархии: как не уйти в бесконечный обход.