Использование CTE для улучшения читаемости сложных запросов

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

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

Сравнение: Подзапрос vs CTE

На первый взгляд оба подхода делают одно и то же — фильтруют оценки по курсу и считают средний балл для каждого студента. Но посмотрите внимательнее: в версии с подзапросом логика "спрятана" внутри скобок, а в CTE она вынесена наружу и получила понятное имя filtered_grades. Теперь представьте, что таких промежуточных шагов не два, а десять!

Подзапрос:

SELECT student_id, AVG(grade) AS avg_grade
FROM (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
) subquery
GROUP BY student_id;

CTE:

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE course_id = 101
)
SELECT student_id, AVG(grade) AS avg_grade
FROM filtered_grades
GROUP BY student_id;

Найдите 10 отличий. Конечно, CTE выигрывает по удобству чтения!

Разделение сложных запросов на этапы с помощью CTE

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

Пример:

WITH avg_grades AS (
    SELECT student_id, course_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id, course_id
),
course_teachers AS (
    SELECT course_id, teacher_id
    FROM courses
)

SELECT ag.student_id, ag.avg_grade, ct.teacher_id
FROM avg_grades ag
JOIN course_teachers ct ON ag.course_id = ct.course_id;

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

Использование нескольких CTE для крупного отчета

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

1. Сначала найдем студентов с высоким средним баллом (выше 90).
2. Затем сопоставим их с курсами.
3. Наконец, добавим данные о преподавателях.

Запрос с несколькими CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name, c.teacher_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
),
teachers AS (
    SELECT teacher_id, name AS teacher_name
    FROM teachers
)

SELECT ha.student_id, ha.avg_grade, sc.course_name, t.teacher_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN teachers t ON sc.teacher_id = t.teacher_id;

Этот запрос хорош тем, что каждую часть задачи мы оформили как отдельный, логически завершенный блок. Хотите узнать, кто из студентов достиг впечатляющих успехов? Посмотрите в CTE high_achievers. Интересует их связь с курсами? Это student_courses. Нужные преподаватели? Всё в teachers. Такой подход значительно упрощает поддержку и модификацию кода.

Разделение на этапы сложных расчетов

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

Пример:

WITH course_stats AS (
    SELECT course_id, COUNT(student_id) AS student_count, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY course_id
),
popular_courses AS (
    SELECT course_id
    FROM course_stats
    WHERE student_count > 50
)
SELECT c.course_name, cs.student_count, cs.avg_grade
FROM popular_courses pc
JOIN course_stats cs ON pc.course_id = cs.course_id
JOIN courses c ON c.course_id = pc.course_id;

Здесь мы сначала собираем статистику по курсам в course_stats, затем фильтруем популярные курсы в popular_courses, а уже потом объединяем это с таблицей курсов. Такой метод позволяет выделить промежуточные этапы, значительно упрощая понимание запроса.

Когда CTE становится незаменимым?

Вот несколько сценариев, где CTE особенно полезен:

1. Аналитика и отчетность. Например, расчет сложных показателей с фильтрацией по группам.
2. Работа с иерархическими структурами. Рекурсивные CTE для построения дерева категорий или организационной структуры.
3. Повторное использование данных. Например, если одна и та же выборка используется на разных этапах запроса.

Типичные ошибки при использовании CTE

Конечно, как и любое другое мощное средство, CTE имеет свои скрытые ловушки.

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

Неправильные соединения. Иногда сложные запросы с несколькими CTE становятся труднооптимизируемыми. Всегда проверяйте свои запросы с помощью EXPLAIN или EXPLAIN ANALYZE.

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

2

Задача

SQL SELF, 28 уровень, 0 лекция

Недоступна

Использование нескольких CTE для подсчета количества студентов

Использование нескольких CTE для подсчета количества студентов

Открыть