SQL для аналитиков-разработчиков: CTE, рекурсивные запросы и читаемость вместо магии

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

Будем считать, что вы уверенно пишете SELECT, JOIN, оконные функции, понимаете агрегирование и умеете читать планы выполнения. Но статья будет полезна даже тем, кто давно использует SQL «как есть»: мы сфокусируемся именно на стиле и технике — на том, как сделать запросы поддерживаемыми.

CTE как инструмент проектирования запроса, а не “синтаксический сахар”

Что такое CTE и почему он помогает

CTE — это именованный подзапрос, который объявляется в начале запроса и затем используется в основном SELECT/INSERT/UPDATE/DELETE. Смысл CTE не в том, чтобы “удобнее писать”. Смысл — в том, чтобы разделить сложную бизнес-логику на понятные шаги.

В хороших запросах CTE играют роль “промежуточных слоёв”:

• очистка и приведение данных (нормализация, фильтры по времени, исключение мусора);
• подготовка фактологии (агрегации, расчёт метрик, разворачивание справочников);
• собственно аналитика (финальные витрины/отчёты/выборки);
• контроль качества (проверки на дубликаты, пропуски, аномалии).

Если мыслить запросом как пайплайном, CTE становятся естественным способом структурировать код.

Базовый шаблон “данные → логика → витрина”

Пример на упрощённой задаче: допустим, у нас есть таблица событий пользователей events(user_id, event_time, event_type, amount) и мы хотим посчитать по пользователям: дату первой покупки и количество дней между первой и последней покупкой.

WITH
base_events AS (
  SELECT
    user_id,
    event_time,
    event_type,
    amount
  FROM events
  WHERE event_type IN ('purchase')
),
user_first_last AS (
  SELECT
    user_id,
    MIN(event_time) AS first_purchase_time,
    MAX(event_time) AS last_purchase_time,
    COUNT(*) AS purchases_count
  FROM base_events
  GROUP BY user_id
)
SELECT
  user_id,
  CAST(first_purchase_time AS DATE) AS first_purchase_date,
  CAST(last_purchase_time AS DATE) AS last_purchase_date,
  DATE_DIFF('day', CAST(first_purchase_time AS DATE), CAST(last_purchase_time AS DATE)) AS days_between_first_and_last,
  purchases_count
FROM user_first_last;

Здесь CTE не “сокращает код”, а делает его читаемым: по имени base_events понятно назначение шага, по имени user_first_last — результат, который уйдёт дальше.

Частая ошибка: слишком много CTE без договорённостей

Одна из самых распространённых проблем — разрастание CTE до состояния, когда запрос превращается в набор “обрывков”, и читатель вынужден постоянно прыгать глазами:

• cte_1, cte_2, tmp, final_v2_v3;
• CTE делают тривиальные преобразования (переименование столбцов) и превращают запрос в многослойный лабиринт;
• бизнес-правила размазаны между десятками кусочков.

Практическое правило: каждый CTE должен иметь смыслимый “результат шага”, а не просто обрамлять SELECT.

Как писать “поддерживаемый” SQL: именование, границы ответственности, инварианты

1) Называйте CTE как сущности результата

Хорошие имена помогают быстрее понять запрос даже без контекста.

Примеры удачных имён:

• eligible_users — пользователи после фильтрации;
• monthly_active_users — уже агрегированная метрика;
• staged_payments — платежи, приведённые к единому формату;
• user_status_as_of — состояние “на дату”.

Примеры плохих:

• t1, cte, x, step2;
• имена без связи с бизнес-единицей.

2) Делайте границы ответственности: один CTE — одна роль

Если в одном CTE смешаны:

• очистка данных,
• расчёт метрик,
• фильтрация по бизнес-условиям,
• и финальная сортировка,

то при изменениях (а они неизбежны) вы рискуете случайно “поломать” часть логики.

Лучше — разбить:

• raw_to_clean (приведение и фильтры качества),
• calc_metrics (математика),
• apply_business_rules (ограничения),
• format_output (витрина).

3) Явные инварианты вместо “угадываний”

Инварианты — это условия, которые вы явно закладываете в код и сохраняете при правках.

Примеры инвариантов в SQL:

• “в staged_events нет дубликатов по (event_id)”;
• “в daily_user_activity одна строка на (user_id, activity_date)”;
• “иерархия строится из parent_id, где root не имеет родителя”.

В SQL это можно документировать комментариями и/или проверками.

Например, проверка уникальности:

WITH staged AS (
  SELECT *
  FROM events
),
dupes AS (
  SELECT
    event_id,
    COUNT(*) AS cnt
  FROM staged
  GROUP BY event_id
  HAVING COUNT(*) > 1
)
SELECT *
FROM dupes;

Если запрос вернул строки — инвариант нарушен. В продакшене можно не выводить результат, а писать в отчёт или логи.

4) Не злоупотребляйте “магическими” условиями в финале

Если сложная бизнес-логика спрятана в одном WHERE в конце, вы получите проблему: при изменении правил нужно снова “распутывать” выражение.

Синтаксически корректно, но плохо поддерживаемо. Вместо этого выносите условия в отдельный CTE или хотя бы в “именованные поля”.

Практика: CTE для задач аналитики-разработчика

Задача 1: де-дыпликация и сбор витрины с понятными шагами

Допустим, у вас есть таблица user_profile(user_id, updated_at, email, phone), где одна и та же сущность может обновляться несколько раз. Требуется взять “актуальные” данные на момент отчёта и построить витрину.

Идея: сначала отфильтровать по времени, потом выбрать актуальную запись на пользователя.

WITH
profile_before_report AS (
  SELECT
    user_id,
    updated_at,
    email,
    phone
  FROM user_profile
  WHERE updated_at < TIMESTAMP '2026-07-01 00:00:00'
),
latest_profile AS (
  SELECT
    user_id,
    email,
    phone,
    updated_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM profile_before_report
)
SELECT
  user_id,
  email,
  phone,
  updated_at
FROM latest_profile
WHERE rn = 1;

Почему это лучше, чем один огромный запрос? Потому что:

• “граница времени” выделена в отдельном CTE;
• “выбор актуальной записи” изолирован;
• вы можете вставить дополнительные проверки, не ломая всё остальное.

Задача 2: многослойные агрегаты без потери ясности

Часто встречается ситуация: требуется посчитать, например, удержание пользователей, но исходные данные сильно неоднородны (сессии, действия, транзакции). CTE помогают фиксировать уровень агрегации.

Пример: считаем для каждой когорты (месяц первой активности) число активных пользователей на каждом месяце.

WITH
first_activity AS (
  SELECT
    user_id,
    MIN(CAST(activity_time AS DATE)) AS first_date
  FROM user_sessions
  GROUP BY user_id
),
cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('month', first_date) AS cohort_month
  FROM first_activity
),
activity_by_month AS (
  SELECT
    user_id,
    DATE_TRUNC('month', activity_time) AS activity_month,
    COUNT(*) AS sessions_cnt
  FROM user_sessions
  GROUP BY user_id, DATE_TRUNC('month', activity_time)
),
cohort_retention AS (
  SELECT
    c.cohort_month,
    a.activity_month,
    COUNT(DISTINCT a.user_id) AS active_users
  FROM cohort c
  JOIN activity_by_month a
    ON a.user_id = c.user_id
  GROUP BY c.cohort_month, a.activity_month
)
SELECT
  cohort_month,
  activity_month,
  active_users
FROM cohort_retention
ORDER BY cohort_month, activity_month;

Здесь CTE фиксируют “контракты”:

• first_activity — по пользователю одна строка;
• activity_by_month — одна строка на (user_id, activity_month);
• финальный cohort_retention — агрегация по месяцам.

Это напрямую снижает риск ошибиться в join-логике.

Рекурсивные CTE: обход иерархий без “ручного” SQL

Когда рекурсивные запросы реально нужны

Рекурсивные CTE незаменимы в задачах:

• построение дерева/графа “родитель–потомок”;
• поиск всех предков или потомков;
• вычисление уровней (depth) и пути;
• распространение атрибутов по иерархии (например, “на кого влияет изменение в подразделении”);
• обработка “правил” в виде графа зависимостей.

Если вы пишете рекурсивную логику через несколько JOIN на фиксированную глубину — вы почти наверняка ограничите корректность и увеличите стоимость изменений. Рекурсивные CTE позволяют описать “всё, пока есть связь”.

Базовая структура рекурсивного CTE

Большинство СУБД поддерживают синтаксис вида:

• WITH RECURSIVE cte AS ( anchor_query UNION ALL recursive_query )
• где anchor даёт стартовый набор,
• recursive_query “расширяет” его, присоединяя новые вершины.

Ниже — универсальная модель для таблицы org_units(unit_id, parent_unit_id, name).

Пусть нам нужно построить путь от каждого узла к корню или наоборот. Рассмотрим задачу: найти всех потомков для заданного подразделения и посчитать глубину.

WITH RECURSIVE
hierarchy AS (
  -- anchor: стартуем с нужного узла
  SELECT
    unit_id,
    parent_unit_id,
    name,
    CAST(0 AS INTEGER) AS depth
  FROM org_units
  WHERE unit_id = 10

  UNION ALL

  -- recursive: добавляем детей к уже найденным
  SELECT
    child.unit_id,
    child.parent_unit_id,
    child.name,
    h.depth + 1 AS depth
  FROM org_units child
  JOIN hierarchy h
    ON child.parent_unit_id = h.unit_id
)
SELECT
  unit_id,
  parent_unit_id,
  name,
  depth
FROM hierarchy
ORDER BY depth, unit_id;

Это работает как “волна”: сначала найден стартовый узел, затем итеративно добавляются его дети, потом дети детей и так далее.

Подводный камень №1: циклы в данных

Реальные данные редко идеальны. Если в таблице иерархии возможны циклы (A → B → C → A), рекурсивный запрос может уйти в бесконечность или упереться в лимиты СУБД.

Самая практичная защита — хранить “маршрут” (путь) или хотя бы набор посещённых узлов и запрещать повторное посещение.

Например, будем хранить путь как строку с разделителями (подход зависит от СУБД; ниже — концепт).

WITH RECURSIVE
hierarchy AS (
  SELECT
    unit_id,
    parent_unit_id,
    name,
    0 AS depth,
    CAST('/' || unit_id || '/' AS VARCHAR(2000)) AS path
  FROM org_units
  WHERE unit_id = 10

  UNION ALL

  SELECT
    child.unit_id,
    child.parent_unit_id,
    child.name,
    h.depth + 1 AS depth,
    h.path || child.unit_id || '/' AS path
  FROM org_units child
  JOIN hierarchy h
    ON child.parent_unit_id = h.unit_id
  WHERE position('/' || child.unit_id || '/' IN h.path) = 0
)
SELECT unit_id, parent_unit_id, name, depth
FROM hierarchy
ORDER BY depth, unit_id;

Да, это добавляет вычисления. Но в сравнении с риском “сломать” запрос на больших графах — лучше перебор в безопасности, чем недобор.

Подводный камень №2: UNION vs UNION ALL

Для рекурсивных CTE критично понимать разницу:

• UNION делает устранение дублей (обычно через сортировку/хеширование);
• UNION ALL просто объединяет результаты, что быстрее, но может породить дубликаты и усугубить проблему циклов.

Чаще всего используют UNION ALL и отдельно контролируют повторные посещения (как в примере с path). Если вы используете UNION “на всякий случай”, производительность может упасть, а логика станет менее прозрачной.

Контроль глубины: когда нужен “стоп-фактор”

Иногда данных гарантированно без циклов, но глубина может быть большой (например, граф зависимостей). Тогда стоит ограничить глубину, чтобы защититься от плохих данных и неожиданных сценариев.

WITH RECURSIVE
hierarchy AS (
  SELECT
    unit_id,
    parent_unit_id,
    name,
    0 AS depth
  FROM org_units
  WHERE unit_id = 10

  UNION ALL

  SELECT
    child.unit_id,
    child.parent_unit_id,
    child.name,
    h.depth + 1 AS depth
  FROM org_units child
  JOIN hierarchy h
    ON child.parent_unit_id = h.unit_id
  WHERE h.depth < 20
)
SELECT * FROM hierarchy;

Это не универсальное решение, но как защитный механизм — полезно. Главное: разумный лимит должен быть обоснован (знанием домена, статистикой, SLA).

Рекурсивные CTE для “аналитики”: уровни, агрегаты и семантика

Уровень (depth) — это не только число “для красоты”

Часто depth используют дальше:

• для ранжирования узлов по близости к корню;
• для “взвешенных” показателей;
• для фильтра по уровню (например, “покажи только до 3 уровня”);
• для построения матрицы “корень → потомок” с расстоянием.

Агрегации поверх рекурсивной выборки

Например, у нас есть events(unit_id, event_time, metric) — события привязаны к подразделениям. Хотим посчитать сумму метрик по всем потомкам заданного узла.

WITH RECURSIVE
subtree AS (
  SELECT
    unit_id,
    0 AS depth
  FROM org_units
  WHERE unit_id = 10

  UNION ALL

  SELECT
    child.unit_id,
    s.depth + 1 AS depth
  FROM org_units child
  JOIN subtree s
    ON child.parent_unit_id = s.unit_id
)
SELECT
  SUM(e.metric) AS subtree_metric_sum,
  COUNT(*) AS events_count
FROM events e
JOIN subtree s
  ON e.unit_id = s.unit_id
WHERE e.event_time >= TIMESTAMP '2026-06-01'
  AND e.event_time <  TIMESTAMP '2026-07-01';

Заметьте: рекурсивный CTE строит набор “какие unit_id входят”, а финальная агрегация — отдельной логикой. Это делает запрос понятным и поддерживаемым.

Читаемость рекурсивного SQL: как не утонуть в сложностях

Документируйте “контракты” рекурсивной части

В рекурсивном запросе есть два критичных места:

1. anchor — стартовый набор;
2. recursive query — правило расширения.

Почти всегда проблема поддержки возникает при изменении anchor или join-условия в recursive части. Поэтому хорошо, когда код буквально отвечает на вопросы:

• “Откуда стартуем?”
• “Как ищем потомков/предков?”
• “Как ограничиваем глубину / предотвращаем циклы?”

Комментарии к этим блокам — не роскошь.

Вынесите сложные условия в именованные поля

Например, если есть сложный критерий “потомок считается релевантным, если …”, лучше посчитать это поле в отдельном месте, чем засунуть логику прямо в join или where.

Не смешивайте рекурсивное вычисление и итоговую бизнес-логику

Иногда хочется “сразу посчитать метрики” внутри рекурсивного CTE. Делать это можно, но поддерживаемость падает: вы переплетаете обход и математику.

Лучше: сначала построить структуру (набор узлов + depth), затем уже считать метрики поверх.

Производительность: как думать о планах выполнения и объёме графов

Почему рекурсивные запросы иногда становятся узким местом