Множественные CTE и их комбинирование

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

Синтаксис множественных CTE

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

WITH 
cte1 AS (
    SELECT ...
),
cte2 AS (
    SELECT ...
),
cte3 AS (
    SELECT ...
)
SELECT * FROM cte3;

Важная особенность: каждый последующий CTE может ссылаться на предыдущие, но не наоборот. Это создаёт направленный граф зависимостей.

Практический пример: RFM-анализ

Рассмотрим построение RFM-сегментации клиентов (Recency, Frequency, Monetary). Задача требует трёх независимых расчётов для каждого клиента:

WITH customer_metrics AS (
    SELECT 
        customer_id,
        MAX(order_date) AS last_order_date,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
recency_scores AS (
    SELECT 
        customer_id,
        CURRENT_DATE - last_order_date AS days_since_last_order,
        NTILE(5) OVER (ORDER BY last_order_date DESC) AS r_score
    FROM customer_metrics
),
frequency_scores AS (
    SELECT 
        customer_id,
        order_count,
        NTILE(5) OVER (ORDER BY order_count DESC) AS f_score
    FROM customer_metrics
),
monetary_scores AS (
    SELECT 
        customer_id,
        total_spent,
        NTILE(5) OVER (ORDER BY total_spent DESC) AS m_score
    FROM customer_metrics
)
SELECT 
    cm.customer_id,
    r.r_score,
    f.f_score,
    m.m_score,
    (r.r_score + f.f_score + m.m_score) AS rfm_total
FROM customer_metrics cm
INNER JOIN recency_scores r ON cm.customer_id = r.customer_id
INNER JOIN frequency_scores f ON cm.customer_id = f.customer_id
INNER JOIN monetary_scores m ON cm.customer_id = m.customer_id
ORDER BY rfm_total DESC;

Структура запроса: 1. customer_metrics — базовые агрегаты для каждого клиента 2. recency_scores, frequency_scores, monetary_scores — независимые расчёты оценок, все используют customer_metrics 3. Финальный запрос объединяет все оценки

Переиспользование результатов

Ключевое преимущество множественных CTE — возможность ссылаться на один CTE из нескольких других. В примере выше customer_metrics используется трижды, но вычисляется только один раз. Это делает запрос эффективнее, чем три отдельных подзапроса с дублированием логики.

Пример переиспользования для когортного анализа:

WITH user_first_action AS (
    SELECT 
        user_id,
        MIN(event_date) AS cohort_date
    FROM events
    GROUP BY user_id
),
cohort_size AS (
    SELECT 
        cohort_date,
        COUNT(DISTINCT user_id) AS cohort_users
    FROM user_first_action
    GROUP BY cohort_date
),
retention_data AS (
    SELECT 
        ufa.cohort_date,
        e.event_date,
        COUNT(DISTINCT e.user_id) AS active_users
    FROM events e
    INNER JOIN user_first_action ufa ON e.user_id = ufa.user_id
    GROUP BY ufa.cohort_date, e.event_date
)
SELECT 
    rd.cohort_date,
    rd.event_date,
    rd.active_users * 100.0 / cs.cohort_users AS retention_rate
FROM retention_data rd
INNER JOIN cohort_size cs ON rd.cohort_date = cs.cohort_date
ORDER BY rd.cohort_date, rd.event_date;

Здесь user_first_action используется и в cohort_size, и в retention_data, обеспечивая консистентность определения когорты.

Модульность запросов

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

Это особенно ценно в командной работе: коллега может понять назначение каждого CTE по его имени, не разбирая весь запрос целиком.

Ключевой вывод: Используйте множественные CTE для декомпозиции сложного анализа на независимые метрики. Давайте CTE описательные имена (user_metrics, monthly_revenue), отражающие их содержание — это документация прямо в коде запроса.