Спрощення запитів за допомогою SQL-загальних виразів таблиць (CTE)

SQL-загальні вирази таблиць (CTE) — це тимчасові набори результатів, які спрощують складні запити, розбиваючи їх на менші, зрозуміліші частини. Вони покращують зручність читання, дають змогу використовувати рекурсію та дозволяють повторно використовувати логіку в межах одного запиту.

У цій статті ми розглянемо потужність CTE, їх синтаксис і практичні приклади, які допоможуть вам освоїти цю універсальну функцію.

Що таке CTE?

CTE — це іменований тимчасовий набір результатів, визначений у межах SQL-запиту. Він існує лише під час виконання запиту і може використовуватися для:

• Спрощення складних запитів.
• Покращення зручності читання запитів.
• Реалізації рекурсивної логіки для ієрархічних або послідовних даних.

Синтаксис для CTE

Основний CTE

WITH cte_name AS (  
 SELECT columns  
 FROM table  
 WHERE condition  
)  
SELECT * FROM cte_name;

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

WITH RECURSIVE cte_name AS (  
 anchor_query  
 UNION ALL  
 recursive_query  
)  
SELECT * FROM cte_name;

Практичні приклади

Розглянемо таблицю employees (співробітники):

| employee_id | employee_name | department | salary |  
|-------------|---------------|------------|--------|  
| 1 | John | Sales | 50000 |  
| 2 | Jane | Sales | 70000 |  
| 3 | Bob | IT | 60000 |  
| 4 | Alice | HR | 52000 |

Приклад 1: Спрощення складного запиту

Знайдемо співробітників, які отримують більше середньої заробітної плати у своїй департаменті:

Без CTE:

SELECT employee_name, department, salary  
FROM employees e  
WHERE salary > (  
 SELECT AVG(salary)  
 FROM employees  
 WHERE department = e.department  
);

З CTE:

WITH AvgSalaries AS (  
 SELECT department, AVG(salary) AS avg_salary  
 FROM employees  
 GROUP BY department  
)  
SELECT e.employee_name, e.department, e.salary  
FROM employees e  
JOIN AvgSalaries a ON e.department = a.department  
WHERE e.salary > a.avg_salary;

CTE покращує зручність читання, розділяючи обчислення середньої зарплати.

Приклад 2: Використання кількох CTE

Щоб вивести співробітників, які отримують більше 50,000 доларів, і загальні суми по департаментам:

WITH HighEarners AS (  
 SELECT employee_name, department, salary  
 FROM employees  
 WHERE salary > 50000  
),  
DepartmentTotals AS (  
 SELECT department, SUM(salary) AS total_salary  
 FROM employees  
 GROUP BY department  
)  
SELECT h.employee_name, h.department, h.salary, d.total_salary  
FROM HighEarners h  
JOIN DepartmentTotals d ON h.department = d.department;

Приклад 3: Рекурсивний CTE для ієрархічних даних

Знайдемо ієрархію звітності співробітників:

Таблиця:

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

Рекурсивний запит:

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  
 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 |

Приклад 4: Комбінування CTE з віконними функціями

Знайдемо рейтинг співробітників у кожному департаменті за заробітною платою:

WITH RankedSalaries AS (  
 SELECT employee_name, department, salary,  
 RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank  
 FROM employees  
)  
SELECT * FROM RankedSalaries  
WHERE rank = 1;

Результат:

| employee_name | department | salary | rank |  
|---------------|------------|--------|------|  
| Jane | Sales | 70000 | 1 |  
| Bob | IT | 60000 | 1 |  
| Alice | HR | 52000 | 1 |

Кращі практики використання CTE

1.
Описуйте CTE зрозуміло: Використовуйте чіткі, значущі імена для покращення зручності читання.
2. Обмежуйте глибину рекурсії: Використовуйте умову WHERE у рекурсивних запитах, щоб уникнути безкінечних циклів.
3. Оптимізуйте CTE: Переконайтесь, що CTE ефективні, обмежуючи кількість оброблених рядків.

Типові помилки, яких варто уникати

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

Висновок

SQL-загальні вирази таблиць (CTE) — це потужний інструмент для спрощення складних запитів, реалізації рекурсії та покращення зручності читання запитів. Освоївши CTE, ви зможете писати більш ефективний і підтримуваний SQL-код.

У наступній статті ми розглянемо SQL Full-Text Search, зосереджуючи увагу на техніках запитування та індексації текстових даних для швидких і точних можливостей пошуку.

Перекладено з: Simplifying Queries with SQL Common Table Expressions (CTEs)