Урок 5: CTE — Сделаем Запросы Организованными, Как Английское Чаепитие!

Lesson 5

Урок 5: CTE — Сделаем Запросы Организованными, Как Английское Чаепитие!

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

Что такое CTE и зачем он нужен?

CTE — это временные именованные результаты, которые существуют только на время выполнения твоего запроса. Представь, что ты пригласил дворецкого подать чай. Он всё аккуратно разложит (твои данные), красиво расставит на столе (твой CTE) и затем бесшумно исчезнет, как только ты закончишь. Стол остаётся в порядке, и ты наслаждаешься чаем, не поднимая и пальца!

CTE идеально подходят для:

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

Создание CTE: Использование Ключевого Слова WITH

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

WITH misyii_rycari AS (
  SELECT imya, korolevstvo, COUNT(misiya) AS misyii_vsego
  FROM misyii
  GROUP BY imya, korolevstvo
)
SELECT * FROM misyii_rycari;

В этом примере CTE misyii_rycari подсчитывает количество миссий для каждого рыцаря и королевства. После того как он создан, ты можешь использовать его в основном запросе, делая запрос куда более понятным.

Множественные CTE: Когда На Вечеринке Мало Одного Дворецкого

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

WITH misyii_rycari AS (
  SELECT imya, korolevstvo, COUNT(misiya) AS misyii_vsego
  FROM misyii
  GROUP BY imya, korolevstvo
),
rycari_veterany AS (
  SELECT imya, korolevstvo
  FROM misyii_rycari
  WHERE misyii_vsego > 3
)
SELECT rycari_veterany.imya, rycari_veterany.korolevstvo
FROM rycari_veterany
JOIN misyii_rycari ON rycari_veterany.imya = misyii_rycari.imya;

Здесь misyii_rycari — наш первый CTE, а rycari_veterany построен на его основе. Это как пригласить на ужин второго дворецкого, чтобы следить за порядком!

Рекурсивные CTE: Когда Одного Дворецкого Мало

CTE становятся ещё мощнее, когда ты используешь их рекурсивно. Представь, что ты хочешь создать родословное древо рыцарей: сэр Артур обучал сэра Ланселота, который, в свою очередь, тренировал сэра Галахада, и так далее. С помощью рекурсивного CTE ты можешь спускаться и подниматься по этому дереву:

WITH RECURSIVE rodoslovnaya_rycar AS (
  SELECT imya, mentor, 1 AS pokolenie
  FROM rycari
  WHERE mentor IS NULL
  
  UNION ALL
  
  SELECT r.imya, r.mentor, ro.pokolenie + 1
  FROM rycari r
  JOIN rodoslovnaya_rycar ro ON r.mentor = ro.imya
)
SELECT * FROM rodoslovnaya_rycar;

Этот запрос строит родословное древо, начиная с главного рыцаря (у которого нет наставника) и спускаясь вниз по поколениям. Это как иметь целый штат дворецких, каждый из которых следит за своей ветвью семьи!

Почему CTE лучше подзапросов?

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

  • Читаемость: CTE разбивают сложные запросы на осмысленные блоки.
  • Повторное использование: Определяй один раз, используй сколько угодно.
  • Отладка: Легче понять, где что-то пошло не так.

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

Собираем Все: Большой Бал CTE

Вот финальный пример, который покажет, как это работает:

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

WITH misyii_rycari AS (
  SELECT imya, korolevstvo, COUNT(misiya) AS misyii_vsego
  FROM misyii
  GROUP BY imya, korolevstvo
),
rycari_veterany AS (
  SELECT imya, korolevstvo
  FROM misyii_rycari
  WHERE misyii_vsego > 3
),
hrabrost_korolevstv AS (
  SELECT korolevstvo, COUNT(*) AS chislo_veteranov, 
         (COUNT(*)::float / (SELECT COUNT(*) FROM rycari WHERE rycari.korolevstvo = hrabrost_korolevstv.korolevstvo)) * 100 AS procent_veteranov
  FROM rycari_veterany
  GROUP BY korolevstvo
)
SELECT korolevstvo, procent_veteranov
FROM hrabrost_korolevstv
ORDER BY procent_veteranov DESC;

Эта изящная структура показывает, как CTE могут сделать управление даже самыми сложными запросами простым и понятным.

Что Мы Сегодня Узнали?

Сегодня мы изучили мир CTE:

  • Базовые CTE: Организуют твои запросы в понятные части.
  • Множественные CTE: Объединяются для улучшения читаемости.
  • Рекурсивные CTE: Идеальны для работы с иерархическими данными.

Что Дальше?

В следующем уроке мы раскроем секреты Оконных Функций — инструментов, которые могут поднять твою аналитику данных на королевский уровень!

Готов добавить немного элегантности в свои запросы с помощью CTE? Надень свой лучший SQL-костюм и готовься к следующей главе!