Подзапросы и CTE: сложные запросы без боли

Подзапросы и CTE: сложные запросы без боли

Главная идея

Как это выглядит на практике

1. Ты строишь страницу профиля пользователя, где нужно показать его 5 последних заказов с суммой и статусом. Начинаешь с простого подзапроса в WHERE: SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 5.
2. Появляется требование: показать только пользователей, которые сделали хотя бы один заказ на сумму больше 5000 руб. Пишешь EXISTS: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 5000).
3. Нужно найти пользователей без ни одного заказа. Пишешь NOT IN (SELECT user_id FROM orders). Тест падает — в orders есть строки с user_id = NULL из-за гостевых заказов. NOT IN возвращает пустой результат. Переписываешь на NOT EXISTS — работает корректно.
4. Задача усложняется: нужен отчёт по топ-клиентам с суммой заказов, количеством и датой последнего заказа. Первая попытка — три отдельных запроса в Ruby с объединением в хэш. Понимаешь что это медленно — переносишь в один CTE.
5. Пишешь WITH order_stats AS (SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total, MAX(created_at) AS last_at FROM orders GROUP BY user_id) SELECT u.*, os.* FROM users u JOIN order_stats os ON u.id = os.user_id ORDER BY os.total DESC LIMIT 20. Три round-trips превращаются в один.
6. Запрос становится сложнее: нужно добавить фильтрацию по периоду, исключить заблокированных пользователей, добавить их регион из другой таблицы. Разбиваешь на несколько CTE: filtered_orders, user_regions, order_stats — каждый делает одно дело.
7. Аналитик просит воронку: сколько пользователей зарегистрировалось → добавило в корзину → купило за последние 30 дней. Три события из разных таблиц. Пишешь три CTE и джойнишь их по дате. Один запрос, один round-trip.
8. Новое требование: категории товаров хранятся в виде дерева (parent_id). Нужно получить все подкатегории для раздела 'Одежда' включая вложенные. Обычный JOIN не работает для произвольной глубины. Узнаёшь о рекурсивном CTE.
9. Пишешь WITH RECURSIVE cat_tree AS (SELECT * FROM categories WHERE slug = 'clothing' UNION ALL SELECT c.* FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id WHERE ct.depth < 8) SELECT * FROM cat_tree. Получаешь все уровни дерева одним запросом.
10. Производительность: замечаешь что запрос с несколькими CTE работает медленнее ожидаемого. Запускаешь EXPLAIN ANALYZE и видишь что один CTE материализуется (PostgreSQL 11) и не использует индекс. Добавляешь NOT MATERIALIZED — производительность восстанавливается.
11. Рефакторинг: коллега показывает запрос с 4 вложенными подзапросами в WHERE — нечитаемый. Вы вместе переписываете в CTE: каждый уровень вложенности становится именованным шагом. Код ревью проходит с первого раза.
12. На собеседовании спрашивают разницу между CTE и временной таблицей. Объясняешь: CTE живёт только в рамках одного запроса, не создаёт объект в БД, может быть встроен в план оптимизатором. Временная таблица (CREATE TEMP TABLE) материализуется на диске/памяти и доступна в нескольких запросах сессии.

Примеры кода

Подзапросы: IN, EXISTS, скалярный и в FROM

-- Подзапрос в WHERE с IN: пользователи из РФ с заказами
SELECT id, email, name
FROM users
WHERE country = 'RU'
  AND id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
  );

-- EXISTS: быстрее IN для больших наборов (short-circuit evaluation)
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
    AND o.status = 'completed'
    AND o.amount > 10000
);

-- NOT EXISTS безопасен с NULL (NOT IN опасен!)
-- Найти пользователей БЕЗ ни одного заказа
SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Подзапрос в FROM (derived table / встроенная таблица)
SELECT
  category_id,
  avg_price,
  CASE WHEN avg_price > 5000 THEN 'premium' ELSE 'standard' END AS segment
FROM (
  SELECT category_id, AVG(price) AS avg_price
  FROM products
  WHERE is_active = true
  GROUP BY category_id
) AS category_stats
ORDER BY avg_price DESC;

CTE: читаемые многоуровневые запросы

-- Простой CTE: топ-10 клиентов за последние 90 дней
WITH recent_orders AS (
  SELECT
    user_id,
    COUNT(*)          AS order_count,
    SUM(amount)       AS total_spent,
    MAX(created_at)   AS last_order_at
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '90 days'
    AND status = 'completed'
  GROUP BY user_id
),
ranked_customers AS (
  SELECT
    u.id,
    u.email,
    u.name,
    ro.order_count,
    ro.total_spent,
    ro.last_order_at,
    RANK() OVER (ORDER BY ro.total_spent DESC) AS spending_rank
  FROM users u
  JOIN recent_orders ro ON u.id = ro.user_id
  WHERE u.is_active = true
)
SELECT *
FROM ranked_customers
WHERE spending_rank <= 10
ORDER BY spending_rank;

-- CTE с несколькими шагами: отчёт по конверсии воронки
WITH
visits AS (
  SELECT DATE(created_at) AS day, COUNT(*) AS visit_count
  FROM page_views
  WHERE page = '/product' AND created_at >= NOW() - INTERVAL '7 days'
  GROUP BY DATE(created_at)
),
cart_adds AS (
  SELECT DATE(created_at) AS day, COUNT(*) AS cart_count
  FROM cart_events
  WHERE event = 'add' AND created_at >= NOW() - INTERVAL '7 days'
  GROUP BY DATE(created_at)
),
purchases AS (
  SELECT DATE(created_at) AS day, COUNT(*) AS purchase_count
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '7 days'
  GROUP BY DATE(created_at)
)
SELECT
  v.day,
  v.visit_count,
  ca.cart_count,
  p.purchase_count,
  ROUND(ca.cart_count::numeric / NULLIF(v.visit_count, 0) * 100, 2) AS visit_to_cart_pct,
  ROUND(p.purchase_count::numeric / NULLIF(ca.cart_count, 0) * 100, 2) AS cart_to_purchase_pct
FROM visits v
LEFT JOIN cart_adds ca ON v.day = ca.day
LEFT JOIN purchases p  ON v.day = p.day
ORDER BY v.day;

Рекурсивный CTE для иерархий

-- Обход дерева: все потомки категории 'Электроника'
WITH RECURSIVE category_tree AS (
  -- Базовый случай: корневая категория
  SELECT
    id,
    name,
    parent_id,
    slug,
    0          AS depth,
    name::text AS path
  FROM categories
  WHERE slug = 'electronics'

  UNION ALL

  -- Рекурсивная часть: дочерние категории
  SELECT
    c.id,
    c.name,
    c.parent_id,
    c.slug,
    ct.depth + 1,
    ct.path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.depth < 10  -- защита от бесконечного цикла
)
SELECT id, name, depth, path
FROM category_tree
ORDER BY path;

-- Рекурсивный CTE с подсчётом товаров в ветке
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.depth < 5
)
SELECT
  ct.id,
  ct.name,
  ct.depth,
  COUNT(p.id) AS product_count
FROM category_tree ct
LEFT JOIN products p ON p.category_id = ct.id AND p.is_active = true
GROUP BY ct.id, ct.name, ct.depth
ORDER BY ct.depth, ct.name;

Что происходит под капотом

• Подзапрос в WHERE с IN: PostgreSQL строит HashSet из результата подзапроса, затем проверяет каждую строку внешнего запроса за O(1). При больших наборах (100k+ строк) лучше JOIN или EXISTS.
• EXISTS использует semi-join стратегию: для каждой строки внешнего запроса выполняет подзапрос и останавливается при первом совпадении (short-circuit). Это быстрее IN когда совпадений много в начале набора.
• NOT IN с NULL: SQL использует трёхзначную логику (TRUE/FALSE/UNKNOWN). Если подзапрос содержит NULL, то NOT IN всегда возвращает UNKNOWN — пустой результат. NOT EXISTS корректно обрабатывает NULL через отсутствие строк.
• Коррелированный подзапрос (ссылается на столбцы внешнего запроса) выполняется для каждой строки внешнего запроса. При 10k строк внешнего — 10k выполнений подзапроса. PostgreSQL часто оптимизирует их в JOIN автоматически.
• CTE в PostgreSQL до версии 12 всегда материализовался — выполнялся отдельно и результат сохранялся в памяти. С версии 12+ оптимизатор может встроить (inline) CTE в основной план если это выгодно.
• Явное управление материализацией: WITH cte AS MATERIALIZED (...) — принудительная материализация (защита от многократного выполнения при нескольких ссылках на CTE). WITH cte AS NOT MATERIALIZED (...) — принудительный inline.
• Рекурсивный CTE использует структуру данных WorkTable: базовый случай заполняет таблицу, рекурсивная часть читает из WorkTable и добавляет новые строки пока не останется новых строк или не сработает защитное условие.
• EXPLAIN ANALYZE показывает CTE Scan для материализованных CTE как отдельный узел в дереве плана. Не-материализованный CTE встраивается в план и его узел не виден отдельно — это хорошо для производительности.
• Window functions и CTE: оконные функции (RANK, ROW_NUMBER) нельзя использовать в WHERE, только в SELECT. Стандартный паттерн: WITH ranked AS (SELECT *, RANK() OVER (...) AS r FROM t) SELECT * FROM ranked WHERE r <= 10.
• Derived table (подзапрос в FROM) и CTE с одной ссылкой генерируют идентичный план выполнения в большинстве случаев начиная с PostgreSQL 12. Выбирай тот вариант, который читаемее.

Термины урока

Мини-разбор реальной ситуации

Задача: страница аналитики для менеджера — топ-20 клиентов за квартал с суммой заказов, количеством, средним чеком, регионом и статусом (активный/VIP/churned). Первая реализация: 4 запроса в Ruby, merge в хэш, сортировка в памяти — 800ms. Переписано в один запрос: WITH quarterly_orders AS (фильтрация по дате и статусу), order_stats AS (агрегация по user_id), user_segments AS (определение сегмента через CASE), customer_regions AS (JOIN с регионами). Финальный SELECT из user_segments JOIN всем необходимым с ORDER BY и LIMIT 20. Результат: 45ms, код в одном месте, тестируется одним запросом в psql.