Аналитика данных / Data Study: “CTE + рекурсия: когда нужно «пройтись» по иерархии данных Есть задачи,…”

CTE + рекурсия: когда нужно «пройтись» по иерархии данных

Есть задачи, где данных недостаточно «плоских» таблиц: нужно подняться по иерархии (от подчинённого к руководителю), спуститься по дереву категорий или пройти по цепочке транзакций. Тут выручают рекурсивные CTE — мощная штука, про которую многие вспоминают только тогда, когда обычный JOIN уже не спасает.

Конструкция простая по форме, но очень эффективная:

WITH RECURSIVE имя_cte AS (
    -- Базовый случай (с чего начинаем)
    SELECT ...
    UNION ALL
    -- Рекурсивный шаг (как переходим дальше)
    SELECT ... FROM имя_cte JOIN ...
)
SELECT * FROM имя_cte;

Есть типичный формат иерархической таблицы, где фиксируется сам объект и его родительский id. К примеру давайте посмотрим на категории товаров, когда например для товара есть целый путь категорий ("Для мужчин" - "Верхняя одежда" - "Зима" - "Куртки"). В итоге с помощью рекурсии для каждого объекта можно сразу достать весь путь его родительских категорий).

WITH RECURSIVE category_path AS (
    SELECT
        category_id,
        parent_category_id,
        name,
        name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL  -- корневые категории

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id,
        c.name,
        cp.full_path || ' > ' || c.name,
        cp.depth + 1
    FROM category_path cp
    JOIN categories c ON c.parent_category_id = cp.category_id
)
SELECT category_id, full_path, depth
FROM category_path;

Другими словами, рекурсия с cte - это некая реализация цикла с помощью SQL. Ставь 👍 если узнал новое