Рекурсивный CTE: сумма продаж по ветвям каталога

В каталоге есть категория «Обувь» и вложенная «Ботинки». Продажа ботинок должна войти и в строку «Ботинки», и в итог «Обувь», а также в общий итог «Одежда». Если соединить продажи только с непосредственной категорией товара, у родительских строк получится неполная выручка.

Задача состоит из двух частей. Сначала для каждой категории надо получить все категории ниже неё, включая саму себя. Затем присоединить к этим потомкам продажи и сложить суммы. Рекурсивный CTE строит пары (ancestor_id, descendant_id): начальная часть даёт пару категории с собой, рекурсивная добавляет её детей, потом детей детей.

Почему категория должна быть собственным потомком

У «Обуви» могут быть продажи, прикреплённые прямо к ней, и продажи во вложенных «Ботинках». Если исходные пары содержат только потомков ниже по дереву, прямые продажи родителя потеряются. Начальная часть SELECT id, id включает их естественным образом.

CREATE TABLE categories (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT);
CREATE TABLE sales (category_id INTEGER, revenue INTEGER);
INSERT INTO categories VALUES
 (1,NULL,'Clothing'),(2,1,'Shoes'),(3,2,'Boots'),(4,1,'Hats');
INSERT INTO sales VALUES (2,100),(3,200),(4,50);
WITH RECURSIVE paths(ancestor_id, descendant_id) AS (
 SELECT id, id FROM categories
 UNION ALL
 SELECT p.ancestor_id, c.id
 FROM paths AS p JOIN categories AS c ON c.parent_id=p.descendant_id
)
SELECT a.id, a.name, COALESCE(SUM(s.revenue),0) AS branch_revenue
FROM categories AS a
JOIN paths AS p ON p.ancestor_id=a.id
LEFT JOIN sales AS s ON s.category_id=p.descendant_id
GROUP BY a.id,a.name ORDER BY a.id;

Результат: Clothing = 350, Shoes = 300, Boots = 200, Hats = 50. У «Одежды» сумма 100+200+50. У «Обуви» — 100+200. Одна продажа ботинок попадает в несколько строк отчёта, потому что каждая строка отвечает на вопрос о своей ветви. Складывать потом эти четыре итоговые строки между собой нельзя: это намеренно перекрывающиеся агрегаты.

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

Когда дерево перестаёт быть деревом

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

Для большого каталога рекурсивный проход на каждый отчёт может оказаться дорогим. Часто заранее сохраняют таблицу всех пар «предок—потомок» и обновляют её при изменении иерархии. Логика суммирования остаётся той же. Начинать оптимизацию стоит после теста на маленьком дереве, где ручной результат известен.

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

Иерархия может меняться: сегодня «Ботинки» вложены в «Обувь», а в прошлом месяце были отдельной ветвью. Запрос к текущему справочнику пересчитает прошлую выручку по сегодняшней структуре. Если нужен отчёт «как тогда», сохраните историю родительских связей и выбирайте версию на дату продажи. Это другое измерение времени, которое нельзя восстановить из одного поля parent_id.

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

Если категория без родителя не обязательно корень, а ошибка импорта, найдите такие строки до построения отчёта. Рекурсия начнётся и от них, но общая строка «Одежда» уже не будет включать их продажи. Сверка прямой выручки всех категорий с суммой корневых ветвей покажет расхождение и заставит проверить потерянные связи.

На курсе «Продвинутый SQL» рекурсивные CTE соединяются с проверками качества данных и расчётами витрины интернет-магазина. Студент пишет запросы на SQLite и проверяет их на разных входах. Посмотрите программу, чтобы освоить и обход дерева, и последующую сверку финансового итога.

Отдельная статья показывает, как выявить цикл в рекурсивном CTE, если справочник уже повреждён.