SQL - Общее табличное выражение (CTE)

Привет, будущие маги SQL! Сегодня мы отправимся в увлекательное путешествие в мир Общих табличных выражений, или CTE для краткости. Не беспокойтесь, если вы новички в программировании – я проведу вас через это шаг за шагом, как я делал это для countless студентов на протяжении многих лет моей преподавательской деятельности. Так что возьмите любимый напиток, устройтесь поудобнее и погружайтесь с нами!

Общее табличное выражение в SQL

Представьте, что вы организовываете большое мероприятие (потому что кто не любит хорошие метафоры?). У вас есть список задач, и вы хотите разбить их на более мелкие, управляемые части. Вот что делает Общее табличное выражение в SQL – оно помогает нам разбить сложные запросы на более простые и читаемые части.

CTE – это как временный named результат, к которому вы можете обратиться в SELECT, INSERT, UPDATE, DELETE или MERGE операторе. Он определяется в scope выполнения одного оператора. Представьте его как создание временной таблицы, существующей только для вашего запроса.

Давайте рассмотрим простой пример:

WITH cte_example AS (
SELECT 'Привет, CTE!' AS greeting
)
SELECT greeting FROM cte_example;

В этом примере:

1. Мы начинаем с ключевого слова WITH, которое сигнализирует о начале нашего CTE.
2. Мы даем нашему CTE имя: cte_example.
3. Мы определяем, что будет содержать наш CTE: в этом случае, простой SELECT оператор, который создает столбец 'greeting' со значением 'Привет, CTE!'.
4. После определения CTE, у нас есть основной запрос, который использует CTE.

When you run this, you'll see:

| greeting    |
|-------------|
| Привет, CTE! |

Не правда ли, здорово? Мы только что создали nuestro primer CTE!

Клаузула WITH в MySQL

Теперь давайте поговорим о клаузуле WITH в MySQL. Это магическая палочка, которая оживляет наши CTE. Общий синтаксис выглядит так:

WITH cte_name [(column_list)] AS (query)
SELECT * FROM cte_name;

Давайте рассмотрим более практический пример. Допустим, у нас есть таблица сотрудников:

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Алиса', 'HR', 50000),
(2, 'Боб', 'IT', 60000),
(3, 'Чарли', 'Финансы', 55000),
(4, 'Дэвид', 'IT', 65000),
(5, 'Эва', 'HR', 52000);

Теперь давайте используем CTE, чтобы найти среднюю зарплату по отделам:

WITH dept_avg_salary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM dept_avg_salary
ORDER BY avg_salary DESC;

Этот запрос даст нам:

| department | avg_salary |
|------------|------------|
| IT         | 62500.00   |
| Финансы    | 55000.00   |
| HR         | 51000.00   |

Здесь мы использовали CTE для расчета средней зарплаты по каждому отделу, а затем выбрали из этого CTE, чтобы отобразить результаты. Это как если бы мы создали временную таблицу со средними зарплатами, которую затем использовали в основном запросе.

CTE из нескольких таблиц

CTE не ограничиваются одной таблицей. Мы можем использовать несколько таблиц в определениях CTE, как и в обычных запросах. Давайте добавим таблицу отделов к нашему примеру:

CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50),
location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'HR', 'Нью-Йорк'),
(2, 'IT', 'Сан-Франциско'),
(3, 'Финансы', 'Чикаго');

Теперь давайте используем CTE, чтобы объединить информацию из обеих таблиц:

WITH dept_info AS (
SELECT e.department,
AVG(e.salary) AS avg_salary,
d.location
FROM employees e
JOIN departments d ON e.department = d.name
GROUP BY e.department, d.location
)
SELECT * FROM dept_info
ORDER BY avg_salary DESC;

Это даст нам:

| department | avg_salary | location      |
|------------|------------|---------------|
| IT         | 62500.00   | Сан-Франциско |
| Финансы    | 55000.00   | Чикаго        |
| HR         | 51000.00   | Нью-Йорк      |

В этом примере наш CTE объединяет таблицы сотрудников и отделов, calculates среднюю зарплату и включает информацию о местоположении.

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

Теперь, когда事情 становятся真的很 интересными – рекурсивные CTE! Это как русские матрешки SQL мира. Рекурсивный CTE ссылается на самого себя, позволяя вам работать с иерархическими или древовидными данными.

Давайте создадим простой пример иерархии сотрудников:

CREATE TABLE employee_hierarchy (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employee_hierarchy VALUES
(1, 'Big Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);

Теперь давайте используем рекурсивный CTE, чтобы отобразить всю иерархию:

WITH RECURSIVE emp_hierarchy AS (
SELECT id, name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employee_hierarchy e
JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM emp_hierarchy
ORDER BY level, id;

Этот запрос даст нам:

| id | name       | manager_id | level |
|----|------------|------------|-------|
| 1  | Big Boss   | NULL       | 0     |
| 2  | Manager A  | 1          | 1     |
| 3  | Manager B  | 1          | 1     |
| 4  | Employee 1 | 2          | 2     |
| 5  | Employee 2 | 2          | 2     |
| 6  | Employee 3 | 3          | 2     |

Этот рекурсивный CTE начинается с верхнего уровня сотрудника (Big Boss) и затем рекурсивно находит всех сотрудников, которые报告 к каждому менеджеру.

Преимущества CTE

CTE имеют несколько преимуществ:

Преимущество	Описание
Readability	CTE делают сложные запросы более читаемыми, разбивая их на named подзапросы.
Reusability	Вы можете ссылаться на CTE несколько раз в одном запросе.
Recursion	CTE позволяют писать рекурсивные запросы, которые великолепны для иерархических данных.
Simplification	Они могут упрощать сложные объединения и подзапросы.
Maintenance	CTE делают запросы easier для обслуживания и модификации.

Недостатки CTE

Хотя CTE мощны, у них есть некоторые ограничения:

Недостаток	Описание
Performance	В некоторых случаях CTE могут не работать так же эффективно, как производные таблицы или представления.
Scope	CTE действительны только в scope единственного оператора, в котором они определены.
Complexity	Для очень простых запросов использование CTE может добавить ненужную сложность.
Database Support	Не все системы управления базами данных поддерживают CTE, хотя большинство современных систем это делают.

И вот мы и добрались до конца, друзья! Мы совершили путешествие через страну Общих табличных выражений, от базовых концепций до рекурсивных запросов. Помните, как и при обучении любому новому навыку, овладение CTE требует практики. Так что не бойтесь экспериментировать с ними в своих запросах. Before you know it, вы будете писать CTE как профессионал, впечатляя своих коллег и делая свои запросы к базе данных чище и более эффективными. Счастливо!

Credits: Image by storyset