Обобщенное табличное выражение.

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

WITH название_cte [(столбец_1 [, столбец_2] ...)] AS (подзапрос)
[, название_cte [(столбец_1 [, столбец_2] ...)] AS (подзапрос)]

Практические примеры:

-- Простое CTE - пользователи с заказами
WITH user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.username, uo.order_count
FROM users u
JOIN user_orders uo ON u.id = uo.user_id;

-- Множественные CTE - статистика по категориям
WITH category_stats AS (
    SELECT category, AVG(price) as avg_price
    FROM products
    GROUP BY category
),
expensive_categories AS (
    SELECT category
    FROM category_stats
    WHERE avg_price > 100
)
SELECT * FROM expensive_categories;

-- Рекурсивное CTE - иерархия категорий
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

-- CTE с обновлением - временная таблица для анализа
WITH temp_analysis AS (
    SELECT user_id, SUM(total_amount) as total_spent
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY user_id
)
UPDATE users u
SET status = 'premium'
WHERE u.id IN (
    SELECT user_id FROM temp_analysis
    WHERE total_spent > 1000
);