Coding Interview Prep · Урок

CTE, подзапрос или временная таблица

Сравнивайте компромиссы материализации, повторного использования и поведения оптимизатора.

«CTE, подзапрос или временная таблица» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

Три способа организовать логику по этапам

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

Этот урок формирует схему принятия решений, которую Вы сможете воспроизвести в стрессовой ситуации.

Подзапрос

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

Имя не требуется (производным таблицам нужен псевдоним).
Оптимизатор может объединить его с внешним запросом.
При глубокой вложенности он становится многословным и трудным для чтения.

SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

CTE

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

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

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

Временная таблица

Временная таблица — это настоящая физическая таблица, существующая в течение сеанса (или транзакции). Вы заполняете её одним оператором, а затем обращаетесь к ней в последующих отдельных операторах.

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

CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

SELECT * FROM spend WHERE total > 1000;

Материализация: ключевое различие

Ключевая концепция, которую проверяют интервьюеры, — это материализация: физически ли промежуточный результат записывается где-либо.

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

Барьер оптимизации и старая ловушка PostgreSQL

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

Упоминание этого нюанса показывает высокий уровень опыта.

WITH spend AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;

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

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

Если пересчёт требует больших затрат, принудительная материализация (или использование временной таблицы) позволяет не выполнять одну и ту же работу дважды.

Повторное использование в нескольких операторах

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

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

Индексы и статистика

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

CTE/подзапрос: оценки оптимизатора на основе исходных таблиц.
Временная таблица: Вы можете выполнить для неё ANALYZE и добавить индексы, настроенные для последующих операций JOIN.

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

Схема принятия решения

Чёткий ответ на собеседовании:

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

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

Как объяснить компромисс

Избегайте категоричных утверждений вроде «CTE всегда медленнее». Лучше скажите: CTE и подзапросы обычно встраиваются, поэтому они нужны прежде всего для читаемости; временная таблица материализуется и оправдана, когда я повторно использую большой результат в нескольких операторах или мне нужен индекс.

Признание того, что поведение зависит от СУБД (а в PostgreSQL — от версии), показывает глубокое понимание темы.

Быстрая проверка

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

Повторение: CTE, подзапрос и временная таблица

Выбор определяется материализацией и областью видимости.

Подзапросы и CTE: обычно встраиваются, действуют в рамках одного оператора и выбираются ради читаемости.
CTE добавляют именование и повторное использование в рамках одного оператора.
Временные таблицы: всегда материализуются, сохраняются между операторами и могут иметь индексы.
PostgreSQL 12+ встраивает простые CTE; используйте подсказки MATERIALIZED, чтобы управлять этим поведением.

Далее: рефакторинг запутанного вложенного запроса в понятные CTE.

Часто задаваемые вопросы

Урок «CTE, подзапрос или временная таблица» бесплатный?

Да — полный текст урока «CTE, подзапрос или временная таблица» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «CTE, подзапрос или временная таблица»?

Сравнивайте компромиссы материализации, повторного использования и поведения оптимизатора. Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «CTE, подзапрос или временная таблица»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к Coding Interview Prep