0Pricing
Coding Interview Prep · Урок

Написание первого CTE

Базовый синтаксис WITH и случаи, когда CTE повышает читаемость по сравнению с подзапросом.

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

Что такое CTE на самом деле

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

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

Базовый синтаксис WITH

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

  • WITH cte_name AS ( ... ) определяет блок.
  • Запрос сразу после скобок — это основной запрос.
  • Имя CTE ведёт себя как имя таблицы, из которой можно выполнять SELECT.
WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT *
FROM recent_orders;

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

Ту же логику можно записать как встроенный подзапрос в предложении FROM. Тогда почему интервьюеры спрашивают о CTE?

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

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

Разобранный пример: сначала фильтрация, затем агрегация

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

Основной запрос воспринимает recent_orders как настоящую таблицу, благодаря чему агрегация остаётся понятной и очевидной.

WITH recent_orders AS (
    SELECT amount
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT SUM(amount) AS total_revenue
FROM recent_orders;

Именование выходных столбцов

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

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

WITH revenue (region, total) AS (
    SELECT region, SUM(amount)
    FROM orders
    GROUP BY region
)
SELECT region, total
FROM revenue
ORDER BY total DESC;

CTE — это просто именованный запрос

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

Это означает, что внутри CTE разрешено всё, что разрешено в обычном SELECT: соединения, GROUP BY, WHERE, оконные функции и многое другое.

Более глубокий пример: CTE с соединением

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

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

WITH order_counts AS (
    SELECT customer_id, COUNT(*) AS num_orders
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, oc.num_orders
FROM customers c
JOIN order_counts oc
    ON oc.customer_id = c.id;

Где CTE находится в запросе

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

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

CTE работает с INSERT, UPDATE и DELETE

Частый дополнительный вопрос: CTE не ограничены операторами SELECT. В большинстве современных баз данных предложение WITH можно добавлять и к операторам изменения данных.

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

WITH stale AS (
    SELECT id
    FROM sessions
    WHERE last_seen < NOW() - INTERVAL '30 days'
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM stale);

Распространённые ошибки начинающих

Интервьюеры обращают внимание на следующие ошибки:

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

Как рассказывать о CTE на собеседовании

Когда Вас просят переработать запутанный подзапрос, озвучьте ход своих мыслей: Я вынесу этот фильтрующий подзапрос в CTE под названием recent_orders, чтобы агрегация стала понятнее.

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

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

Проверьте, насколько хорошо Вы усвоили базовый синтаксис CTE и область видимости.

Повторение: Ваш первый CTE

Вы узнали, что CTE использует WITH name AS ( ... ), чтобы дать имя временному набору результатов, а затем обращается к нему как к таблице в следующем операторе.

  • CTE повышают читаемость, повторное использование и удобство отладки по сравнению со встроенными подзапросами.
  • Область видимости ограничена одним оператором; после этого они исчезают.
  • Они работают с SELECT, а также с INSERT/UPDATE/DELETE.
  • Они сами по себе не повышают производительность.

Далее: объединение нескольких CTE в конвейер.

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

Урок «Написание первого CTE» бесплатный?

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

Чему я научусь в уроке «Написание первого CTE»?

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

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

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

Сколько времени занимает урок «Написание первого CTE»?

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

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

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

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

  1. Написание первого CTE
  2. Объединение нескольких CTE в цепочку
  3. CTE, подзапрос или временная таблица
  4. Преобразование вложенных запросов в CTE
← Назад к Coding Interview Prep