Дослідження рекурсивних запитів SQL: Робота з ієрархічними даними

Рекурсивні запити SQL дозволяють працювати з ієрархічними або рекурсивними структурами даних, такими як організаційні схеми, дерева категорій або родові генеалогії. Використовуючи Загальні Табличні Вирази (CTE), рекурсивні запити дозволяють ефективно обходити і маніпулювати даними з батьківсько-дитячими відносинами.

У цій статті ми розглянемо, що таке рекурсивні запити, їх синтаксис та практичні приклади роботи з ієрархічними даними.

Що таке рекурсивні запити SQL?

Рекурсивний запит — це запит, який посилається на сам себе для обробки ієрархічних або рекурсивних даних. Такі запити створюються за допомогою рекурсивних Загальних Табличних Виразів (CTE), які складаються з двох частин:

1. Якорний запит: Початковий запит, який отримує базовий випадок.
   2.
   Рекурсивний запит: Запит, який посилається на сам себе для обробки залишкових рівнів.

Синтаксис для рекурсивних CTE

WITH RECURSIVE cte_name (column1, column2, ...) AS (  
 anchor_query  
 UNION ALL  
 recursive_query  
)  
SELECT * FROM cte_name;

Практичний приклад: Ієрархія співробітників

Розглянемо наступну таблицю employees:

| employee_id | employee_name | manager_id |  
|-------------|---------------|------------|  
| 1 | Alice | NULL |  
| 2 | Bob | 1 |  
| 3 | Carol | 1 |  
| 4 | David | 2 |  
| 5 | Eve | 2 |

Мета

Отримати всю ієрархію підзвітних співробітників для конкретного керівника, починаючи з верхнього рівня.

Приклад 1: Простий рекурсивний запит

Знайти всіх співробітників, які підпорядковуються Alice (employee_id = 1):

WITH RECURSIVE EmployeeHierarchy AS (  
 -- Якорний запит: Вибір керівника верхнього рівня  
 SELECT employee_id, employee_name, manager_id  
 FROM employees  
 WHERE manager_id IS NULL  

 UNION ALL  

 -- Рекурсивний запит: Вибір співробітників, які підпорядковуються поточному рівню  
 SELECT e.employee_id, e.employee_name, e.manager_id  
 FROM employees e  
 INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id  
)  
SELECT * FROM EmployeeHierarchy;

Результат:

| employee_id | employee_name | manager_id |  
|-------------|---------------|------------|  
| 1 | Alice | NULL |  
| 2 | Bob | 1 |  
| 3 | Carol | 1 |  
| 4 | David | 2 |  
| 5 | Eve | 2 |

Приклад 2: Додавання рівнів до ієрархії

Щоб додати стовпець level, що вказує глибину в ієрархії:

WITH RECURSIVE EmployeeHierarchy AS (  
 SELECT employee_id, employee_name, manager_id, 1 AS level  
 FROM employees  
 WHERE manager_id IS NULL  

 UNION ALL  

 SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1  
 FROM employees e  
 INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id  
)  
SELECT * FROM EmployeeHierarchy;

Результат:

| employee_id | employee_name | manager_id | level |  
|-------------|---------------|------------|-------|  
| 1 | Alice | NULL | 1 |  
| 2 | Bob | 1 | 2 |  
| 3 | Carol | 1 | 2 |  
| 4 | David | 2 | 3 |  
| 5 | Eve | 2 | 3 |

Приклад 3: Обмеження глибини рекурсії

Використовуємо обмеження глибини, щоб обмежити кількість оброблюваних рівнів:

WITH RECURSIVE EmployeeHierarchy AS (  
 SELECT employee_id, employee_name, manager_id, 1 AS level  
 FROM employees  
 WHERE manager_id IS NULL  

 UNION ALL  

 SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1  
 FROM employees e  
 INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id  
 WHERE eh.level < 2 -- Обмеження до 2 рівнів глибини  
)  
SELECT * FROM EmployeeHierarchy;

Результат:

| employee_id | employee_name | manager_id | level |  
|-------------|---------------|------------|-------|  
| 1 | Alice | NULL | 1 |  
| 2 | Bob | 1 | 2 |  
| 3 | Carol | 1 | 2 |

Кращі практики для рекурсивних запитів

1.
Використовуйте UNION ALL обережно: Віддавайте перевагу UNION ALL замість UNION для кращої продуктивності, оскільки UNION видаляє дублікати.
2. Обмежте глибину рекурсії: Завжди вказуйте ліміт глибини, щоб уникнути нескінченних циклів та надмірного використання ресурсів.
3. Індексуйте стовпці батьківських і дочірніх елементів: Переконайтеся, що стовпці, що використовуються в умовах JOIN (наприклад, manager_id), індексовані для досягнення оптимальної продуктивності.

Поширені помилки, яких слід уникати

1. Нескінченна рекурсія: Забування включити умову зупинки в рекурсивному запиті може призвести до нескінченних циклів.
2. Складні базові запити: Спрощуйте якорний запит для ясності та ефективності.
3. Надмірне використання рекурсивних запитів: Використовуйте їх лише коли це необхідно; плоскі дані часто можна обробляти більш ефективно стандартними запитами.

Висновок

Рекурсивні SQL-запити є незамінними для навігації та маніпулювання ієрархічними даними.
Оволодівши рекурсивними CTE (Common Table Expressions), ви зможете ефективно працювати з складними зв'язками, такими як організаційні діаграми, дерева категорій та багато іншого.

У наступній статті ми розглянемо роботу з JSON в SQL, занурюючись у техніки запитів, оновлення та маніпулювання JSON-даними в сучасних реляційних базах даних.

Перекладено з: Exploring SQL Recursive Queries: Working with Hierarchical Data