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. Ставь 👍 если узнал новое