Створення тимчасових таблиць з використанням 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 витягуються з великих таблиць без індексів, це сповільнить запит.