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

Поэтому в этой статье я расскажу:

1. что такое CTE

2. зачем оно нужно 

3. что такое рекурсивные СТЕ

4. чем СТЕ отличается от временных таблиц, представлений и подзапросов

5. как СТЕ может плохо сказаться на производительности 

6. как использовать СTE в самом народном фреймворке Django

Использует SELECT со звёздочкой Макс - Lead Backend и автор YouTube-канала PyLounge. Поехали!

Что такое CTE

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

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

Для написания обобщенного табличного выражения используется оператор WITH:

WITH название_сте (список получившихся толбцов, необязательно) AS (
    SQL_ВЫРАЖЕНИЕ
)

Пример

Дана таблица Employees

CTE:

WITH AvgSalaryByPosition AS (
    SELECT Position, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY Position
)
SELECT *
FROM AvgSalaryByPosition;

Результат:

Ещё СТЕ:

WITH AvgSalaryByPosition AS (
    SELECT Position, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY Position
)
SELECT *
FROM AvgSalaryByPosition
WHERE Position = 'Backend';

Ещё результат:

Зачем нужны СТЕ

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

Кроме того, повторное использование уже полученного (материализованного) результатов CTE может быть эффективнее, чем несколько раз выполнить один и тот же подзапрос. База данных один раз вычисляет результат CTE и затем использует его повторно, избегая избыточных вычислений. То есть определяете CTE один раз, а затем многократно ссылаетесь на него в основном запросе.

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

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

Пример 1: Улучшение читаемости

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

Без CTE:

SELECT customer_id, total_amount
FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS subquery
WHERE total_amount > 1000;

С использованием CTE:

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM customer_totals
WHERE total_amount > 1000;

Второй вариант более читаем, так как логика разбита на два понятных шага.

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

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

Без CTE:

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (
    SELECT AVG(total_amount)
    FROM (
        SELECT SUM(amount) AS total_amount
        FROM orders
        GROUP BY customer_id
    ) AS subquery
);

С использованием CTE:

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM customer_totals
WHERE total_amount > (SELECT AVG(total_amount) FROM customer_totals);

CTE позволяет избежать дублирования кода и делает запрос более понятным.

Рекурсивные СТЕ

Рекурсивные CTE позволяют выполнять рекурсивные запросы. Рекурсивные CTE состоят из двух частей: анкерной части и рекурсивной части.

Анкерная часть - это начальный запрос, который возвращает базовый набор данных.

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

Общий синтаксис рекурсивного CTE выглядит так:

WITH RECURSIVE cte_name AS (
    -- Анкерная часть
    SELECT ...
    FROM ...
    WHERE ...

    UNION ALL

    -- Рекурсивная часть
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT * FROM cte_name;

Пример 1: Генерация последовательности чисел

Допустим, мы хотим сгенерировать последовательность чисел от 1 до 10.

WITH RECURSIVE numbers AS (
    -- Анкерная часть: начинаем с 1
    SELECT 1 AS n

    UNION ALL

    -- Рекурсивная часть: добавляем 1 к предыдущему значению
    SELECT n + 1
    FROM numbers
    WHERE n < 10
)
SELECT * FROM numbers;

Результат:

n
---
1
2
3
4
5
6
7
8
9
10

Пример 2: Иерархия сотрудников

Предположим, у нас есть таблица сотрудников, где каждый сотрудник ссылается на своего менеджера. Мы хотим построить иерархию для конкретного сотрудника.

Таблица employees

Запрос для получения всей иерархии подчиненных для сотрудника с id = 1:

WITH RECURSIVE employee_hierarchy AS (
    -- Анкерная часть: начинаем с сотрудника с id = 1
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 1

    UNION ALL

    -- Рекурсивная часть: добавляем подчиненных
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

Результат:

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

Пример 3: Поиск пути в графе

Предположим, у нас есть таблица, представляющая граф, где каждая строка — это ребро между двумя узлами.

Таблица graph:

Мы хотим найти все пути от узла 1 до узла 4.

WITH RECURSIVE path AS (
    -- Анкерная часть: начинаем с узла 1
    SELECT from_node, to_node, CAST(from_node AS TEXT) || '->' || CAST(to_node AS TEXT) AS path
    FROM graph
    WHERE from_node = 1

    UNION ALL

    -- Рекурсивная часть: добавляем следующий узел в путь
    SELECT g.from_node, g.to_node, p.path || '->' || CAST(g.to_node AS TEXT)
    FROM graph g
    INNER JOIN path p ON g.from_node = p.to_node
    WHERE p.to_node != 4
)
SELECT path FROM path WHERE to_node = 4;

Результат:

path
-----
1->2->3->4

На что обратить внимание:

• Условие остановки: Рекурсивная часть должна иметь условие, которое остановит рекурсию. В противном случае запрос будет выполняться бесконечно.

• Производительность: Рекурсивные CTE могут быть ресурсоемкими, особенно на больших объемах данных. Важно оптимизировать запросы и использовать индексы.

• Ограничения: Некоторые СУБД могут иметь ограничения на глубину рекурсии. Например, в PostgreSQL можно настроить параметр max_recursion для увеличения глубины рекурсии.

Материализованные СТЕ

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

В PostgreSQL материализация CTE может быть выполнена с использованием ключевого слова MATERIALIZED (начиная с версии 12):

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

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

Преимущества материализованных CTE

• Оптимизация производительности: Если CTE используется несколько раз в запросе, материализация позволяет избежать повторного выполнения вычислений.

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

• Снижение нагрузки на СУБД: Повторное использование сохраненного результата снижает нагрузку на процессор и память.

Недостатки материализованных CTE

• Дополнительные затраты на хранение: Материализация требует выделения памяти для хранения временной таблицы.

• Не всегда полезно: Если CTE используется только один раз или если данные в CTE часто изменяются, материализация может не дать преимуществ и даже замедлить выполнение запроса.

Пример использования материализованных CTE

Предположим, у нас есть таблица sales, и мы хотим вычислить общий объем продаж по каждому региону, а затем использовать этот результат для дальнейших расчетов:

WITH regional_sales AS MATERIALIZED (
    SELECT region, SUM(amount) AS total_sales
    FROM sales
    GROUP BY region
)
SELECT region, total_sales, (total_sales / SUM(total_sales) OVER ()) * 100 AS sales_percentage
FROM regional_sales;

В этом примере:

• CTE regional_sales материализуется, и его результат сохраняется.

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

Когда использовать материализованные CTE?

Материализованные CTE стоит использовать в следующих случаях:

• CTE используется несколько раз в запросе.

• CTE содержит сложные вычисления или агрегации.

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

• PostgreSQL: Поддерживает материализацию CTE с помощью ключевого слова MATERIALIZED (начиная с версии 12).

• Oracle: Поддерживает материализацию через WITH ... AS MATERIALIZED.

• MySQL: Не поддерживает материализацию CTE.

В PostgreSQL поведение CTE по умолчанию не является материализованным. Начиная с версии PostgreSQL 12, поведение CTE можно контролировать с помощью ключевого слова MATERIALIZED или NOT MATERIALIZED.

Поведение CTE в PostgreSQL

По умолчанию (без указания MATERIALIZED/NOT MATERIALIZED):

• В PostgreSQL 12 и выше CTE по умолчанию не материализуются. Это означает, что оптимизатор PostgreSQL может решить, стоит ли материализовать CTE, исходя из плана выполнения запроса. Обычно CTE ведет себя как подзапрос и может быть "встроен" в основной запрос для оптимизации.

• В PostgreSQL CTE материализуется по умолчанию, если к ней обращаются более одного раза.

• В версиях до PostgreSQL 12 CTE по умолчанию всегда материализовались, то есть результат CTE сохранялся во временной таблице.

Явное указание MATERIALIZED

Если вы хотите гарантировать, что CTE будет материализован, вы можете использовать ключевое слово MATERIALIZED:

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

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

Явное указание NOT MATERIALIZED

Если вы хотите явно запретить материализацию CTE, вы можете использовать ключевое слово NOT MATERIALIZED:

WITH cte_name AS NOT MATERIALIZED (
    SELECT column1, column2
    FROM some_table
    WHERE condition
)
SELECT *
FROM cte_name;

В этом случае CTE будет вести себя как подзапрос и может быть встроен в основной запрос.

Пример

Предположим, у нас есть таблица sales, которая содержит данные о продажах:

sales: (sale_id, region, sale_date, amount).

Мы хотим:

• Вычислить общую сумму продаж по каждому региону.

• Найти регионы, где общая сумма продаж превышает среднюю сумму продаж по всем регионам.

• Вывести детали по этим регионам, включая количество продаж и среднюю сумму продаж на одну транзакцию.

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

-- Материализованный CTE для вычисления общей суммы продаж по регионам
WITH regional_sales AS MATERIALIZED (
    SELECT
        region,
        SUM(amount) AS total_sales,
        COUNT(sale_id) AS total_transactions
    FROM
        sales
    GROUP BY
        region
)
-- Основной запрос
SELECT
    rs.region,
    rs.total_sales,
    rs.total_transactions,
    rs.total_sales / rs.total_transactions AS avg_sale_per_transaction,
    (SELECT AVG(total_sales) FROM regional_sales) AS avg_sales_all_regions
FROM
    regional_sales rs
WHERE
    rs.total_sales > (SELECT AVG(total_sales) FROM regional_sales)
ORDER BY
    rs.total_sales DESC;

Материализованный CTE regional_sales - вычисляет общую сумму продаж (total_sales) и количество транзакций (total_transactions) для каждого региона. Этот CTE материализуется, то есть его результат сохраняется во временной таблице.

Основной запрос использует CTE regional_sales дважды:

• Для фильтрации регионов, где total_sales превышает среднюю сумму продаж по всем регионам.

• Для вычисления средней суммы продаж по всем регионам (avg_sales_all_regions).

• Также вычисляет среднюю сумму продаж на одну транзакцию (avg_sale_per_transaction) для каждого региона.

Преимущества использования материализованного CTE

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

• Удобство: CTE используется несколько раз в запросе, что делает код более читаемым и поддерживаемым.

Чтобы заставить отказаться от материализации, надо явно приказать оптимизатору: NOT MATERIALIZED.

Использование нескольких СТЕ

Предположим, у нас есть две таблицы: employees (сотрудники) и departments (отделы). Мы хотим получить список сотрудников с их зарплатами и названиями отделов, а также вычислить среднюю зарплату по каждому отделу. Здесь будем использовать определение сразу двух СТЕ.

WITH 
    -- Первый CTE: выбираем сотрудников с их зарплатами и отделами
    EmployeeInfo AS (
        SELECT 
            e.employee_id,
            e.first_name,
            e.last_name,
            e.salary,
            e.department_id
        FROM 
            employees e
    ),
    
    -- Второй CTE: вычисляем среднюю зарплату по каждому отделу
    DepartmentAvgSalary AS (
        SELECT 
            d.department_id,
            d.department_name,
            AVG(e.salary) AS avg_salary
        FROM 
            employees e
        JOIN 
            departments d ON e.department_id = d.department_id
        GROUP BY 
            d.department_id, d.department_name
    )

-- Основной запрос: объединяем данные из обоих CTE
SELECT 
    ei.employee_id,
    ei.first_name,
    ei.last_name,
    ei.salary,
    das.department_name,
    das.avg_salary
FROM 
    EmployeeInfo ei
JOIN 
    DepartmentAvgSalary das ON ei.department_id = das.department_id
ORDER BY 
    ei.employee_id;

Чем СТЕ отличается от временных таблиц, представлений и подзапросов

CTE, временные таблицы (temporary table), представления (VIEW) и подзапросы (subquery) - это различные инструменты в SQL, которые используются для организации и выполнения запросов. У каждого из них есть свои особенности, преимущества и ограничения. Давайте разберем, чем они отличаются друг от друга:

1. CTE 

Особенности:

• CTE - это временные результаты запросов, которые существуют только во время выполнения основного запроса.

• Они определяются с помощью ключевого слова WITH и могут быть рекурсивными.

• CTE не сохраняются в базе данных и не требуют дополнительных прав доступа.

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

• Улучшают читаемость и структурированность кода.

• Позволяют разбивать сложные запросы на логические блоки.

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

Недостатки

• Время жизни ограничено выполнением запроса.

• В некоторых случаях могут быть менее производительными.

Пример:

WITH sales_cte AS (
    SELECT product_id, SUM(quantity) AS total_quantity
    FROM sales
    GROUP BY product_id
)
SELECT * FROM sales_cte WHERE total_quantity > 100;

2. Временные таблицы 

Особенности:

• Временные таблицы создаются на время сессии или транзакции и автоматически удаляются после завершения.

• Они хранятся в базе данных, но только временно.

• Для создания временных таблиц используется синтаксис CREATE TEMPORARY TABLE.

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

• Могут быть использованы многократно в рамках одной сессии.

• Поддерживают индексы, что может улучшить производительность для сложных запросов.

• Удобны для хранения промежуточных результатов.

Недостатки:

• Требуют дополнительных ресурсов для создания и управления.

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

Пример:

CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

SELECT * FROM temp_sales WHERE total_quantity > 100;

3. Представления (VIEW)

Особенности:

• VIEW - это виртуальные таблицы, которые сохраняются в базе данных и представляют собой результат SQL-запроса.

• Они не хранят данные, а только определяют способ их извлечения.

• VIEW можно использовать как обычные таблицы в запросах.

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

• Упрощают сложные запросы, предоставляя готовые "виртуальные таблицы".

• Могут быть использованы многократно разными пользователями.

• Обеспечивают уровень абстракции и безопасности (можно ограничить доступ к данным через VIEW).

Недостатки:

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

• Не подходят для временных или одноразовых задач.

Пример:

CREATE VIEW sales_view AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

SELECT * FROM sales_view WHERE total_quantity > 100;

4. Подзапросы 

Особенности:

• Подзапросы - это запросы, вложенные в другие запросы (например, в SELECT, FROM, WHERE).

• Они выполняются "на лету" и не сохраняются в базе данных.

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

• Просты в использовании для одноразовых задач. Чем-то напоминают лямбда-функции в языка программирования.

• Не требуют создания дополнительных объектов в базе данных.

Недостатки:

• Могут ухудшать читаемость кода, особенно если вложенность большая.

• Не поддерживают рекурсию.

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

Пример:

SELECT product_id, total_quantity
FROM (
    SELECT product_id, SUM(quantity) AS total_quantity
    FROM sales
    GROUP BY product_id
) AS subquery
WHERE total_quantity > 100;

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

CTE и временные таблицы - это два разных инструмента, предназначенных для решения разных задач. Хотя оба позволяют временно хранить данные, их применение и характеристики существенно отличаются.

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

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

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

Преимущества CTE перед другими подходами

По сравнению с вложенными подзапросами:

• CTE более читаемы и легче поддерживаются.

• Вложенные подзапросы могут быть сложными для понимания, особенно если их много.

По сравнению с временными таблицами:

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

• CTE существуют только во время выполнения запроса.

По сравнению с представлениями (VIEW):

• CTE не сохраняются в базе данных, поэтому они не занимают место и не требуют дополнительных прав доступа.

• Они идеально подходят для одноразовых запросов.

Поэтому лучший подход - пробовать разное и сравнивать. Большинство СУБД предоставляют инструменты для анализа планов выполнения запросов - EXPLAINE. EXPLAINE ANALYZE. Можно увидеть, как оптимизатор обрабатывает каждый вариант. Это позволит вам выбрать наиболее эффективный подход для конкретной ситуации.

Почему СТЕ могут вызвать проблемы с производительностью

CTE в PostgreSQL могут вызывать проблемы с производительностью по нескольким причинам:

1. Материализация CTE

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

• Избыточному использованию памяти, если результат CTE большой.

• Неоптимальному выполнению, если оптимизатор не может эффективно использовать индексы или объединять данные.

Начиная с PostgreSQL 12, появилась возможность отключить материализацию с помощью ключевого слова NOT MATERIALIZED, но это требует явного указания.

2. Отсутствие оптимизации через CTE

Оптимизатор PostgreSQL не всегда может "протащить" условия фильтрации или объединения через CTE. Это может привести к тому, что CTE будет обрабатывать больше данных, чем необходимо, что снизит производительность.

Например:

WITH cte AS (
    SELECT * FROM large_table
)
SELECT * FROM cte WHERE column = 'value';

В этом случае CTE сначала обработает всю таблицу large_table, а только потом применит фильтр column = 'value', что может быть неэффективно.

3. Ограничения оптимизатора

Оптимизатор PostgreSQL не всегда может переписать запрос с CTE в более эффективную форму. Например, он может не использовать индексы или не выполнять объединения (joins) оптимальным образом, если данные находятся внутри CTE.

4. Многократное использование CTE

Если CTE используется несколько раз в запросе, то в версиях PostgreSQL до 12 она будет материализована и вычислена только один раз. Это может быть как плюсом, так и минусом:

Плюс: если CTE вычисляется долго, то повторное использование сэкономит время.

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

5. Временные таблицы и дисковые операции

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

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

Оптимизатор PostgreSQL может не всегда корректно оценивать стоимость выполнения CTE, что приводит к неоптимальным планам запросов. Например, он может выбрать полное сканирование таблицы вместо использования индекса.

Как избежать проблем с производительностью?

• Когда нужно использовать NOT MATERIALIZED (если доступно):

  WITH cte AS NOT MATERIALIZED (
      SELECT * FROM large_table
  )
  SELECT * FROM cte WHERE column = 'value'
  

• Переписать запрос без CTE: Иногда запрос можно переписать без использования CTE, что позволит оптимизатору лучше работать с данными.

• Анализировать план запроса: Используйте EXPLAIN ANALYZE, чтобы понять, как выполняется запрос, и найдите узкие места.

  EXPLAIN WITH sales_summary AS (
      SELECT salesperson_id, SUM(amount) AS total_sales
      FROM sales
      GROUP BY salesperson_id
  )
  SELECT * FROM sales_summary;

• Разделять сложные запросы: Если CTE слишком сложный, разбейте его на несколько запросов или используйте временные таблицы.

• Аккуратно использовать рекурсивных CTE

• Если используем рекурсивные CTE, убедиться, что условие завершения правильно написано и что запрос работает с минимальным количеством строк.

• Оптимизируйте CTE Убедитесь, что запросы внутри CTE оптимизированы. Таблицы используют индексы, есть фильтры и ограничения, чтобы уменьшить объем данных, обрабатываемых в CTE.

Django CTE

Django ORM не поддерживает CTE напрямую (т.к. не все СУБД поддерживают CTE), но их можно реализовать с помощью RawSQL или сторонних библиотек (например, django-cte).

1. Использование CTE через RawSQL Самый простой способ — написать CTE вручную с использованием RawSQL.

Пример: CTE для агрегации данных

from django.db import models
from django.db.models import RawSQL

class Order(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    date = models.DateField()

# Создаем CTE для подсчета суммы заказов по пользователям
cte_query = """
WITH user_total AS (
    SELECT 
        user_id, 
        SUM(amount) AS total 
    FROM 
        app_order 
    GROUP BY 
        user_id
)
SELECT * FROM user_total WHERE total > 1000;
"""

# Используем RawSQL в queryset
orders = Order.objects.annotate(
    total=RawSQL("SELECT total FROM user_total WHERE user_id = app_order.user_id", ())
).raw(cte_query)

2. Использование библиотеки django-cte 

Библиотека django-cte предоставляет интеграцию CTE с Django ORM, позволяя строить сложные запросы с использованием синтаксиса, похожего на стандартный ORM.

Установка:

pip install django-cte

Пример 1: Простой CTE

from django_cte import CTEManager, With

class Product(models.Model):
    name = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    category = models.CharField(max_length=50)
    objects = CTEManager()

# Создаем CTE для товаров дороже 1000
cte = With(
    Product.objects
    .filter(price__gt=1000)
    .values("category")
    .annotate(total_price=models.Sum("price"))
)

# Основной запрос, использующий CTE
products = (
    cte.join(Product, category=cte.col.category)
    .with_cte(cte)
    .annotate(
        category_total=cte.col.total_price,
    )
    .values("name", "price", "category", "category_total")
)

print(products.query)  # вывести SQL с CTE
SQL:

WITH RECURSIVE "cte" AS (
    SELECT
        "product"."category",
        SUM("product"."price") AS "total_price"
    FROM
        "product"
    WHERE
        "product"."price" > 1000
    GROUP BY
        "product"."category"
)
SELECT
    "product"."name",
    "product"."price",
    "product"."category",
    "cte"."total_price" AS "category_total"
FROM
    "product"
INNER JOIN
    "cte"
ON
    "product"."category" = "cte"."category"
Ещё один пример:

from django_cte import With

cte = With(
    Order.objects
    .values("region_id")
    .annotate(total=Sum("amount"))
)

orders = (
    cte.join(Order, region=cte.col.region_id)
    .with_cte(cte)
    .annotate(region_total=cte.col.total)
)

print(orders.query)
SQL:

WITH RECURSIVE "cte" AS (
    SELECT
        "orders"."region_id",
        SUM("orders"."amount") AS "total"
    FROM "orders"
    GROUP BY "orders"."region_id"
)
SELECT
    "orders"."id",
    "orders"."region_id",
    "orders"."amount",
    "cte"."total" AS "region_total"
FROM "orders"
INNER JOIN "cte" ON "orders"."region_id" = "cte"."region_id"

Вы могли заметить, что в CTE для запроса использует WITH RECURSIVE, хотя это не рекурсивное СТЕ. В этой библиотеке ключевое слово RECURSIVE используется всегда, даже для нерекурсивных CTE. В таких базах данных, как PostgreSQL и SQLite, это не имеет никакого эффекта, если запрос в действительности не является рекурсивным.

Пример 2: Рекурсивный CTE (для иерархических данных)

from django_cte import CTEManager
from django.db import models

class Category(models.Model):
    name = models.CharField(max_length=100)
    parent = models.ForeignKey("self", null=True, on_delete=models.CASCADE)
    objects = CTEManager()

# Рекурсивный CTE для получения всех подкатегорий
cte = With.recursive(
    Category.objects.filter(parent__isnull=True).values("id", "name", "parent_id"),  # База рекурсии
    name="root_category",
    parents=[],
    __union=Category.objects.filter(
        parent=With.outer("id")  # Рекурсивный шаг
    ).values("id", "name", "parent_id"),
)

# Запрос для вывода дерева категорий
categories = (
    cte.join(Category, id=cte.col.id)
    .with_cte(cte)
    .annotate(
        root_name=cte.col.name,
        path=models.Func(
            cte.col.parents,  # Путь через родителей
            function="array_to_string",
            template="%(function)s(%(expressions)s, ' -> ')",
        )
    )
    .values("name", "root_name", "path")
)
SQL:

WITH RECURSIVE "cte" AS (
    -- База рекурсии: корневые категории (без родителя)
    SELECT
        "category"."id",
        "category"."name",
        "category"."parent_id",
        "category"."name" AS "root_category",
        ARRAY[]::TEXT[] AS "parents"
    FROM
        "category"
    WHERE
        "category"."parent_id" IS NULL

    UNION ALL

    -- Рекурсивный шаг: подкатегории
    SELECT
        "category"."id",
        "category"."name",
        "category"."parent_id",
        "cte"."root_category",
        ARRAY_APPEND("cte"."parents", "cte"."name") AS "parents"
    FROM
        "category"
    INNER JOIN
        "cte"
    ON
        "category"."parent_id" = "cte"."id"
)
SELECT
    "category"."name",
    "cte"."root_category" AS "root_name",
    ARRAY_TO_STRING("cte"."parents", ' -> ') AS "path"
FROM
    "category"
INNER JOIN
    "cte"
ON
    "category"."id" = "cte"."id"

3. Использование нескольких CTE

Можно объединять несколько CTE в одном запросе.

Пример: Задача: Найти сотрудников, чья зарплата выше средней по отделу и больше 5000.

from django.db.models import Q
from django_cte import CTEManager, With

# Первый CTE: средняя зарплата по отделам
avg_salary = (
    Employee.objects
    .values('department')
    .annotate(avg_salary=Avg('salary'))
    .values('department', 'avg_salary')
)

# Второй CTE: сотрудники с зарплатой > 5000
high_salary = (
    Employee.objects
    .filter(salary__gt=5000)
    .values('id', 'name', 'department', 'salary')
)

cte_query = With(avg_salary, high_salary)

result = (
    Employee.objects
    .with_cte(cte_query)
    .annotate(
        avg_salary=cte_query.col.avg_salary,
    )
    .filter(
        Q(salary__gt=F('avg_salary')) &
        Q(id__in=high_salary.values('id'))
    )
    .values('name', 'department', 'salary', 'avg_salary')
)
SQL:

WITH "avg_salary" AS (
    SELECT
        "employee"."department",
        AVG("employee"."salary") AS "avg_salary"
    FROM
        "employee"
    GROUP BY
        "employee"."department"
),
"high_salary" AS (
    SELECT
        "employee"."id",
        "employee"."name",
        "employee"."department",
        "employee"."salary"
    FROM
        "employee"
    WHERE
        "employee"."salary" > 5000
)
SELECT
    "employee"."name",
    "employee"."department",
    "employee"."salary",
    "avg_salary"."avg_salary"
FROM
    "employee"
INNER JOIN
    "avg_salary"
ON
    "employee"."department" = "avg_salary"."department"
WHERE
    "employee"."salary" > "avg_salary"."avg_salary"
    AND "employee"."id" IN (
        SELECT
            "high_salary"."id"
        FROM
            "high_salary"
    )

4. Использование CTE с аннотациями

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

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.IntegerField()
Теперь создадим CTE, чтобы вычислить общую стоимость каждого продукта.

from django.db.models import F, ExpressionWrapper, DecimalField
from django.db.models.expressions import With

# Создаем CTE для вычисления общей стоимости
cte = With(
    Product.objects.annotate(
        total_cost=ExpressionWrapper(F('price') * F('quantity'), output_field=DecimalField())
    ).values('id', 'name', 'total_cost')
)

# Используем CTE для получения продуктов с их общей стоимостью
products_with_cost = cte.queryset()

# Получаем результат
for product in products_with_cost:
    print(product['name'], product['total_cost'])
SQL:

WITH "cte" AS (
    SELECT
        "product"."id",
        "product"."name",
        ("product"."price" * "product"."quantity") AS "total_cost"
    FROM
        "product"
)
SELECT
    "cte"."id",
    "cte"."name",
    "cte"."total_cost"
FROM
    "cte";

Заключение

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

Хотя Django ORM не поддерживает CTE напрямую, их можно использовать:

• Через RawSQL для ручного написания SQL.

• С помощью библиотеки django-cte, которая интегрирует CTE в ORM-стиль.

И помните, СТЕ это инструмент, а не серебряная пуля. Всегда анализируйте план запроса (EXPLAIN ANALYZE), чтобы убедиться в их оптимальности.

Если заметили в статье неточности, ошибки или просто вам есть что добавить, милости прошу в комментарии. Вместе мы сможем улучшить этот материал и ещё больше людей научится грамотно использовать CTE :)

Полезные источники

• Книги

  Оптимизация запросов в PostgreSQL | Домбровская Г. Р., Новиков Борис

  https://www.ozon.ru/product/optimizatsiya-zaprosov-v-postgresql-dombrovskaya-g-r-novikov-boris-1798385340/

• Статьи

  Игра в прятки с оптимизатором. Гейм овер, это CTE PostgreSQL 12

  https://habr.com/ru/companies/postgrespro/articles/451344/

  СTE, подзапрос или представление?

  https://habr.com/ru/articles/855694/

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

  https://docs.arenadata.io/ru/ADPG/current/how-to/queries/common-table-expressions.html?ysclid=m6fyx8qq34927657123

  Обобщённое табличное выражение, оператор WITH

  https://sql-academy.org/ru/guide/operator-with

• Документация

  Django CTE

  https://dimagi.github.io/django-cte/

  PostgresPro Запросы WITH (Общие табличные выражения)

  https://postgrespro.ru/docs/postgrespro/9.5/queries-with