CTE (WITH) vs подзапросы vs временные таблицы — разница?

SELECT c.name, t.total
FROM customers c
JOIN (
    SELECT customer_id, SUM(total) AS total
    FROM orders
    WHERE created_at >= '2025-09-01'
    GROUP BY customer_id
) t ON t.customer_id = c.id
WHERE t.total > (
    SELECT AVG(s.total) FROM (
        SELECT SUM(total) AS total FROM orders WHERE created_at >= '2025-09-01' GROUP BY customer_id
    ) s
);

WITH monthly AS (
    SELECT customer_id, SUM(total) AS total
    FROM orders
    WHERE created_at >= '2025-09-01'
    GROUP BY customer_id
),
avg_total AS (
    SELECT AVG(total) AS value FROM monthly          -- CTE ссылается на предыдущий CTE
)
SELECT c.name, m.total
FROM monthly m
JOIN customers c ON c.id = m.customer_id
CROSS JOIN avg_total a
WHERE m.total > a.value;

CREATE TEMP TABLE monthly AS
SELECT customer_id, SUM(total) AS total
FROM orders WHERE created_at >= '2025-09-01'
GROUP BY customer_id;

CREATE INDEX ON monthly (customer_id);
ANALYZE monthly;                                     -- статистика для оптимизатора

SELECT c.name, m.total FROM monthly m JOIN customers c ON c.id = m.customer_id
WHERE m.total > (SELECT AVG(total) FROM monthly);

UPDATE customers SET segment = 'vip'
WHERE id IN (SELECT customer_id FROM monthly WHERE total > 1000000);

-- Дерево категорий: все потомки категории 5 с уровнем и путём
WITH RECURSIVE tree AS (
    -- якорь: стартовая строка
    SELECT id, parent_id, name, 1 AS depth, name::text AS path
    FROM categories
    WHERE id = 5

    UNION ALL

    -- рекурсивная часть: дети уже найденных
    SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || ' > ' || c.name
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
    WHERE t.depth < 10                              -- защита от бесконечной рекурсии
)
SELECT * FROM tree ORDER BY path;

-- Для каждого клиента — дата последнего заказа (выполняется «для каждой строки» внешнего запроса)
SELECT c.name,
       (SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = c.id) AS last_order_at
FROM customers c;

-- Принудительно вычислить один раз (например, дорогая функция используется несколько раз)
WITH expensive AS MATERIALIZED (
    SELECT id, heavy_calculation(data) AS score FROM items
)
SELECT * FROM expensive WHERE score > 10
UNION ALL
SELECT * FROM expensive WHERE score < -10;

-- Наоборот, разрешить встраивание, даже если CTE используется дважды
WITH cheap AS NOT MATERIALIZED (...)

var descendants = await db.Categories.FromSql($"""
    WITH RECURSIVE tree AS (
        SELECT * FROM categories WHERE id = {rootId}
        UNION ALL
        SELECT c.* FROM categories c JOIN tree t ON c.parent_id = t.id
    )
    SELECT * FROM tree
    """).AsNoTracking().ToListAsync(ct);