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