Рекурсивные CTE

Рекурсивные CTE (Common Table Expressions) в SQL позволяют работать с иерархическими или многослойными структурами данных, такими как организационные структуры, семейные деревья или графы. Они позволяют выполнять рекурсивные запросы, которые могут обращаться к самим себе для извлечения данных на разных уровнях иерархии.

Основы рекурсивных CTE

Рекурсивный CTE состоит из двух частей:

Anchor Member (Опорный элемент): начальная часть рекурсии, которая задает базовые данные.
Recursive Member (Рекурсивный элемент): часть, которая выполняется рекурсивно для поиска подчиненных данных.

Синтаксис

WITH RECURSIVE CTE_Name AS (
    -- Anchor Member: начальная часть рекурсии
    SELECT column1, column2, ...
    FROM table_name
    WHERE initial_conditions

    UNION ALL

    -- Recursive Member: рекурсивная часть
    SELECT t.column1, t.column2, ...
    FROM table_name t
    INNER JOIN CTE_Name cte ON t.some_column = cte.some_column
)
SELECT *
FROM CTE_Name;

Примеры использования рекурсивных CTE

Пример 1: Организационная структура

Предположим, у нас есть таблица employees с полями employee_id, name и manager_id. Мы хотим найти всех подчиненных для определенного менеджера, включая их подчиненных на всех уровнях.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Member: Начинаем с заданного менеджера
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id = 1234

    UNION ALL

    -- Recursive Member: Находим подчиненных для каждого сотрудника
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy
ORDER BY level, manager_id, employee_id;

В этом примере EmployeeHierarchy создает иерархию сотрудников, начиная с менеджера с employee_id равным 1234 и добавляя их подчиненных рекурсивно. Поле level показывает уровень иерархии.

Пример 2: Дерево категорий

Если у нас есть таблица categories с полями category_id, category_name и parent_category_id, мы можем использовать рекурсивный CTE для построения иерархии категорий.

WITH RECURSIVE CategoryHierarchy AS (
    -- Anchor Member: Начинаем с корневых категорий
    SELECT category_id, category_name, parent_category_id, 1 AS level
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Recursive Member: Находим подкатегории для каждой категории
    SELECT c.category_id, c.category_name, c.parent_category_id, ch.level + 1
    FROM categories c
    INNER JOIN CategoryHierarchy ch ON c.parent_category_id = ch.category_id
)
SELECT *
FROM CategoryHierarchy
ORDER BY level, parent_category_id, category_id;

Здесь CategoryHierarchy строит дерево категорий, начиная с корневых категорий (категории без родителя) и рекурсивно добавляя их подкатегории.

Пример 3: Построение пути в графе

Если у нас есть таблица graph_edges с полями source_id и target_id, описывающая ребра в графе, мы можем использовать рекурсивный CTE для нахождения всех путей между двумя узлами.

WITH RECURSIVE Path AS (
    -- Anchor Member: Начинаем с начальной точки
    SELECT source_id, target_id, CAST(source_id AS VARCHAR(255)) AS path
    FROM graph_edges
    WHERE source_id = 1

    UNION ALL

    -- Recursive Member: Находим пути для каждого узла
    SELECT e.source_id, e.target_id, CONCAT(p.path, '->', e.target_id)
    FROM graph_edges e
    INNER JOIN Path p ON e.source_id = p.target_id
)
SELECT *
FROM Path;

В этом примере Path строит все возможные пути в графе, начиная с узла с source_id равным 1 и рекурсивно добавляя последующие узлы в путь.

Пример 4: Вычисления факториала числа в SQL с использованием CTE

Предположим, мы хотим вычислить факториал числа 5.

Для SQL Server и PostgreSQL:

WITH RECURSIVE FactorialCTE (n, factorial) AS (
    -- Начальная запись: факториал 0 равен 1
    SELECT 0 AS n, 1 AS factorial
    UNION ALL
    -- Рекурсивная часть: n! = n * (n-1)!
    SELECT n + 1, (n + 1) * factorial
    FROM FactorialCTE
    WHERE n < 5  -- Условие окончания рекурсии, например, до 5!
)
SELECT n, factorial
FROM FactorialCTE;

Объяснение:

Начальная запись (анкер): Запуск с n = 0 и factorial = 1, поскольку 0! = 1.
Рекурсивная часть: Каждая последующая запись вычисляется как текущий факториал, умноженный на n+1n + 1n+1. Например, для вычисления 5!5!5! SQL выполняет:
- 0! = 1
- 1! = 1 * 1 = 1
- 2! = 2 * 1 = 2
- 3! = 3 * 2 = 6
- 4! = 4 * 6 = 24
- 5! = 5 * 24 = 120
Условие окончания: Мы указываем WHERE n < 5, чтобы завершить рекурсию, как только достигнем n = 5.

Результат

n | factorial
--|----------
0 | 1
1 | 1
2 | 2
3 | 6
4 | 24
5 | 120

Особенности и советы

Базовые случаи: Обязательно убедитесь, что в Anchor Member есть строки, которые определяют начальные условия рекурсии. Без этого рекурсия может не завершиться или создать бесконечный цикл.
Предел рекурсии: В большинстве СУБД существуют настройки для ограничения глубины рекурсии (например, maxRecursion в SQL Server). Эти настройки могут помочь предотвратить бесконечные циклы.
Производительность: Рекурсивные CTE могут быть медленными для больших и сложных иерархий. Использование индексов и оптимизация запросов может помочь улучшить производительность.
Циклы в данных: Будьте внимательны с циклическими зависимостями в данных, так как это может привести к бесконечным циклам. Многие СУБД имеют встроенные механизмы для обработки циклов.

Заключение

Рекурсивные CTE — это мощный инструмент для работы с иерархическими и многослойными структурами данных. Они позволяют создавать и обрабатывать сложные иерархии, деревья и графы, что делает их полезными для множества аналитических задач.

PreviousСвязанные CTE для фильтрации данных NextОрганизация кода запросов с помощью CTE

Last updated 17 days ago