Что такое Common Table Expressions (CTE) и когда их использовать?

Common Table Expressions (CTE): Рекурсивные и Нерекурсивные Запросы

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

Основной Синтаксис

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

Типы CTE

1. Нерекурсивные CTE (Non-Recursive)

Просто временный набор данных:

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date)::DATE as month,
        SUM(amount) as total_sales,
        COUNT(*) as num_orders
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    month,
    total_sales,
    num_orders,
    ROUND(total_sales / num_orders, 2) as avg_order_value
FROM monthly_sales
WHERE total_sales > 10000
ORDER BY month DESC;

2. Несколько CTE в одном запросе

WITH daily_revenue AS (
    SELECT 
        DATE(order_date) as day,
        SUM(amount) as revenue
    FROM orders
    GROUP BY DATE(order_date)
),
daily_costs AS (
    SELECT 
        DATE(expense_date) as day,
        SUM(cost) as expenses
    FROM expenses
    GROUP BY DATE(expense_date)
),
daily_profit AS (
    SELECT 
        COALESCE(r.day, c.day) as day,
        COALESCE(r.revenue, 0) as revenue,
        COALESCE(c.expenses, 0) as expenses,
        COALESCE(r.revenue, 0) - COALESCE(c.expenses, 0) as profit
    FROM daily_revenue r
    FULL OUTER JOIN daily_costs c ON r.day = c.day
)
SELECT 
    day,
    revenue,
    expenses,
    profit,
    ROUND(100.0 * profit / NULLIF(revenue, 0), 2) as profit_margin
FROM daily_profit
WHERE profit IS NOT NULL
ORDER BY day DESC;

3. Рекурсивные CTE (Recursive)

Для иерархических и рекурсивных структур:

-- Категории товаров с иерархией
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT REFERENCES categories(id)
);

INSERT INTO categories VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Laptops', 2),
(4, 'Desktops', 2),
(5, 'Phones', 1),
(6, 'Accessories', 1);

-- Рекурсивный CTE для полной иерархии
WITH RECURSIVE category_hierarchy AS (
    -- Базовая часть: все корневые категории
    SELECT 
        id,
        name,
        parent_id,
        0 as depth,
        name as full_path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Рекурсивная часть: все подкатегории
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        ch.depth + 1,
        ch.full_path || ' > ' || c.name
    FROM categories c
    INNER JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT 
    REPEAT('  ', depth) || name as category_tree,
    full_path,
    depth
FROM category_hierarchy
ORDER BY full_path;

-- Результат:
-- Electronics (Depth 0)
--   Computers (Depth 1)
--     Laptops (Depth 2)
--     Desktops (Depth 2)
--   Phones (Depth 1)
--   Accessories (Depth 1)

4. Рекурсия: Поиск Пути между Узлами

-- Граф дорог между городами
CREATE TABLE roads (
    from_city VARCHAR(50),
    to_city VARCHAR(50),
    distance INT
);

INSERT INTO roads VALUES
('Moscow', 'SPB', 700),
('Moscow', 'Tver', 170),
('Tver', 'SPB', 540),
('SPB', 'Novgorod', 200);

-- Найти все пути из Moscow в SPB
WITH RECURSIVE paths AS (
    -- Начальная точка
    SELECT 
        from_city,
        to_city,
        distance,
        ARRAY[from_city, to_city] as path,
        1 as hop_count
    FROM roads
    WHERE from_city = 'Moscow'
    
    UNION ALL
    
    -- Расширение пути
    SELECT 
        p.from_city,
        r.to_city,
        p.distance + r.distance,
        p.path || r.to_city,
        p.hop_count + 1
    FROM paths p
    INNER JOIN roads r ON p.to_city = r.from_city
    WHERE NOT r.to_city = ANY(p.path)  -- Избегаем циклов
    AND p.hop_count < 5  -- Ограничиваем глубину
)
SELECT 
    path,
    distance,
    hop_count
FROM paths
WHERE to_city = 'SPB'
ORDER BY distance;

Преимущества CTE

✅ Читаемость: Структурированные, понятные запросы ✅ Переиспользование: Ссылка на одно CTE несколько раз ✅ Разделение логики: Разбивает сложный запрос на части ✅ Рекурсия: Поддержка иерархических данных ✅ Модульность: Легче тестировать отдельные части

CTE vs Subqueries

-- Без CTE (вложенные подзапросы) — сложно читать
SELECT 
    user_id,
    (SELECT AVG(amount) FROM orders o2 WHERE o2.user_id = users.id) as avg_order
FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE amount > (
        SELECT AVG(amount) FROM orders
    )
);

-- С CTE — чище и понятнее
WITH high_value_orders AS (
    SELECT user_id, AVG(amount) as avg_amount
    FROM orders
    GROUP BY user_id
    HAVING AVG(amount) > (SELECT AVG(amount) FROM orders)
)
SELECT 
    u.id,
    u.name,
    h.avg_amount
FROM users u
INNER JOIN high_value_orders h ON u.id = h.user_id
ORDER BY h.avg_amount DESC;

Практические Примеры для Data Engineering

Пример 1: Анализ Когорт

WITH user_cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', MIN(order_date))::DATE as cohort_month
    FROM orders
    GROUP BY user_id
),
cohort_analysis AS (
    SELECT 
        u.cohort_month,
        DATE_TRUNC('month', o.order_date)::DATE as order_month,
        COUNT(DISTINCT o.user_id) as users,
        SUM(o.amount) as revenue,
        (DATE_TRUNC('month', o.order_date)::DATE - u.cohort_month) / INTERVAL '1 month' as months_since_cohort
    FROM orders o
    INNER JOIN user_cohorts u ON o.user_id = u.user_id
    GROUP BY u.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT 
    cohort_month,
    months_since_cohort::INT as months,
    users,
    revenue
FROM cohort_analysis
WHERE cohort_month >= '2025-01-01'
ORDER BY cohort_month, months_since_cohort;

Пример 2: Поиск Аномалий

WITH daily_stats AS (
    SELECT 
        DATE(created_at) as day,
        COUNT(*) as transaction_count,
        SUM(amount) as total_amount,
        AVG(amount) as avg_amount
    FROM transactions
    GROUP BY DATE(created_at)
),
stats_with_avg AS (
    SELECT 
        day,
        transaction_count,
        total_amount,
        avg_amount,
        AVG(transaction_count) OVER (ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_transactions_30d,
        STDDEV(transaction_count) OVER (ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as stddev_transactions_30d
    FROM daily_stats
)
SELECT 
    day,
    transaction_count,
    avg_transactions_30d,
    ROUND(
        ABS(transaction_count - avg_transactions_30d) / NULLIF(stddev_transactions_30d, 0), 2
    ) as z_score,
    CASE 
        WHEN ABS(transaction_count - avg_transactions_30d) / NULLIF(stddev_transactions_30d, 0) > 3 THEN 'ANOMALY'
        ELSE 'NORMAL'
    END as status
FROM stats_with_avg
WHERE day >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY day DESC;

Пример 3: Ранжирование и Top-N

WITH ranked_products AS (
    SELECT 
        product_id,
        product_name,
        category,
        SUM(quantity) as total_sold,
        SUM(revenue) as total_revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) as rank_in_category
    FROM sales
    WHERE year = 2026
    GROUP BY product_id, product_name, category
)
SELECT 
    category,
    product_name,
    total_sold,
    total_revenue,
    rank_in_category
FROM ranked_products
WHERE rank_in_category <= 5  -- Top 5 по каждой категории
ORDER BY category, rank_in_category;

Когда Использовать CTE

✅ Иерархические данные — рекурсивные CTE для деревьев ✅ Сложная логика — разбивка на понятные части ✅ Многоступенчатые преобразования — несколько CTE подряд ✅ Когда нужно переиспользовать результаты несколько раз ✅ Аналитика и отчёты — когда нужна гибкость

Когда НЕ Использовать CTE

❌ Простые запросы — один SELECT с несколькими JOINами ❌ Производительность критична — CTE может быть медленнее (зависит от БД) ❌ Когда нужны временные таблицы — используй CREATE TEMP TABLE

Performance Tips

-- Плохо: CTE материализуется множество раз
WITH expensive_cte AS (
    SELECT ... (медленный запрос)
)
SELECT ... FROM expensive_cte
WHERE ...

UNION ALL

SELECT ... FROM expensive_cte
WHERE ...;

-- Хорошо: добавить MATERIALIZED hint (если БД поддерживает)
WITH expensive_cte AS MATERIALIZED (
    SELECT ...
)
SELECT ...
UNION ALL
SELECT ...;

CTE — это мощный инструмент для написания чистого и понятного SQL кода, особенно важен для Data Engineers при работе со сложными аналитическими запросами.