Создание временных таблиц с использованием WITH

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

Но CTE делает всё ещё проще — он позволяет реализовать временные таблицы без лишней возни:

• Никаких забот по удалению.

  CTE живёт только в момент выполнения запроса. Как только SQL отработал — CTE исчезает, будто невидимый ассистент, который всё сделал и испарился, не оставив следов.

• Код становится понятнее.

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

• Отличный вариант для "одноразовых" задач.

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

Синтаксис CTE как временной таблицы

Сначала освежим синтаксис CTE:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

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

Пример: посчитаем отличников

Давайте создадим временную таблицу с помощью CTE, чтобы найти студентов, у которых средний балл (grade) выше 90. А затем выведем их список.

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Здесь мы:

• используем WITH для создания временной таблицы high_achievers.
• группируем внутри CTE мы оценки (grades) для каждого студента (student_id) и считаем средний балл.
• в запросе SELECT * мы просто ссылаемся на временную таблицу high_achievers, как на обычную таблицу.

Сравнение CTE и временных таблиц

Иногда возникает вопрос: а в чём разница между использованием CTE и временных таблиц, созданных через команду CREATE TEMP TABLE?

Вот пример традиционной временной таблицы (CREATE TEMP TABLE) для тех же данных:

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- Не забудьте удалить таблицу!

И аналогичный запрос через CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Когда использовать CTE, а когда — временные таблицы

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

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

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

Пример: Аггрегирование данных

Предположим, у нас есть таблица enrollments, в которой содержатся записи о том, какие студенты записаны на какие курсы. Мы хотим узнать, сколько студентов записано на каждый курс, причём учитывать только курсы с более чем 5 студентами.

Через CTE это делается так:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

Множественные 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
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

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

Теперь в запросе два CTE:

1. high_achievers находит отличников.
2. student_courses сопоставляет студентов с курсами.

Результат — список студентов с высоким средним баллом и их курсами.

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

Слишком большие данные. CTE материализуются в памяти PostgreSQL. Если вы создаёте CTE с огромным результатом, это может замедлить выполнение запроса или привести к превышению лимита памяти.

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

Забытые индексы. Если данные в CTE извлекаются из больших таблиц без индексов, то это замедлит запрос.

2

Задача

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

Недоступна

Создание и использование простой временной таблицы

Создание и использование простой временной таблицы

Открыть