Что такое оператор WITH (CTE) в SQL и каковы его преимущества?

Question

Что такое оператор WITH (CTE) в SQL и каковы его преимущества?

Тип вопроса: Базы данных Вероятность: 10% Категория: Golang Разработчик

Ответ

Оператор WITH, также известный как CTE (Common Table Expression), — это конструкция в SQL, которая позволяет создать временный, именованный набор результатов запроса. Этот временный набор существует только в рамках выполнения одного основного запроса (SELECT, INSERT, UPDATE или DELETE).

Синтаксис:

WITH CteName AS (
    -- Запрос, формирующий временный набор данных
    SELECT column1, column2 FROM some_table WHERE condition
)
-- Основной запрос, использующий CTE
SELECT * FROM CteName WHERE ...;

Ключевые преимущества использования CTE:

Читаемость и структурирование: CTE позволяет разбить сложный, монолитный запрос на несколько логических, легко читаемых блоков. Это сильно упрощает понимание и отладку.
Многократное использование: На один и тот же CTE можно ссылаться несколько раз в основном запросе, избегая дублирования кода.
```
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > (SELECT AVG(total_sales) FROM regional_sales);
```

Рекурсивные запросы: CTE — это стандартный способ выполнения рекурсивных операций в SQL, что незаменимо для работы с иерархическими данными (например, деревья категорий, организационные структуры).

WITH RECURSIVE employee_hierarchy (id, name, manager_id, level) AS (
    -- Начальная часть рекурсии (якорь)
    SELECT id, name, manager_id, 0 FROM employees WHERE manager_id IS NULL
    UNION ALL
    -- Рекурсивная часть
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

Ответ 18+ 🔞

Давай разберём эту штуку, про которую все так умно говорят — WITH, он же CTE. Представь, что ты пишешь запрос, а он такой длинный, что сам себя за хвост кусает, и уже не понятно, где начало, а где конец. Вот тут-то этот самый WITH и выручает, как спасительная соломинка в море SQL-говна.

По сути, это как взять кусок сложной логики и вынести его в отдельную, временную табличку, которой ты даёшь имя. Живёт она ровно столько, сколько выполняется твой основной запрос, а потом — пуф! — исчезает, как твоя зарплата в баре. Но пока запрос идёт, ты можешь к этой таблице обращаться, как к обычной, хоть десять раз.

Как это выглядит, если не умничать:

WITH МояВременнаяТаблица AS (
    -- Тут твой отбор, группировки, всякая хуйня
    SELECT id, имя, сумма FROM продажи WHERE дата > '2024-01-01'
)
-- А тут уже основной запрос, который юзает эту таблицу
SELECT * FROM МояВременнаяТаблица WHERE сумма > 1000;

И зачем это всё, спросишь? А вот зачем, блядь:

Читаемость, ёпта! Вместо одной многоэтажной хуеты, где подзапросы вложены друг в друга, как матрёшки у ебучего программиста, ты получаешь аккуратные блоки. Сначала подготовил данные в CTE — потом на них работаешь. Глаза не вытекают.

Чтобы не повторяться. Иногда нужно к одним и тем же промежуточным данным обратиться несколько раз. Можно, конечно, один и тот же подзапрос скопипастить, но это же пиздец как некрасиво и неэффективно. А с CTE объявил один раз — и юзай, как свою зубную щётку.

WITH РегиональныеПродажи AS (
    SELECT регион, SUM(сумма) AS общая_выручка
    FROM заказы
    GROUP BY регион
)
-- И вот тут мы дважды к одному CTE идём — и всё ок!
SELECT регион, общая_выручка
FROM РегиональныеПродажи
WHERE общая_выручка > (SELECT AVG(общая_выручка) FROM РегиональныеПродажи);

Самое офигенное — рекурсия. Вот тут WITH просто бог. Нужно пройтись по дереву, например, по начальникам и подчинённым? Без проблем, ёперный театр! Пишешь WITH RECURSIVE и описываешь, как шагать.

WITH RECURSIVE ИерархияСотрудников (id, имя, id_начальника, уровень) AS (
    -- Якорь: с кого начинаем (те, у кого нет начальника)
    SELECT id, имя, id_начальника, 0 FROM сотрудники WHERE id_начальника IS NULL
    UNION ALL
    -- Рекурсивный шаг: берём начальников из предыдущего шага и ищем их подчинённых
    SELECT e.id, e.имя, e.id_начальника, eh.уровень + 1
    FROM сотрудники e
    JOIN ИерархияСотрудников eh ON e.id_начальника = eh.id
)
SELECT * FROM ИерархияСотрудников;

Вот и весь сказ. Не такая уж страшная эта ваша CTE, если разобраться. Главное — не бояться и пробовать, а то так и будете свои километровые подзапросы плодить, как мудаки.