Что такое CTE (Common Table Expression) в PostgreSQL и для чего используется?

CTE (Common Table Expression, обобщенное табличное выражение) в PostgreSQL — это временный именованный результирующий набор, который существует только во время выполнения одного SQL-запроса. Определяется с помощью конструкции WITH.

Основные цели использования:

1. Улучшение читаемости: Разбиение сложных запросов на логические части.
2. Организация рекурсивных запросов: Для работы с иерархическими или древовидными структурами данных.
3. Многократное использование в запросе: К 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).