Знакомство с CTE: WITH

В программировании можно вынести отдельно кусок кода и дать ему имя — создать функцию. Так же и с CTE. Вы можете вынести SELECT-подзапрос отдельно от основного запроса, дать ему имя и потом использовать его в основном SQL-запросе.

CTE (Common Table Expressions, или общие табличные выражения) — это как свежий глоток воздуха для уставшего от вложенных запросов разработчика. Они делают SQL-код не только понятным, но и по-настоящему элегантным. Если раньше вы сидели над громоздкими конструкциями из подзапросов и чувствовали, как в глазах рябит — самое время познакомиться с "магией" CTE.

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

По сути, CTE — это виртуальные таблицы, которые вы создаёте на лету с помощью SELECT-запроса. Что-то вроде подзапросов, только круче. Если в программировании можно вынести кусок логики в отдельную функцию с понятным названием, то в SQL эту роль берёт на себя CTE. Вы пишете SELECT, даёте ему имя — и используете его дальше как часть большого и сложного запроса. Красиво? Ещё бы.

Пример SQL-запроса с подзапросом:

-- основной запрос
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- подзапрос, который получил псевдоним filtered_students

Вынесли подзапрос отдельно:

-- CTE/подзапрос, который получил псевдоним filtered_students
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- основной запрос
SELECT *
FROM filtered_students;

Удивительно, но подзапросы появились раньше CTE на 20 лет! В стандарте SQL-89 уже были подзапросы, а вот CTE появляются только в стандарте SQL-2009.

Синтаксис WITH

CTE начинается с ключевого слова WITH и выглядит примерно так:

WITH cte_name AS (
    SELECT ... -- ваш запрос здесь
)

SELECT ...
FROM cte_name;

Здесь:

• cte_name — это имя вашего CTE. Вы можете выбирать любое осмысленное название, например, high_scores, filtered_data или даже best_students.
• Внутри скобок () пишется запрос, который подготавливает данные для последующего использования.
• После определения CTE вы можете обращаться к нему, как к обычной таблице, в основном запросе.

Пример 1: Простой CTE

Давайте посмотрим, как работает CTE на живом примере. Представим, что у нас есть таблица students — список студентов с их оценками:

Наша цель — выбрать всех студентов с оценкой выше 85 и вывести их данные.

Вариант без CTE:

Можно сделать это с помощью подзапроса:

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

А вот так же, но с CTE — гораздо приятнее глазу:

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

Согласитесь, выглядит чище и понятнее. Мы чётко отделили подготовку данных (WITH) от основной части запроса (SELECT). Это как сначала навести порядок на столе, а потом уже начинать работать — сразу дышится легче.

Пример 2: Несколько CTE

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

Дано: таблица grades, где хранятся оценки студентов по курсам:

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

Решение с несколькими CTE:

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- обращаемся к первому CTE - student_averages
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- обращаемся ко второму CTE - high_achievers

Здесь:

1. student_averages готовит предварительные данные — средние оценки студентов.
2. high_achievers использует эти данные, чтобы выбрать только тех, у кого оценки выше 85.

Отличие CTE от подзапросов

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

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

Пример:

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

Подзапросы могут быть внутри SELECT, внутри FROM, внутри WHERE и внутри HAVING. Кроме того, они могут ссылаться на колонки внешнего запроса. У CTE с последним сложности.

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

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

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

Когда использовать CTE?

• Когда вам нужно разбить сложный запрос на несколько логичных этапов.
• Если запрос должен быть читаемым и поддерживаемым. Никто не хочет разбираться в вложенных структурах кодов, напоминающих спагетти.
• Для временной подготовки данных, которые использует только текущий запрос.

Заключительный пример: анализ курсов

Давайте объединим всё, что узнали:

1. Найдём студентов с высокой средней оценкой.
2. Выведем их имена и курсы, на которые они записаны.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Обратите внимание, как всё структурировано:

1. Сначала подготовили средние оценки.
2. Потом выбрали только лучших студентов.
3. Затем связали их с курсами.

Теперь вы официально готовы начать использовать CTE для создания красивых, читаемых и мощных SQL-запросов.

Вперёд — к вашим собственным проектам!

2

Задача

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

Недоступна

Использование CTE для фильтрации данных

Использование CTE для фильтрации данных

Открыть