Знайомство з 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-запитів.

Вперед — до своїх власних проектів!