Ответ
CTE (Common Table Expression, обобщенное табличное выражение) в PostgreSQL — это временный именованный результирующий набор, который существует только во время выполнения одного SQL-запроса. Определяется с помощью конструкции WITH.
Основные цели использования:
- Улучшение читаемости: Разбиение сложных запросов на логические части.
- Организация рекурсивных запросов: Для работы с иерархическими или древовидными структурами данных.
- Многократное использование в запросе: К CTE можно обратиться несколько раз в основном запросе, что может быть эффективнее подзапроса.
Базовый синтаксис:
WITH cte_name (column_list) AS (
-- Определение CTE (подзапрос)
SELECT ...
)
-- Основной запрос, использующий CTE
SELECT * FROM cte_name WHERE ...;Пример 1: Простой CTE для структурирования
WITH high_value_orders AS (
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 1000 AND order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT c.name, COUNT(hvo.order_id) as order_count
FROM high_value_orders hvo
JOIN customers c ON hvo.customer_id = c.id
GROUP BY c.name;Пример 2: Рекурсивный CTE (для иерархии)
WITH RECURSIVE employee_path AS (
-- Якорная часть: начальник
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Рекурсивная часть: подчиненные
SELECT e.id, e.name, e.manager_id, ep.level + 1
FROM employees e
INNER JOIN employee_path ep ON e.manager_id = ep.id
)
SELECT * FROM employee_path ORDER BY level, name;Важно: CTE может быть материализована (вычислена и сохранена в памяти) или нет, что влияет на производительность. В PostgreSQL можно управлять этим с помощью MATERIALIZED / NOT MATERIALIZED (с версии 12).
Ответ 18+ 🔞
Давай разберём эту штуку, которая, как выясняется, не какая-то заумная хуйня, а реально полезная фишка. CTE, или обобщённое табличное выражение, в Постгресе — это типа временной таблички, которая живёт ровно столько, сколько выполняется твой запрос. Создаётся она через волшебное слово WITH.
Зачем это, блядь, нужно?
- Читаемость, ёпта! Вместо одной мега-пиздопроёбины из вложенных SELECT'ов можно сделать аккуратные блоки, как в нормальном коде.
- Рекурсия, мать её. Это когда нужно спуститься по дереву, например, от начальника к подчинённым, и так до самого последнего офисного планктона. Без CTE там голову сломаешь.
- Переиспользование. Назвал кусок запроса — и тыкай на него ссылками сколько влезет, не переписывая одно и то же по десять раз.
Как выглядит, блядь, синтаксис?
WITH наше_крутое_имя (столбцы_тут) AS (
-- А вот тут сам запрос, который формирует данные
SELECT ...
)
-- А теперь основной запрос, который жрёт эту временную таблицу
SELECT * FROM наше_крутое_имя WHERE ...;Пример первый: для красоты и порядка Представь, тебе нужно найти за последний месяц всех клиентов, которые натолкали заказов больше чем на тысячу рублей, и посчитать, сколько раз они это сделали. В лоб — можно, но некрасиво.
WITH дорогие_заказы AS (
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 1000 AND order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT c.name, COUNT(дз.order_id) as сколько_раз_наёбывали
FROM дорогие_заказы дз
JOIN customers c ON дз.customer_id = c.id
GROUP BY c.name;Видишь? Всё разложено по полочкам. Сначала отфильтровали заказы, потом присоединили клиентов и посчитали. Чисто, аккуратно, в рот меня чих-пых!
Пример второй: рекурсия, или "Найди всех, кто под тобой копает" А вот это уже магия. Допустим, у тебя таблица сотрудников, где у каждого есть ссылка на мудя-начальника. Нужно получить всю иерархию сверху донизу.
WITH RECURSIVE путь_сотрудника AS (
-- Якорь: самый главный мудак, у которого нет начальника
SELECT id, name, manager_id, 1 as уровень_в_иерархии
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Рекурсивная часть: цепляем всех подчинённых к каждому найденному начальнику
SELECT e.id, e.name, e.manager_id, пс.уровень_в_иерархии + 1
FROM employees e
INNER JOIN путь_сотрудника пс ON e.manager_id = пс.id
)
SELECT * FROM путь_сотрудника ORDER BY уровень_в_иерархии, name;Работает так: сначала берём босса. Потом находим всех, у кого он в manager_id, — это первый уровень подчинения. Потом находим всех, кто подчиняется уже найденным подчинённым, и так далее, пока не упрёмся в самых молодых, у которых своих подчинённых уже нет. Красота, да? Тургенев бы оценил.
Важный нюанс на посошок:
Постгрес может эту CTE или материализовать (то есть посчитать один раз и запомнить, как ёбаный калькулятор), или нет (подставлять каждый раз заново). С версии 12 этим можно даже ручками порулить через MATERIALIZED / NOT MATERIALIZED. Но это уже тонкости, о которых думают, когда запрос начинает тормозить как последняя мартышлюшка.