Lesson 5

Урок 5: CTE — Робимо Запити Такими Ж Організованими, Як Британське Чаювання!

Вітаю знову, шанувальнику даних! Сьогодні ми зануримося у світ Common Table Expressions (CTE) — вишуканий спосіб тримати свої складні SQL-запити акуратними й елегантними. Уяви собі CTE як дворецьких для SQL: вони готують усе завчасно, стежать, щоб дані лилися плавно, і роблять це з неабиякою порцією стилю.

Що Таке CTE і Навіщо Воно Потрібне?

CTE — це тимчасові результати, яким ти даєш ім’я, і які існують лише на час виконання твого запиту. Уяви, що ти запросив дворецького подати чай. Він усе приготує (твої дані), акуратно розставить на столі (твій CTE) і потім зникне так само тихо, як і з’явився. Ти насолоджуєшся чаєм, навіть не поворухнувши пальцем!

CTE чудово підходять для:

Розбиття складних запитів на зрозумілі частини.
Повторного використання тих самих запитів без дублювання.
Створення SQL, що виглядає так само вишукано, як чашечка справжнього англійського чаю!

Створення CTE: Ключове Слово `WITH`

CTE визначається за допомогою слова WITH, після якого йде ім’я (як призначення дворецькому). Далі ти задаєш запит, який створює CTE, і використовуєш його так, ніби це окрема таблиця:

WITH misyiyi_lytsari AS (
  SELECT imya, korolivstvo, COUNT(misyia) AS misyiyi_vseho
  FROM misyiyi
  GROUP BY imya, korolivstvo
)
SELECT * FROM misyiyi_lytsari;

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

Кілька CTE: Коли Потрібно Зібрати Всіх Дворецьких

Можна визначати кілька CTE, розділяючи їх комами. Уявімо, що ти хочеш дізнатися, які лицарі — ветерани (більше 3 місій) і які королівства лідирують за сміливістю:

WITH misyiyi_lytsari AS (
  SELECT imya, korolivstvo, COUNT(misyia) AS misyiyi_vseho
  FROM misyiyi
  GROUP BY imya, korolivstvo
),
lytsari_veterany AS (
  SELECT imya, korolivstvo
  FROM misyiyi_lytsari
  WHERE misyiyi_vseho > 3
)
SELECT lytsari_veterany.imya, lytsari_veterany.korolivstvo
FROM lytsari_veterany
JOIN misyiyi_lytsari ON lytsari_veterany.imya = misyiyi_lytsari.imya;

Тут misyiyi_lytsari — наш перший CTE, а lytsari_veterany будується на його основі. Це як запросити на вечерю ще одного дворецького, щоб той стежив за порядком!

Рекурсивні CTE: Коли Одного Дворецького Недостатньо

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

WITH RECURSIVE rodovid_lytsari AS (
  SELECT imya, mentor, 1 AS pokolinnya
  FROM lytsari
  WHERE mentor IS NULL
  
  UNION ALL
  
  SELECT l.imya, l.mentor, r.pokolinnya + 1
  FROM lytsari l
  JOIN rodovid_lytsari r ON l.mentor = r.imya
)
SELECT * FROM rodovid_lytsari;

Цей запит будує родовід, починаючи з найвищого лицаря (без наставника) і спускаючись вниз через покоління. Це як мати цілу бригаду дворецьких, кожен з яких стежить за своїм гіллям родинного дерева!

Чому CTE Кращі за Підзапити?

Хоча підзапити можуть бути корисними, вони можуть стати заплутаними, якщо їх використовувати занадто часто. CTE, навпаки, тримають твій SQL чітким і охайним:

Зрозумілість: CTE розбивають складні запити на окремі шматочки.
Повторне Використання: Визнач один раз, використовуй скільки завгодно.
Налагодження: Легше знайти, де все пішло шкереберть.

Тож наступного разу, коли ти боротимешся з заплутаним запитом, поклич CTE-дворецького, щоб підтримати порядок у твоєму SQL.

Збираємо Все Разом: Великий Бал CTE

Ось фінальний приклад, що покаже, як це працює:

Ти хочеш знайти всіх лицарів, у яких більше 3 місій, і дізнатися, яке королівство має найбільший відсоток таких ветеранів. Розділимо запит на кілька CTE:

WITH misyiyi_lytsari AS (
  SELECT imya, korolivstvo, COUNT(misyia) AS misyiyi_vseho
  FROM misyiyi
  GROUP BY imya, korolivstvo
),
lytsari_veterany AS (
  SELECT imya, korolivstvo
  FROM misyiyi_lytsari
  WHERE misyiyi_vseho > 3
),
smilyvist_korolivstva AS (
  SELECT korolivstvo, COUNT(*) AS kilkist_veteraniv, 
         (COUNT(*)::float / (SELECT COUNT(*) FROM lytsari WHERE lytsari.korolivstvo = smilyvist_korolivstva.korolivstvo)) * 100 AS vidvidok_veteraniv
  FROM lytsari_veterany
  GROUP BY korolivstvo
)
SELECT korolivstvo, vidvidok_veteraniv
FROM smilyvist_korolivstva
ORDER BY vidvidok_veteraniv DESC;

Ця витончена структура показує, як CTE можуть зробити керування навіть найскладнішими запитами приємним і зрозумілим.

Що Ми Сьогодні Вивчили?

Сьогодні ми дослідили світ CTE:

Базові CTE: Організовують твої запити в зрозумілі частини.
Кілька CTE: Поєднуються для покращення читабельності.
Рекурсивні CTE: Ідеальні для роботи з ієрархічними даними.

Що Далі?

У наступному уроці ми розкриємо секрети Віконних Функцій — інструментів, які можуть піднести твою аналітику даних на королівський рівень!

Готовий принести трохи елегантності в свої запити за допомогою CTE? Надінь свій найкращий SQL-капелюх і готуйся до наступного розділу!