Прості CTE для підготовки даних: приклади та реальні кейси

Прості CTE для підготовки даних: приклади та реальні кейси

Здається, ти вже освоїв основи CTE і, може, навіть пишеш WITH майже на автоматі. Сьогодні давай зануримось трохи глибше — і подивимось, як юзати CTE для підготовки даних у реальних ситуаціях. Уяви, що ти збираєшся створити звіт або складний SQL-запит: спочатку треба розкласти інгредієнти — а вже потім варити смачний аналітичний «суп».

CTE тут — це топовий інструмент для проміжних кроків: фільтрації, підрахунків, агрегації, розрахунку середніх значень — усього, що треба для осмисленої підготовки даних. Ти можеш розбити складний запит на зрозумілі логічні блоки, кожен з яких робить щось одне: відбирає потрібні записи, рахує середнє або готує дані для фінальної вибірки. Це спрощує читання коду, позбавляє від повторів і дозволяє уникнути використання тимчасових таблиць, якщо вони тобі не потрібні.

Підхід із CTE особливо корисний, коли ти готуєш дані для звітів, будуєш складну фільтрацію або хочеш «почистити» дані перед подальшою обробкою. У цьому сенсі CTE стає не просто технічним прийомом, а повноцінною стратегією — вибудовування логіки крок за кроком, не втрачаючи контроль над процесом.

Готовий? Зараз перейдемо до прикладів.

Фільтрація даних за допомогою CTE

CTE — це класний спосіб «витягнути» потрібні дані з загальної таблиці, щоб далі працювати вже тільки з тим, що реально треба. Замість того щоб писати громіздкі вкладені запити, ти спочатку фільтруєш дані, даєш цьому кроку ім'я — і потім спокійно працюєш із результатом як зі звичайною таблицею.

Уявімо, у нас є таблиця students, де зберігаються оцінки студентів:

Таблиця students

Допустимо, ти хочеш вибрати всіх, у кого оцінка вище 85. За допомогою CTE це робиться максимально прозоро:

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

Результат:

Що тут зручно?

Ти заздалегідь відібрав потрібні рядки і дав цьому кроку ім'я — excellent_students. Тепер цей результат можна юзати далі: наприклад, об'єднати з іншою таблицею, зробити ще одну фільтрацію або порахувати середню оцінку. Все читабельно, просто і не заплутує, особливо якщо запит великий.

Агрегування даних за допомогою CTE

Тепер перейдемо до випадку, коли треба порахувати кількість записів або обчислити середні значення. Наприклад, у нас є таблиця enrollments, де зберігаються дані про те, які студенти записані на які курси.

Таблиця enrollments

Ми хочемо дізнатись, скільки студентів записано на кожен курс.

Приклад запиту:

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

Результат:

Тут важливо:

• Ми згрупували дані по course_id і порахували кількість студентів для кожного курсу.
• Таблиця course_enrollments тепер містить цю інфу, і її можна використовувати для подальшого аналізу.

Підготовка даних для звітів

Якщо тобі треба зібрати детальний звіт, заснований на кількох кроках обробки даних, CTE буде справжньою знахідкою. Він дозволяє розбити всю логіку на зрозумілі блоки і при цьому не створювати зайві тимчасові таблиці. Уяви, що у тебе є таблиця grades з оцінками і таблиця students з інфою про студентів. Треба скласти звіт, у якому будуть тільки ті студенти, чий середній бал вище 80.

Таблиця grades

Таблиця students

Замість громіздкого вкладеного запиту можна спокійно зібрати все по кроках:

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

На першому етапі (avg_grades) ми порахували середній бал для кожного студента і одразу відфільтрували тільки тих, хто показав гарні результати — вище 80. На другому кроці (students_with_grades) ми акуратно об'єднали ці дані з таблицею students, щоб отримати імена та прізвища. У результаті фінальний SELECT повертає акуратну таблицю, яку можна вставляти прямо у звіт — все вже пораховано, відфільтровано і гарно оформлено.

Результат:

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

Розрахунок складних метрик

Іноді доводиться комбінувати різні дані в одному запиті. Наприклад, треба порахувати для кожного курсу:

1. Кількість студентів.
2. Середній бал по курсу.

Приклад запиту:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

Помилки, яких варто уникати

Працюючи з CTE, можна легко заплутатись і зробити пару поширених помилок.

Перша — зайва матеріалізація. Якщо створюєш занадто багато CTE, PostgreSQL може зберегти їх результати як тимчасові таблиці, навіть якщо вони потрібні лише один раз. У підсумку запит буде працювати повільніше, ніж хотілося б.

Друга помилка — неправильне застосування фільтрів. Якщо фільтри накладаються у неправильному порядку або на різних етапах по-різному, підсумкова вибірка може вийти не такою, як ти очікував. Наприклад, можна випадково відсіяти важливі дані занадто рано.

Тому CTE краще використовувати там, де дані проходять кілька послідовних трансформацій — саме там цей інструмент розкриває всі свої переваги і допомагає писати чистий, зрозумілий та ефективний код.