Простые CTE для подготовки данных: примеры и реальные кейсы

Простые CTE для подготовки данных: примеры и реальные кейсы

Кажется, вы уже освоили основы CTE и, возможно, даже пишете WITH почти на автомате. Сегодня давайте нырнём чуть глубже — и посмотрим, как использовать CTE для подготовки данных в реальных ситуациях. Представьте, что вы собираетесь создать отчёт или сложный SQL-запрос: сначала нужно разложить ингредиенты — а уже потом варить вкусный аналитический «суп».

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

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

Готовы? Сейчас перейдём к примерам.

Фильтрация данных с помощью CTE

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

Представим, у нас есть таблица students, где хранятся оценки студентов:

Таблица students

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

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

Результат:

Что здесь удобно?

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

Агрегирование данных с помощью CTE

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

Таблица enrollments

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

Пример запроса:

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

Результат:

Здесь важно:

• Мы сгруппировали данные по course_id и подсчитали количество студентов для каждого курса.
• Таблица course_enrollments теперь содержит эту информацию, и её можно использовать для дальнейшего анализа.

Подготовка данных для отчётов

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

Таблица grades

Таблица students

Вместо громоздкого вложенного запроса можно спокойно собрать всё по шагам:

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

На первом этапе (avg_grades) мы посчитали средний балл для каждого студента и сразу отфильтровали только тех, кто показал хорошие результаты — выше 80. Во втором шаге (students_with_grades) мы аккуратно объединили эти данные с таблицей students, чтобы получить имена и фамилии. В результате финальный SELECT возвращает аккуратную таблицу, которую можно вставлять прямо в отчёт — всё уже подсчитано, отфильтровано и красиво оформлено.

Результат:

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

Расчёт сложных метрик

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

1. Количество студентов.
2. Средний балл по курсу.

Пример запроса:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

Ошибки, которые стоит избегать

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

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

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

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

2

Задача

SQL SELF, 27 уровень, 2 лекция

Недоступна

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

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

Открыть