CTE в SQL: Объяснение, примеры и использование
CTE (Common Table Expression) в SQL это временная таблица, которая создается и используется только в рамках текущего запроса. Это позволяет упростить составление запросов и повысить читаемость кода. Чаще всего CTE используются для реализации рекурсивных запросов и сложных запросов, которые требуют использования одной и той же таблицы несколько раз со сложными условиями фильтрации и сортировки.
Пример использования CTE для рекурсивного запроса:
WITH RECURSIVE tree(id, name, parent_id, depth) AS (
SELECT id, name, parent_id, 0
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories c
JOIN tree t ON t.id = c.parent_id
)
SELECT id, name, depth
FROM tree
ORDER BY depth, id;
В данном запросе CTE tree используется для построения древовидной структуры категорий. Сначала выбираются все категории, у которых нет родителей (is null) и начинается рекурсия соединения с таблицей categories на основе поля parent_id, где каждая строка соответствует родительской строке верхнего уровня. Поле depth - это количество уровней вложенности, которое увеличивается на 1 на каждой итерации рекурсии. В конце запроса выводятся идентификаторы, названия категорий и уровни вложенности в порядке возрастания глубины идентификаторов.
Пример использования CTE для сложного запроса:
WITH top_users AS (
SELECT user_id, COUNT(*) as n
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 3
),
top_products AS (
SELECT product_id, COUNT(*) as n
FROM orders
WHERE user_id IN (SELECT user_id FROM top_users)
GROUP BY product_id
HAVING COUNT(*) >= 2
)
SELECT p.name
FROM top_products tp
JOIN products p ON tp.product_id = p.id;
В данном запросе CTE top_users используется для выбора пользователей, которые сделали более 3 заказов, а затем CTE top_products используется для выбора продуктов, которые были заказаны как минимум 2 раза этими пользователями. Далее простой JOIN с таблицей продуктов позволяет выбрать названия продуктов, наиболее популярных у верхних пользователей.
Когда CTE стоит перед запросом, он начинает работать сразу же при чтении запроса, а когда он стоит в запросе в середине или в конце, данные подгружаются только когда код доходит до него, что может улучшить производительность запроса в некоторых случаях.