Перейти на PRO

Что такое обобщённое табличное выражение (CTE)?

Тип вопроса: Базы данных Вероятность: 25% Категория: C/C++ Разработчик

Ответ

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

Основные преимущества и случаи использования:

  • Улучшение читаемости: Разбиение сложного запроса на логические блоки.
  • Рекурсивные запросы: CTE может ссылаться на саму себя, что идеально для работы с иерархическими данными (деревьями, графами).
  • Замена представления (VIEW): Когда нужно временное представление для одного сложного запроса.
  • Многократное использование: CTE можно сослаться несколько раз в основном запросе, избегая дублирования подзапросов.

Пример 1: Простой CTE для структурирования запроса

WITH HighValueOrders AS (
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE total_amount > 1000
),
ActiveCustomers AS (
    SELECT customer_id, customer_name
    FROM customers
    WHERE status = 'active'
)
-- Основной запрос, использующий оба CTE
SELECT ac.customer_name, hvo.order_id, hvo.total_amount
FROM ActiveCustomers ac
JOIN HighValueOrders hvo ON ac.customer_id = hvo.customer_id
ORDER BY hvo.total_amount DESC;

Пример 2: Рекурсивный CTE для обхода иерархии (дерева сотрудников)

-- Таблица сотрудников с указанием менеджера
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

-- Рекурсивный CTE для получения всей цепочки подчинённых для менеджера с id = 1
WITH RECURSIVE SubordinateTree AS (
    -- Якорь рекурсии: начальная точка (менеджер)
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE id = 1

    UNION ALL

    -- Рекурсивный член: находим подчинённых предыдущего уровня
    SELECT e.id, e.name, e.manager_id, st.level + 1
    FROM employees e
    INNER JOIN SubordinateTree st ON e.manager_id = st.id
)
SELECT * FROM SubordinateTree ORDER BY level, id;

CTE выполняются до основного запроса и материализуются во временное пространство, что может влиять на производительность. Для очень больших или часто используемых наборов данных иногда предпочтительнее использовать временные таблицы.

Ответ 18+ 🔞

Давай разберём эту штуку, которая в SQL называется CTE, или, по-русски, «обобщённое табличное выражение». Представь, что ты пишешь огромный, пиздец какой сложный запрос, в котором столько вложенных SELECT, что глаза сломаешь. И вот чтобы не сойти с ума, умные дядьки придумали CTE. Это как взять кусок этого запроса, дать ему человеческое имя и сказать: «Вот, смотри, это у нас будет временная табличка ХуйСГоры, работай с ней».

Зачем это вообще нужно, спросишь ты? Ну, во-первых, читать становится в разы проще — не надо глазами прыгать по скобкам, ища, где какой подзапрос кончился. Во-вторых, это ебушки-воробушки для рекурсивных штук — когда одной таблице нужно на саму себя посмотреть и построить какую-нибудь иерархию, типа дерева подчинённых. И в-третьих, если один и тот же кусок логики нужен в запросе несколько раз, не надо его копировать — объявил CTE один раз и юзаешь его, где хочешь. Доверия ебать ноль к тем, кто один и тот же подзапрос на десять экранов копипастит.

Смотри, как это выглядит на практике. Самый простой случай — разбить бардак на части.

-- Объявляем наш первый временный блок. Назовём его, например, КрупныеЗаказы.
WITH HighValueOrders AS (
    SELECT order_id, customer_id, total_amount
    FROM orders
    -- Берём только те, что дороже тысячи. Не мелочимся.
    WHERE total_amount > 1000
),
-- А можно сразу несколько таких блоков объявить. Вот, например, активные клиенты.
ActiveCustomers AS (
    SELECT customer_id, customer_name
    FROM customers
    WHERE status = 'active'
)
-- А теперь основной запрос, где мы эти блоки как обычные таблицы используем.
SELECT ac.customer_name, hvo.order_id, hvo.total_amount
FROM ActiveCustomers ac
JOIN HighValueOrders hvo ON ac.customer_id = hvo.customer_id
ORDER BY hvo.total_amount DESC;

Видишь? Всё аккуратно, по полочкам. Сначала подготовили два набора данных, а потом их склеили. Не то что какой-нибудь распиздяй, который всё в одну кучу валит.

А теперь ни хуя себе пример — рекурсия. Это когда CTE умеет сам на себя ссылаться. Классика — дерево сотрудников: у каждого есть айдишник и айдишник его начальника.

-- Допустим, есть таблица сотрудников. Менеджер ссылается сам на себя.
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

-- А вот и магия. Ключевое слово RECURSIVE.
WITH RECURSIVE SubordinateTree AS (
    -- Это "якорь" рекурсии. С него всё начинается. Берём, например, главного начальника с id = 1.
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE id = 1

    UNION ALL

    -- А это рекурсивная часть. Берём сотрудников, чей начальник — это кто-то из уже найденных на предыдущем шаге.
    SELECT e.id, e.name, e.manager_id, st.level + 1
    FROM employees e
    INNER JOIN SubordinateTree st ON e.manager_id = st.id
    -- И так будет происходить, пока находятся новые подчинённые.
)
-- Всё, теперь в SubordinateTree лежит вся цепочка: начальник, его подчинённые, подчиненные подчинённых и т.д.
SELECT * FROM SubordinateTree ORDER BY level, id;

Вот это и есть сила. Запрос сам себя джойнит, пока не пройдёт по всем веткам дерева. Ёперный театр, да?

Но, чувак, есть и подводные камни. CTE — штука временная, живёт только пока запрос выполняется. Для одноразовых сложных операций — идеально. Но если у тебя овердохуища данных и этот набор нужно юзать в куче мест или много раз, иногда лучше создать нормальную временную таблицу. А то CTE может каждый раз вычисляться заново, и производительность накроется медным тазом. Так что э, бошка, думай, где что применять.

© ХакСобесов, 2026
support@hacksobesov.com