Урок 6.4: Обобщённые табличные выражения (CTE)

Обобщённые табличные выражения, или CTE, являются одной из самых мощных и недостаточно используемых функций SQL. Они позволяют вам определять временные именованные наборы результатов, которые можно использовать в более крупном запросе. В этом уроке мы исследуем, как CTE может сделать ваш SQL код более читаемым, легко поддерживаемым и проще для отладки.

Что такое CTE?

Обобщённое табличное выражение (CTE) — это временный набор результатов, определённый в начале запроса с помощью предложения WITH. Думайте о нём как об именованном подзапросе, который можно использовать несколько раз в одном запросе.

Ключевые преимущества CTE:

Читаемость: Именованные наборы результатов делают запросы понятнее
Переиспользуемость: Ссылайтесь на один и тот же CTE несколько раз без переопределения
Модульность: Разбейте сложные запросы на логичные, управляемые части
Поддерживаемость: Изменения логики нужно делать только в одном месте
Отладка: Тестируйте каждый CTE независимо перед объединением

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

Общий синтаксис CTE:

WITH имя_cte AS (
    SELECT ...
)
SELECT * FROM имя_cte;

Компоненты:

WITH: Ключевое слово, вводящее CTE
имя_cte: Имя, которое вы даёте временному набору результатов
AS: Ключевое слово, вводящее определение запроса
(SELECT ...): Запрос, который определяет CTE
Основной запрос может затем ссылаться на CTE по имени

Ваше первое CTE

Давайте начнём с простого примера, который вычисляет расходы клиента:

WITH расходы_клиента AS (
    SELECT
        customer_id,
        SUM(amount) AS всего_потрачено,
        COUNT(*) AS количество_платежей,
        AVG(amount) AS среднее_платежа
    FROM
        payment
    GROUP BY
        customer_id
)
SELECT
    customer_id,
    всего_потрачено,
    количество_платежей,
    среднее_платежа
FROM
    расходы_клиента
WHERE
    всего_потрачено > 100
ORDER BY
    всего_потрачено DESC;

Этот CTE:

Определяет именованный набор результатов расходы_клиента
Вычисляет метрики расходов для каждого клиента
Ссылается на этот CTE в основном запросе для фильтрации высокорасходующих клиентов

Преимущество здесь в ясности — намерение очевидно: мы работаем с данными расходов клиентов.

CTE vs Подзапросы

Сравним одну логику, используя традиционный подход с подзапросом:

Использование подзапроса:

SELECT
    customer_id,
    всего_потрачено,
    количество_платежей,
    среднее_платежа
FROM (
    SELECT
        customer_id,
        SUM(amount) AS всего_потрачено,
        COUNT(*) AS количество_платежей,
        AVG(amount) AS среднее_платежа
    FROM
        payment
    GROUP BY
        customer_id
) AS данные_расходов
WHERE
    всего_потрачено > 100
ORDER BY
    всего_потрачено DESC;

Использование CTE:

WITH расходы_клиента AS (
    SELECT
        customer_id,
        SUM(amount) AS всего_потрачено,
        COUNT(*) AS количество_платежей,
        AVG(amount) AS среднее_платежа
    FROM
        payment
    GROUP BY
        customer_id
)
SELECT
    customer_id,
    всего_потрачено,
    количество_платежей,
    среднее_платежа
FROM
    расходы_клиента
WHERE
    всего_потрачено > 100
ORDER BY
    всего_потрачено DESC;

Ключевые различия:

CTE определяется в начале, делая структуру запроса сразу же ясной
CTE имеет значимое имя (расходы_клиента), а не просто безымянный подзапрос
Намерение основного запроса видно до погружения в трансформации данных
Если вам нужно ссылаться на этот набор результатов несколько раз, вы определяете его только один раз с CTE

Несколько CTE в одном запросе

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

WITH расходы_клиента AS (
    SELECT
        customer_id,
        SUM(amount) AS всего_потрачено
    FROM
        payment
    GROUP BY
        customer_id
),
крупные_расходы AS (
    SELECT
        customer_id,
        всего_потрачено
    FROM
        расходы_клиента
    WHERE
        всего_потрачено > 150
),
детали_клиента AS (
    SELECT
        кр.customer_id,
        кр.всего_потрачено,
        c.first_name,
        c.last_name,
        c.email
    FROM
        крупные_расходы кр
    JOIN
        customer c ON кр.customer_id = c.customer_id
)
SELECT
    customer_id,
    CONCAT(first_name, ' ', last_name) AS имя_клиента,
    email,
    всего_потрачено
FROM
    детали_клиента
ORDER BY
    всего_потрачено DESC;

В этом запросе:

расходы_клиента вычисляет всего потрачено на клиента
крупные_расходы фильтрует клиентов с общими расходами > 150
детали_клиента объединяет крупные расходы с информацией о клиенте
Основной запрос выбирает и форматирует окончательные результаты

Эта структура делает логический поток ясным и лёгким для следования.

Переиспользуемость CTE

Мощный аспект CTE — ссылаться на себя несколько раз:

WITH продажи_по_месяцам AS (
    SELECT
        DATE_TRUNC('month', payment_date) AS месяц,
        SUM(amount) AS ежемесячный_итог
    FROM
        payment
    GROUP BY
        DATE_TRUNC('month', payment_date)
)
SELECT
    m1.месяц AS текущий_месяц,
    m1.ежемесячный_итог AS текущие_продажи,
    m2.ежемесячный_итог AS продажи_предыдущего_месяца,
    ROUND(((m1.ежемесячный_итог - m2.ежемесячный_итог) / m2.ежемесячный_итог * 100), 2) AS процент_изменения
FROM
    продажи_по_месяцам m1
LEFT JOIN
    продажи_по_месяцам m2 ON m1.месяц = m2.месяц + INTERVAL '1 month'
WHERE
    m1.месяц IS NOT NULL
ORDER BY
    m1.месяц;

Здесь мы ссылаемся на продажи_по_месяцам дважды — один раз как m1 и один раз как m2. Это потребовало бы двух отдельных подзапросов, если бы мы не использовали CTE.

CTE с оконными функциями

CTE работают замечательно с оконными функциями:

WITH ранжированные_прокаты AS (
    SELECT
        customer_id,
        rental_date,
        return_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY rental_date DESC
        ) AS ранг_проката
    FROM
        rental
),
последний_прокат AS (
    SELECT
        customer_id,
        rental_date,
        return_date
    FROM
        ранжированные_прокаты
    WHERE
        ранг_проката = 1
)
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS имя_клиента,
    пп.rental_date AS дата_последнего_проката,
    DATEDIFF(CURDATE(), пп.rental_date) AS дней_с_проката
FROM
    customer c
LEFT JOIN
    последний_прокат пп ON c.customer_id = пп.customer_id
ORDER BY
    дней_с_проката DESC
LIMIT 20;

Этот запрос:

Использует ROW_NUMBER() для выявления самого последнего проката каждого клиента
Фильтрует, чтобы получить только самый последний прокат на клиента
Объединяет с таблицей клиента, чтобы показать имена клиентов и рассчитать дни с проката

Модульная структура облегчает понимание и изменение.

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

CTE отличны для сложных аналитических запросов, таких как анализ когорты:

WITH первый_прокат_клиента AS (
    SELECT
        customer_id,
        MIN(rental_date) AS дата_первого_проката,
        DATE_TRUNC('month', MIN(rental_date)) AS месяц_когорты
    FROM
        rental
    GROUP BY
        customer_id
),
история_проката_клиента AS (
    SELECT
        ппк.customer_id,
        ппк.месяц_когорты,
        DATE_TRUNC('month', r.rental_date) AS месяц_проката,
        COUNT(*) AS прокатов_за_месяц
    FROM
        первый_прокат_клиента ппк
    JOIN
        rental r ON ппк.customer_id = r.customer_id
    GROUP BY
        ппк.customer_id,
        ппк.месяц_когорты,
        DATE_TRUNC('month', r.rental_date)
)
SELECT
    месяц_когорты,
    месяц_проката,
    COUNT(DISTINCT customer_id) AS клиенты,
    SUM(прокатов_за_месяц) AS всего_прокатов
FROM
    история_проката_клиента
GROUP BY
    месяц_когорты,
    месяц_проката
ORDER BY
    месяц_когорты,
    месяц_проката;

Этот сложный анализ становится управляемым благодаря CTE:

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

Итоговая таблица преимуществ

Аспект	CTE	Подзапрос
Читаемость	Высокая читаемость с именованными наборами	Может быть трудной(вложенные структуры)
Переиспользуемость	Легко ссылаться несколько раз	Нужно переопределять каждый раз
Отладка	Можно тестировать каждый CTE независимо	Сложно изолировать спецлогику
Организация	Логическая структура, сверху вниз	Линейная но иногда запутанная
Производительность	Одинаковая или лучше (зависит от оптимизатора)	Может быть менее эффективна при глубокой вложенности

Ключевые выводы

CTE — это временные именованные наборы результатов, определяемые с предложением WITH
Читаемость: Именованные CTE делают запросы самодокументируемыми
Несколько CTE: Цепляйте CTE вместе, каждый опирается на предыдущий
Переиспользуемость: Ссылайтесь на один и тот же CTE несколько раз без переопределения
Нет штрафа за производительность: CTE не создают промежуточное хранилище; это инструменты оптимизации запроса
Работает со всем: CTE могут включать объединения, агрегации, оконные функции и многое другое
Модульность: Разбейте сложные запросы на логичные кусочки, которые легче понять и поддерживать

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

В следующем уроке мы исследуем рекурсивные CTE — мощную функцию для работы с иерархическими данными.

Продолжить практику

Войти

Регистрация