Общие табличные выражения (CTE)

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

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

Определение и назначение

Общее табличное выражение представляет собой именованное временное результирующее множество, существующее в рамках одного SQL-запроса. Общие табличные выражения позволяют разбить сложный запрос на логически связанные части, каждая из которых имеет собственное имя и может использоваться в последующих частях запроса.

Основное назначение общих табличных выражений заключается в повышении читаемости и поддерживаемости сложных SQL-запросов. Они предоставляют возможность выделить промежуточные вычисления, дать им осмысленные имена и использовать эти именованные результаты в основном запросе или в других общих табличных выражениях.

Общие табличные выражения действуют как временные представления, существующие только в течение выполнения одного запроса. После завершения выполнения запроса общее табличное выражение автоматически удаляется, и его результаты становятся недоступными.

---

Синтаксис и структура

Общее табличное выражение определяется с использованием ключевого слова WITH, за которым следует имя выражения, список столбцов в круглых скобках и ключевое слово AS. После этого в круглых скобках указывается полный подзапрос, результат которого будет доступен под заданным именем.

WITH имя_выражения (столбец1, столбец2, столбец3) AS (
    SELECT столбец1, столбец2, столбец3
    FROM таблица
    WHERE условие
)
SELECT * FROM имя_выражения;

Имя общего табличного выражения должно быть уникальным в рамках запроса и не должно совпадать с именами существующих таблиц или представлений в базе данных. Список столбцов в определении является необязательным, если имена столбцов могут быть однозначно определены из подзапроса.

Общее табличное выражение определяется перед основным запросом и доступно только в этом запросе. Оно не может быть использовано в других запросах, даже если они выполняются в рамках одной транзакции.

---

Возможности операций выборки

Общее табличное выражение может содержать любые операции выборки, доступные в SQL. Это включает соединения таблиц, агрегатные функции, оконные функции, подзапросы и другие сложные конструкции.

Пример использования соединений в общем табличном выражении:

WITH заказы_клиентов AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        o.order_id,
        o.order_date,
        o.total_amount
    FROM customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.order_date >= '2025-01-01'
)
SELECT 
    customer_name,
    COUNT(order_id) AS количество_заказов,
    SUM(total_amount) AS общая_сумма
FROM заказы_клиентов
GROUP BY customer_name
ORDER BY общая_сумма DESC;

Пример с использованием агрегатных функций:

WITH продажи_по_регионам AS (
    SELECT 
        region,
        SUM(sales_amount) AS total_sales,
        AVG(sales_amount) AS avg_sales,
        COUNT(*) AS transaction_count
    FROM sales_data
    GROUP BY region
)
SELECT 
    region,
    total_sales,
    avg_sales,
    transaction_count,
    total_sales / transaction_count AS средний_чек
FROM продажи_по_регионам
WHERE total_sales > 100000;

Пример с оконными функциями:

WITH рейтинги_продуктов AS (
    SELECT 
        product_id,
        product_name,
        category,
        sales_amount,
        RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS category_rank,
        ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS global_rank
    FROM products
    WHERE active = 1
)
SELECT 
    product_name,
    category,
    sales_amount,
    category_rank,
    global_rank
FROM рейтинги_продуктов
WHERE category_rank <= 3;

---

Множественные последовательные выражения

Общие табличные выражения поддерживают определение нескольких последовательных или взаимосвязанных выражений через запятую. Каждое последующее выражение может ссылаться на предыдущие выражения, определенные в том же блоке WITH.

WITH базовые_данные AS (
    SELECT 
        employee_id,
        employee_name,
        department_id,
        salary,
        hire_date
    FROM employees
    WHERE status = 'active'
),
данные_отделов AS (
    SELECT 
        d.department_id,
        d.department_name,
        d.manager_id,
        COUNT(b.employee_id) AS employee_count
    FROM departments d
    LEFT JOIN базовые_данные b ON d.department_id = b.department_id
    GROUP BY d.department_id, d.department_name, d.manager_id
),
расчеты_зарплат AS (
    SELECT 
        b.employee_id,
        b.employee_name,
        b.department_id,
        b.salary,
        AVG(b.salary) OVER (PARTITION BY b.department_id) AS avg_dept_salary,
        b.salary - AVG(b.salary) OVER (PARTITION BY b.department_id) AS diff_from_avg
    FROM базовые_данные b
)
SELECT 
    r.employee_name,
    d.department_name,
    r.salary,
    r.avg_dept_salary,
    r.diff_from_avg,
    d.employee_count
FROM расчеты_зарплат r
JOIN данные_отделов d ON r.department_id = d.department_id
ORDER BY r.diff_from_avg DESC;

В этом примере каждое последующее общее табличное выражение использует результаты предыдущих выражений, создавая цепочку зависимостей. Это позволяет построить сложную логику обработки данных, разбив ее на понятные и управляемые части.

---

Рекурсивные общие табличные выражения

Для работы с иерархическими или древовидными данными используется рекурсивный вариант общего табличного выражения с ключевым словом RECURSIVE. Рекурсивное общее табличное выражение состоит из двух частей: начального запроса и рекурсивного запроса, разделенных оператором UNION ALL.

WITH RECURSIVE иерархия_отделов AS (
    -- Начальный запрос: корневые элементы
    SELECT 
        department_id,
        department_name,
        parent_department_id,
        department_name AS full_path,
        1 AS level
    FROM departments
    WHERE parent_department_id IS NULL
    
    UNION ALL
    
    -- Рекурсивный запрос: дочерние элементы
    SELECT 
        d.department_id,
        d.department_name,
        d.parent_department_id,
        CONCAT(h.full_path, ' -> ', d.department_name) AS full_path,
        h.level + 1 AS level
    FROM departments d
    INNER JOIN иерархия_отделов h ON d.parent_department_id = h.department_id
)
SELECT 
    department_id,
    department_name,
    full_path,
    level
FROM иерархия_отделов
ORDER BY full_path;

Рекурсивные общие табличные выражения особенно полезны для работы с организационными структурами, категориями товаров, древовидными меню и другими данными, имеющими иерархическую природу.

Пример работы с древовидной структурой категорий:

WITH RECURSIVE категории_дерево AS (
    SELECT 
        category_id,
        category_name,
        parent_category_id,
        category_name AS path,
        0 AS depth
    FROM categories
    WHERE parent_category_id IS NULL
    
    UNION ALL
    
    SELECT 
        c.category_id,
        c.category_name,
        c.parent_category_id,
        CONCAT(ct.path, ' > ', c.category_name) AS path,
        ct.depth + 1 AS depth
    FROM categories c
    INNER JOIN категории_дерево ct ON c.parent_category_id = ct.category_id
)
SELECT 
    category_id,
    REPEAT('  ', depth) || category_name AS отступ_категория,
    path,
    depth
FROM категории_дерево
ORDER BY path;

---

Поддержка в различных СУБД

Общие табличные выражения поддерживаются в большинстве современных систем управления базами данных. В разных СУБД могут существовать незначительные различия в синтаксисе и функциональности.

PostgreSQL предоставляет полную поддержку общих табличных выражений, включая рекурсивные выражения. PostgreSQL поддерживает определение нескольких общих табличных выражений в одном запросе и позволяет использовать их в различных частях запроса.

MySQL начиная с версии 8.0 предоставляет поддержку общих табличных выражений. В более ранних версиях MySQL эта функциональность отсутствует.

SQL Server поддерживает общие табличные выражения начиная с версии 2005. SQL Server предоставляет дополнительные возможности, такие как использование общих табличных выражений в инструкциях INSERT, UPDATE и DELETE.

Oracle Database поддерживает общие табличные выражения начиная с версии 9i. Oracle предоставляет расширенные возможности для работы с рекурсивными общими табличными выражениями, включая управление циклами и ограничениями рекурсии.

SQLite поддерживает общие табличные выражения начиная с версии 3.8.3. Поддержка рекурсивных общих табличных выражений также доступна в SQLite.

---

Производительность и оптимизация

Производительность общих табличных выражений зависит от конкретной реализации в системе управления базами данных. В некоторых СУБД общие табличные выражения обрабатываются как встроенные представления, а в других они могут материализовываться во временные таблицы.

Оптимизатор запросов в большинстве современных СУБД способен эффективно обрабатывать общие табличные выражения, применяя те же методы оптимизации, что и для обычных подзапросов. Однако в некоторых случаях использование общих табличных выражений может привести к снижению производительности, особенно при работе с большими объемами данных.

Для оптимизации производительности общих табличных выражений рекомендуется использовать индексы на столбцах, используемых в условиях соединения и фильтрации. Также важно избегать избыточных вычислений и выбирать только необходимые столбцы в общих табличных выражениях.

Пример оптимизированного общего табличного выражения с использованием индексов:

WITH активные_пользователи AS (
    SELECT 
        user_id,
        username,
        registration_date,
        last_login_date
    FROM users
    WHERE status = 'active'
    AND last_login_date >= CURRENT_DATE - INTERVAL '30 days'
    ORDER BY last_login_date DESC
    LIMIT 1000
),
покупки_пользователей AS (
    SELECT 
        p.user_id,
        COUNT(*) AS purchase_count,
        SUM(p.amount) AS total_spent,
        MAX(p.purchase_date) AS last_purchase_date
    FROM purchases p
    WHERE p.purchase_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY p.user_id
)
SELECT 
    au.user_id,
    au.username,
    au.registration_date,
    pu.purchase_count,
    pu.total_spent,
    pu.last_purchase_date
FROM активные_пользователи au
LEFT JOIN покупки_пользователей pu ON au.user_id = pu.user_id
ORDER BY pu.total_spent DESC NULLS LAST;

В этом примере используются ограничения LIMIT и временные фильтры для уменьшения объема обрабатываемых данных. Индексы на столбцах status, last_login_date и purchase_date значительно ускоряют выполнение запроса.

---

Материализация и повторное использование

Некоторые системы управления базами данными предоставляют возможность материализации общих табличных выражений, что означает сохранение результатов выражения во временную таблицу для повторного использования. Это может значительно повысить производительность при многократном обращении к одному и тому же общему табличному выражению в рамках запроса.

В системах, поддерживающих материализацию, оптимизатор запросов автоматически решает, следует ли материализовать общее табличное выражение или обрабатывать его как встроенное представление. Решение принимается на основе анализа стоимости выполнения запроса и ожидаемого объема данных.

Пример запроса с потенциальной материализацией:

WITH сложные_вычисления AS (
    SELECT 
        product_id,
        product_name,
        category_id,
        base_price,
        (base_price * 1.2) AS price_with_tax,
        (base_price * 0.85) AS discounted_price,
        CASE 
            WHEN stock_quantity > 100 THEN 'high'
            WHEN stock_quantity > 50 THEN 'medium'
            ELSE 'low'
        END AS stock_level
    FROM products
    WHERE active = 1
    AND discontinued = 0
)
SELECT 
    sc.product_name,
    c.category_name,
    sc.price_with_tax,
    sc.discounted_price,
    sc.stock_level
FROM сложные_вычисления sc
JOIN categories c ON sc.category_id = c.category_id
WHERE sc.stock_level = 'high'
UNION ALL
SELECT 
    sc.product_name,
    c.category_name,
    sc.price_with_tax,
    sc.discounted_price,
    sc.stock_level
FROM сложные_вычисления sc
JOIN categories c ON sc.category_id = c.category_id
WHERE sc.price_with_tax > 1000
ORDER BY product_name;

В этом примере общее табличное выражение используется дважды в основном запросе. Система управления базами данных может выбрать материализацию этого выражения для избежания повторного выполнения сложных вычислений.

---

Взаимосвязанные общие табличные выражения

Взаимосвязанные общие табличные выражения позволяют создавать сложные цепочки зависимостей между различными частями запроса. Каждое выражение может ссылаться на предыдущие выражения, создавая многоуровневую структуру обработки данных.

WITH исходные_данные AS (
    SELECT 
        transaction_id,
        customer_id,
        transaction_date,
        amount,
        transaction_type
    FROM transactions
    WHERE transaction_date >= '2025-01-01'
),
агрегированные_данные AS (
    SELECT 
        customer_id,
        COUNT(*) AS transaction_count,
        SUM(amount) AS total_amount,
        AVG(amount) AS avg_amount,
        MIN(transaction_date) AS first_transaction,
        MAX(transaction_date) AS last_transaction
    FROM исходные_данные
    GROUP BY customer_id
),
сегментация_клиентов AS (
    SELECT 
        ad.customer_id,
        ad.transaction_count,
        ad.total_amount,
        ad.avg_amount,
        ad.first_transaction,
        ad.last_transaction,
        CASE 
            WHEN ad.total_amount > 10000 THEN 'premium'
            WHEN ad.total_amount > 5000 THEN 'gold'
            WHEN ad.total_amount > 1000 THEN 'silver'
            ELSE 'bronze'
        END AS customer_segment
    FROM агрегированные_данные ad
),
статистика_сегментов AS (
    SELECT 
        customer_segment,
        COUNT(*) AS customer_count,
        SUM(total_amount) AS segment_total,
        AVG(total_amount) AS segment_avg,
        MIN(transaction_count) AS min_transactions,
        MAX(transaction_count) AS max_transactions
    FROM сегментация_клиентов
    GROUP BY customer_segment
)
SELECT 
    sc.customer_id,
    c.customer_name,
    c.email,
    sc.transaction_count,
    sc.total_amount,
    sc.avg_amount,
    sc.customer_segment,
    ss.segment_total,
    ss.segment_avg
FROM сегментация_клиентов sc
JOIN customers c ON sc.customer_id = c.customer_id
JOIN статистика_сегментов ss ON sc.customer_segment = ss.customer_segment
ORDER BY sc.total_amount DESC;

Этот пример демонстрирует создание многоуровневой структуры обработки данных, где каждое последующее общее табличное выражение строится на результатах предыдущих выражений. Такой подход позволяет создавать сложные аналитические запросы с четкой структурой и хорошей читаемостью.

---

Рекурсивные выражения с ограничениями

Рекурсивные общие табличные выражения могут включать ограничения для предотвращения бесконечной рекурсии. Ограничения могут быть заданы с помощью условия в рекурсивной части запроса или с использованием специальных ключевых слов, предоставляемых системой управления базами данных.

WITH RECURSIVE дерево_категорий AS (
    SELECT 
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS level
    FROM categories
    WHERE parent_category_id IS NULL
    
    UNION ALL
    
    SELECT 
        c.category_id,
        c.category_name,
        c.parent_category_id,
        CONCAT(dc.full_path, ' > ', c.category_name) AS full_path,
        dc.level + 1 AS level
    FROM categories c
    INNER JOIN дерево_категорий dc ON c.parent_category_id = dc.category_id
    WHERE dc.level < 10
)
SELECT 
    category_id,
    category_name,
    full_path,
    level
FROM дерево_категорий
ORDER BY full_path;

В этом примере ограничение dc.level < 10 предотвращает бесконечную рекурсию, ограничивая глубину обхода дерева категорий десятью уровнями. Это особенно важно при работе с данными, которые могут содержать циклические зависимости или очень глубокие иерархии.

Некоторые системы управления базами данных предоставляют дополнительные механизмы для управления рекурсией, такие как ключевые слова SEARCH и CYCLE, которые позволяют явно указывать порядок обхода и обнаруживать циклы в данных.

---

Когда применять общие табличные выражения

Общие табличные выражения представляют собой инструмент для организации запросов. Эффективное использование этого инструмента требует понимания его назначения и ограничений.

Целесообразность применения

Общие табличные выражения оправданы в следующих случаях:

• Запрос содержит несколько логических этапов обработки данных
• Необходимо повторное использование промежуточного результата в разных частях запроса
• Требуется повышение читаемости сложного запроса через именование этапов
• Реализуется рекурсивный обход иерархических структур
• Выполняется многоуровневая агрегация или трансформация данных

---

Примеры излишнего использования

Простые запросы без сложной логики не требуют применения общих табличных выражений.

-- Излишнее использование CTE
WITH отфильтрованные_счета AS (
    SELECT *
    FROM счета
    WHERE статус = 'активный'
)
SELECT *
FROM отфильтрованные_счета;

-- Прямой запрос
SELECT *
FROM счета
WHERE статус = 'активный';

-- Излишнее использование CTE
WITH данные_пользователей AS (
    SELECT user_id, имя, фамилия
    FROM пользователи
    WHERE дата_регистрации > '2025-01-01'
)
SELECT *
FROM данные_пользователей
ORDER BY дата_регистрации;

-- Прямой запрос
SELECT user_id, имя, фамилия
FROM пользователи
WHERE дата_регистрации > '2025-01-01'
ORDER BY дата_регистрации;

В этих примерах общие табличные выражения добавляют вертикальный объем кода без улучшения читаемости или функциональности.

---

Примеры оправданного использования

Сложные запросы с многоэтапной обработкой выигрывают от применения общих табличных выражений.

-- Многоэтапная обработка данных
WITH исходные_транзакции AS (
    SELECT 
        transaction_id,
        account_id,
        amount,
        transaction_date,
        transaction_type
    FROM транзакции
    WHERE transaction_date >= CURRENT_DATE - INTERVAL '90 days'
    AND статус = 'завершена'
),
агрегация_по_счетам AS (
    SELECT 
        account_id,
        COUNT(*) AS transaction_count,
        SUM(amount) AS total_volume,
        AVG(amount) AS average_amount,
        MAX(transaction_date) AS last_activity
    FROM исходные_транзакции
    GROUP BY account_id
),
категоризация_активности AS (
    SELECT 
        account_id,
        transaction_count,
        total_volume,
        average_amount,
        last_activity,
        CASE 
            WHEN transaction_count > 50 THEN 'высокая'
            WHEN transaction_count > 20 THEN 'средняя'
            ELSE 'низкая'
        END AS activity_level
    FROM агрегация_по_счетам
)
SELECT 
    c.account_id,
    a.account_name,
    c.transaction_count,
    c.total_volume,
    c.average_amount,
    c.activity_level,
    c.last_activity
FROM категоризация_активности c
JOIN счета a ON c.account_id = a.account_id
WHERE c.activity_level = 'высокая'
ORDER BY c.total_volume DESC;

Этот запрос разбивает сложную логику на последовательные этапы с осмысленными именами, что упрощает понимание и сопровождение.

---

Рекурсивные структуры

Рекурсивные общие табличные выражения незаменимы при работе с иерархическими данными.

WITH RECURSIVE структура_подразделений AS (
    -- Корневые подразделения
    SELECT 
        department_id,
        department_name,
        parent_department_id,
        department_name AS полный_путь,
        1 AS уровень
    FROM подразделения
    WHERE parent_department_id IS NULL
    
    UNION ALL
    
    -- Дочерние подразделения
    SELECT 
        d.department_id,
        d.department_name,
        d.parent_department_id,
        CONCAT(s.полный_путь, ' / ', d.department_name) AS полный_путь,
        s.уровень + 1 AS уровень
    FROM подразделения d
    INNER JOIN структура_подразделений s 
        ON d.parent_department_id = s.department_id
)
SELECT 
    department_id,
    department_name,
    полный_путь,
    уровень
FROM структура_подразделений
ORDER BY полный_путь;

Рекурсивные общие табличные выражения предоставляют единственный стандартный способ обхода древовидных структур в SQL.

---

Повторное использование промежуточных результатов

Когда один и тот же промежуточный результат используется несколько раз в запросе, общее табличное выражение устраняет дублирование кода.

WITH активные_клиенты AS (
    SELECT 
        client_id,
        client_name,
        registration_date,
        last_order_date,
        total_orders
    FROM клиенты
    WHERE статус = 'активный'
    AND last_order_date >= CURRENT_DATE - INTERVAL '180 days'
)
SELECT 
    'премиум' AS сегмент,
    COUNT(*) AS количество,
    SUM(total_orders) AS общее_количество_заказов
FROM активные_клиенты
WHERE total_orders > 20

UNION ALL

SELECT 
    'стандарт' AS сегмент,
    COUNT(*) AS количество,
    SUM(total_orders) AS общее_количество_заказов
FROM активные_клиенты
WHERE total_orders BETWEEN 5 AND 20

UNION ALL

SELECT 
    'начинающие' AS сегмент,
    COUNT(*) AS количество,
    SUM(total_orders) AS общее_количество_заказов
FROM активные_клиенты
WHERE total_orders < 5;

В этом примере общее табличное выражение используется трижды, что предотвращает повторение сложного фильтра.

---

Рекомендации по выбору подхода

При разработке SQL-запросов следует руководствоваться следующими принципами:

• Простые запросы без сложной логики реализуются напрямую
• Сложные многоэтапные запросы разбиваются на общие табличные выражения
• Каждое общее табличное выражение должно иметь осмысленное имя
• Общие табличные выражения применяются для повышения читаемости, а не усложнения
• Рекурсивные структуры требуют рекурсивных общих табличных выражений
• Повторное использование промежуточных результатов оправдывает применение общих табличных выражений

---