PrepBro
← Назад к вопросам

Что такое Common Table Expressions (CTE) в SQL?

2.0 Middle🔥 61 комментариев
#Базы данных и SQL#Основы Java

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Common Table Expressions (CTE) в SQL

Это один из самых полезных и мощных инструментов SQL, который существенно улучшает читаемость и производительность запросов. CTE (также называется WITH clause) — это именованный временный результат запроса, который можно использовать в следующем SELECT, INSERT, UPDATE или DELETE.

Основная концепция

CTE — это запрос, который определяется внутри главного запроса и работает как временная виртуальная таблица.

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

Основное преимущество: читаемость кода и переиспользование одного подзапроса несколько раз.

Простой пример: TOP 3 customers по тратам

-- Без CTE (сложно читать)
SELECT customer_id, total_spent
FROM (
    SELECT 
        customer_id,
        SUM(amount) as total_spent
    FROM orders
    GROUP BY customer_id
) subquery
WHERE total_spent > 1000
ORDER BY total_spent DESC
LIMIT 3;

-- С CTE (намного понятнее!)
WITH customer_totals AS (
    SELECT 
        customer_id,
        SUM(amount) as total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent > 1000
ORDER BY total_spent DESC
LIMIT 3;

Пример в Java приложении

@Repository
public class CustomerRepository {
    @Query("""
        WITH customer_totals AS (
            SELECT 
                c.id,
                c.name,
                SUM(o.amount) as total_spent,
                COUNT(o.id) as order_count
            FROM customers c
            LEFT JOIN orders o ON c.id = o.customer_id
            WHERE o.created_at >= :startDate
            GROUP BY c.id, c.name
        )
        SELECT id, name, total_spent, order_count
        FROM customer_totals
        WHERE total_spent > :minAmount
        ORDER BY total_spent DESC
        LIMIT :limit
    """)
    List<CustomerDTO> getTopCustomers(
        @Param("startDate") LocalDate startDate,
        @Param("minAmount") BigDecimal minAmount,
        @Param("limit") int limit
    );
}

Несколько CTE в одном запросе (Multiple CTEs)

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as monthly_total
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
),
monthly_avg AS (
    SELECT AVG(monthly_total) as average_monthly
    FROM monthly_sales
)
SELECT 
    ms.month,
    ms.monthly_total,
    ma.average_monthly,
    ROUND((ms.monthly_total - ma.average_monthly) / ma.average_monthly * 100, 2) as pct_diff
FROM monthly_sales ms
CROSS JOIN monthly_avg ma
ORDER BY ms.month;

Java пример:

@Query("""
    WITH monthly_sales AS (
        SELECT 
            DATE_TRUNC('month', order_date) as month,
            SUM(amount) as monthly_total
        FROM orders
        GROUP BY DATE_TRUNC('month', order_date)
    ),
    monthly_avg AS (
        SELECT AVG(monthly_total) as average_monthly
        FROM monthly_sales
    )
    SELECT 
        ms.month as period,
        ms.monthly_total as total,
        ma.average_monthly as avgTotal,
        ROUND((ms.monthly_total - ma.average_monthly) / ma.average_monthly * 100, 2) as percentageDiff
    FROM monthly_sales ms
    CROSS JOIN monthly_avg ma
    ORDER BY ms.month
""")
List<SalesAnalysisDTO> getMonthlySalesAnalysis();

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

Рекурсивные CTE — это мощный инструмент для работы с иерархическими структурами: деревья категорий, org структуры, графы.

Пример: Иерархия категорий товаров

-- Таблица категорий (имеет parent_id для иерархии)
-- id | name | parent_id
-- 1  | Electronics | NULL
-- 2  | Laptops | 1
-- 3  | Gaming Laptops | 2
-- 4  | Business Laptops | 2

WITH RECURSIVE category_hierarchy AS (
    -- BASE CASE: начальные категории (без родителя)
    SELECT 
        id,
        name,
        parent_id,
        1 as level,
        name as full_path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- RECURSIVE CASE: присоединение дочерних категорий
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        ch.level + 1,
        ch.full_path || ' > ' || c.name
    FROM categories c
    INNER JOIN category_hierarchy ch ON c.parent_id = ch.id
    WHERE ch.level < 10  -- защита от бесконечной рекурсии
)
SELECT 
    id,
    name,
    level,
    full_path
FROM category_hierarchy
ORDER BY full_path;

-- Результат:
-- 1 | Electronics | 1 | Electronics
-- 2 | Laptops | 2 | Electronics > Laptops
-- 3 | Gaming Laptops | 3 | Electronics > Laptops > Gaming Laptops
-- 4 | Business Laptops | 3 | Electronics > Laptops > Business Laptops

Пример: Построение дерева организации

WITH RECURSIVE org_hierarchy AS (
    -- CEO (начальная точка)
    SELECT 
        id,
        name,
        manager_id,
        1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Подчиненные
    SELECT 
        e.id,
        REPEAT('  ', oh.level) || e.name as name,  -- indent для readable output
        e.manager_id,
        oh.level + 1
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT id, name, level
FROM org_hierarchy
ORDER BY level, name;

Пример: Поиск пути в графе (кто кому друг?)

-- Таблица друзей (граф)
-- user_id | friend_id
-- 1 | 2
-- 2 | 3
-- 3 | 4
-- 5 | 6

WITH RECURSIVE friend_chain AS (
    -- Начало: мой друг
    SELECT 
        user_id,
        friend_id,
        1 as distance,
        ARRAY[user_id, friend_id] as path
    FROM friendships
    WHERE user_id = 1  -- мой ID
    
    UNION ALL
    
    -- Друзья моих друзей (и дальше...)
    SELECT 
        fc.user_id,
        f.friend_id,
        fc.distance + 1,
        fc.path || f.friend_id
    FROM friend_chain fc
    INNER JOIN friendships f ON fc.friend_id = f.user_id
    WHERE fc.distance < 3  -- максимум 3 степени разделения
    AND NOT f.friend_id = ANY(fc.path)  -- избегаем циклов
)
SELECT DISTINCT
    friend_id,
    distance,
    path
FROM friend_chain
WHERE friend_id = 4  -- ищу человека ID 4
ORDER BY distance;

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

1. Читаемость (Readability)

// Сложный вложенный запрос
SELECT customer_id 
FROM (
    SELECT customer_id, SUM(amount) as total
    FROM (
        SELECT customer_id, amount
        FROM orders
        WHERE status = 'completed'
    ) subq1
    GROUP BY customer_id
) subq2
WHERE total > 1000;

// С CTE — супер ясно
WITH completed_orders AS (
    SELECT customer_id, amount
    FROM orders
    WHERE status = 'completed'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) as total
    FROM completed_orders
    GROUP BY customer_id
)
SELECT customer_id
FROM customer_totals
WHERE total > 1000;

2. Переиспользование

-- CTE можно использовать несколько раз
WITH user_activity AS (
    SELECT user_id, COUNT(*) as activity_count
    FROM user_events
    GROUP BY user_id
)
SELECT 
    (SELECT COUNT(*) FROM user_activity WHERE activity_count > 10) as active_users,
    (SELECT COUNT(*) FROM user_activity WHERE activity_count <= 10) as inactive_users,
    (SELECT AVG(activity_count) FROM user_activity) as avg_activity;

3. Производительность

В PostgreSQL и других СУБД, CTE часто материализуется (кэшируется), что избегает повторных вычислений.

4. Рекурсия для иерархий

Рекурсивные CTE идеальны для деревьев и графов, вместо того чтобы писать сложный код на Java.

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

✅ Используй CTE:

  • Для сложных запросов с несколькими подзапросами
  • Для рекурсивных структур (деревья, графы)
  • Когда нужно переиспользовать промежуточный результат
  • Для пошагового построения сложной логики

❌ Не используй CTE:

  • Для простых запросов (одно условие WHERE)
  • Если база не поддерживает CTE (очень старые СУБД)
  • Если CTE заметно медленнее, чем прямой запрос (редко)

CTE в разных СУБД

Поддерживают CTE:
✅ PostgreSQL (+ рекурсивные)
✅ MySQL 8.0+
✅ SQL Server (+ рекурсивные)
✅ Oracle (+ рекурсивные)
✅ SQLite 3.8.3+

Не поддерживают:
❌ SQLite < 3.8.3
❌ Старые версии MySQL (< 8.0)

Вывод

Common Table Expressions (CTE) — это essential инструмент для Java Developer, который пишет SQL запросы. Они делают код:

  • Понятнее (читаемость)
  • Мощнее (рекурсия, множественные CTE)
  • Быстрее (материализация, избегание подзапросов)

Особенно важно освоить рекурсивные CTE для работы с иерархиями — это экономит часы разработки, которые иначе ушли бы на Java код для обхода деревьев.

Что такое Common Table Expressions (CTE) в SQL? | PrepBro