Coding Interview Prep · Урок

Преобразование вложенных запросов в CTE

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

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

Рефакторинг на собеседовании в реальном времени

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

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

Начните с самого внутреннего запроса

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

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

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

Перенесите один уровень в CTE

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

Одно это действие уже убирает один уровень мысленной вложенности и даёт шагу содержательное имя.

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

По-настоящему вложенный пример

Вот более сложный пример для рефакторинга: два уровня вложенности плюс фильтр в стиле коррелированного подзапроса. Цель — вычислить среднюю стоимость заказа среди клиентов из верхней категории по расходам.

Запрос корректен, но его трудно читать. Мы разберём его поэтапно.

SELECT AVG(o.amount) AS avg_order
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id
    FROM (
        SELECT customer_id, SUM(amount) AS total
        FROM orders
        GROUP BY customer_id
    ) s
    WHERE s.total > 1000
);

Назовите первый этап

Самый глубокий блок вычисляет общую сумму расходов для каждого клиента. Перенесите его в CTE с именем spend. Теперь средний уровень просто фильтрует этот CTE.

Обратите внимание: каждое выделение уменьшает глубину вложенности на один уровень и добавляет самодокументируемое имя.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT AVG(o.amount) AS avg_order
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id FROM spend WHERE total > 1000
);

Назовите второй этап

Вынесите фильтр по spend в отдельный CTE — big_spenders. Оставшийся основной запрос превращается в плоское соединение или проверку принадлежности к явно названному набору.

Каждый этап теперь отвечает за одну задачу — отличительный признак аккуратного кода.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
),
big_spenders AS (
    SELECT customer_id FROM spend WHERE total > 1000
)
SELECT AVG(o.amount) AS avg_order
FROM orders o
JOIN big_spenders b ON b.customer_id = o.customer_id;

Сохраняйте смысл при рефакторинге

Золотое правило: рефакторинг не должен изменять результаты. Следите за ловушками, которые незаметно меняют выходные данные:

Замена IN на JOIN может привести к появлению дублирующихся строк, если правая часть не содержит уникальных значений.
NOT IN со значениями NULL ведёт себя иначе, чем NOT EXISTS.
Уровень детализации агрегации должен оставаться прежним.

Озвучьте эти риски, чтобы показать внимательность.

Проверка рефакторинга

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

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

SELECT COUNT(*), SUM(amount)
FROM orders
WHERE customer_id IN (SELECT customer_id FROM big_spenders);

Когда NOT следует выполнять рефакторинг

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

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

Контрольный список рефакторинга

Вот последовательность, которую можно воспроизводить на собеседовании:

Читайте изнутри наружу, чтобы найти самый глубокий подзапрос.
Вынесите его в именованный CTE.
Повторяйте, поднимаясь наружу на один уровень за раз.
Называйте каждый этап по тому, что он создаёт.
Убедитесь, что результаты не изменились (следите за ловушками с IN/JOIN и NULL).

Так пугающий вложенный запрос превращается в спокойную пошаговую переработку.

Как объяснять свой рефакторинг

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

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

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

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

Повторение: рефакторинг с помощью CTE

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

Называйте каждый этап по тому, что он создаёт.
Сохраняйте семантику; следите за дубликатами при IN и JOIN и ловушками NULL.
Проверяйте результат, сравнивая количество строк и строки из выборки.
Не делите запрос чрезмерно; остановитесь, когда он читается как ясная последовательность именованных шагов.

На этом курс по CTE завершён; теперь Вы можете уверенно выполнять рефакторинг на собеседовании в реальном времени.

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

Урок «Преобразование вложенных запросов в CTE» бесплатный?

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

Чему я научусь в уроке «Преобразование вложенных запросов в CTE»?

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

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

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

Сколько времени занимает урок «Преобразование вложенных запросов в CTE»?

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

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

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

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

← Назад к Coding Interview Prep