Виртуальные таблицы и Common Table Expressions (CTE)

Зачем нужны CTE? Временные таблицы на лету 🏗️

Когда запрос становится сложным, как лабиринт, на помощь приходят Common Table Expressions (CTE) — виртуальные таблицы, существующие только во время выполнения запроса. Они делают код:

Чище — разбивают монолитные запросы на логические блоки
Читабельнее — как главы в книге
Удобнее для отладки — можно проверить каждую часть отдельно

WITH team_stats AS (
  SELECT 
    team_id,
    COUNT(*) AS games_played,
    AVG(score) AS avg_score
  FROM matches
  GROUP BY team_id
)
SELECT * FROM team_stats WHERE avg_score > 50;

Базовый синтаксис CTE ⚙️

CTE начинается с ключевого слова WITH, за которым следует имя и определение:

WITH виртуальная_таблица AS (
  SELECT ... FROM ... WHERE ...
)
SELECT * FROM виртуальная_таблица;

Важно:

CTE существует только в рамках одного запроса
Можно создавать несколько CTE через запятую
Допускается вложенность (CTE внутри CTE)

Реальный пример: анализ продаж 🛒

Допустим, нам нужно найти лучших менеджеров по динамике продаж:

WITH monthly_sales AS (
  SELECT 
    manager_id,
    EXTRACT(MONTH FROM sale_date) AS month,
    SUM(amount) AS total
  FROM sales
  GROUP BY manager_id, month
),
growth AS (
  SELECT 
    manager_id,
    (MAX(total) - MIN(total)) / MIN(total) AS growth_rate
  FROM monthly_sales
  GROUP BY manager_id
)
SELECT 
  m.name,
  g.growth_rate * 100 AS "Рост, %"
FROM managers m
JOIN growth g ON m.id = g.manager_id
ORDER BY g.growth_rate DESC
LIMIT 5;

Рекурсивные CTE: мощь рекурсии 🔄

Особый тип CTE, который умеет обращаться к самому себе — идеально подходит для иерархических данных (например, структура компании):

WITH RECURSIVE employee_tree AS (
  -- Базовый случай: начальник
  SELECT id, name, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Рекурсивная часть: подчинённые
  SELECT e.id, e.name, et.level + 1
  FROM employees e
  JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree ORDER BY level;

Осторожно: всегда указывайте условие остановки рекурсии!

CTE vs подзапросы: когда что использовать ⚖️

Преимущества CTE:

Читаемость — именованные блоки вместо вложенности
Многократное использование — можно ссылаться несколько раз
Проще дебажить — проверяем каждую часть отдельно

Подзапросы лучше, когда:

Нужна простая однострочная операция
Требуется максимальная производительность (иногда)

Оптимизация запросов с CTE 🚀

Советы от Данилы:

Ограничивайте данные в CTE настолько рано, насколько возможно
Для больших таблиц добавляйте индексы по полям соединения
Избегайте излишней вложенности — 3-4 CTE обычно оптимально

WITH filtered_data AS (
  SELECT * FROM huge_table WHERE created_at > '2023-01-01'
)
-- Дальше работаем только с отфильтрованными данными

Практика: попробуйте сами! ✏️

Задача: Найдите клиентов, которые покупали больше всего категорий товаров.

Подсказка: вам понадобятся три CTE:

Связь клиентов с заказами
Связь заказов с категориями
Подсчёт уникальных категорий на клиента

WITH client_orders AS (
  SELECT client_id, order_id 
  FROM orders
),
order_categories AS (
  SELECT 
    o.order_id,
    p.category_id
  FROM order_items o
  JOIN products p ON o.product_id = p.id
),
client_category_counts AS (
  SELECT
    co.client_id,
    COUNT(DISTINCT oc.category_id) AS unique_categories
  FROM client_orders co
  JOIN order_categories oc ON co.order_id = oc.order_id
  GROUP BY co.client_id
)
SELECT 
  c.name,
  ccc.unique_categories
FROM clients c
JOIN client_category_counts ccc ON c.id = ccc.client_id
ORDER BY ccc.unique_categories DESC
LIMIT 10;