WITH (CTE): разбиение запроса на логические блоки

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

Синтаксис WITH

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

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

После объявления CTE можно использовать его имя как обычную таблицу в основном запросе. Важно: CTE существует только в рамках одного запроса и автоматически удаляется после его выполнения.

Практический пример: анализ активных клиентов

Представим задачу: найти клиентов, которые сделали заказы в последние 30 дней, и рассчитать их средний чек. Без CTE запрос превращается в трудночитаемую конструкцию с подзапросами:

SELECT 
    customer_id,
    AVG(amount) AS avg_check
FROM orders
WHERE customer_id IN (
    SELECT DISTINCT customer_id 
    FROM orders 
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
GROUP BY customer_id;

С использованием CTE логика становится прозрачной:

WITH active_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT 
    o.customer_id,
    AVG(o.amount) AS avg_check
FROM orders o
INNER JOIN active_customers ac ON o.customer_id = ac.customer_id
GROUP BY o.customer_id;

Здесь active_customers — это промежуточный результат, который явно описывает первый шаг: определение активных клиентов. Основной запрос работает с этим набором, не отвлекаясь на детали фильтрации.

Цепочки преобразований

CTE особенно полезны при многоэтапных преобразованиях данных. Например, для расчёта доли повторных покупок:

WITH first_orders AS (
    SELECT 
        customer_id,
        MIN(order_date) AS first_order_date
    FROM orders
    GROUP BY customer_id
),
repeat_customers AS (
    SELECT 
        o.customer_id
    FROM orders o
    INNER JOIN first_orders fo ON o.customer_id = fo.customer_id
    WHERE o.order_date > fo.first_order_date
    GROUP BY o.customer_id
)
SELECT 
    COUNT(DISTINCT rc.customer_id) * 100.0 / COUNT(DISTINCT fo.customer_id) AS repeat_rate
FROM first_orders fo
LEFT JOIN repeat_customers rc ON fo.customer_id = rc.customer_id;

Каждый CTE решает одну задачу: 1. first_orders — находит дату первого заказа для каждого клиента 2. repeat_customers — определяет клиентов с повторными покупками 3. Основной запрос — вычисляет итоговую метрику

Преимущества для отладки

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

WITH step1 AS (
    SELECT user_id, SUM(amount) AS total
    FROM transactions
    GROUP BY user_id
)
SELECT * FROM step1 LIMIT 10; -- проверяем первый шаг

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

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