SQL: Реляционные базы данных on Telegram: “📥 Что такое CTE (WITH) в SQL? CTE (Common Table…”

📥 Что такое CTE (WITH) в SQL?

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

Зачем нужен CTE?
➖ Упрощает структуру запроса. Вместо вложенных подзапросов (особенно повторяющихся), можно дать промежуточным результатам имена и вынести их в WITH.
➖ Повышает читаемость. Каждый WITH-блок как отдельный логический шаг, как в пайплайне обработки данных.
➖ Упрощает отладку. Можно легко запускать отдельные WITH-блоки как обычные SELECT-запросы и проверять результаты.
➖ Поддерживает рекурсию. С помощью WITH RECURSIVE можно обходить иерархии, строить деревья и графы.

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

WITH имя_cte (опциональные_поля) AS (
    SQL-запрос
)
SELECT ...
FROM имя_cte
...

WITH объявляет формирование CTE. Можно создавать несколько CTE за один раз, разделяя их запятыми:

WITH cte1 AS (...),
     cte2 AS (...)
SELECT ...
FROM cte1
JOIN cte2 ...

🪣 Давайте разберем на примере. Задача:
Найти всех пользователей, которые заходили в систему за последние 30 дней, и посчитать, сколько заказов сделал каждый из них.

💳 Модель данных:
➖ Таблица пользователи — users (id, name, last_login)
Атрибуты:
➖users.id — уникальный идентификатор пользователя, первичный ключ.
➖users.name — имя пользователя, текстовое поле.
➖users.last_login — дата (или дата и время) последнего входа в систему, используется для фильтрации активных пользователей.

🪙Полный состав таблицы users по данным:

id   name   last_login
101  Иван   2025-07-20 
105  Ольга  2025-07-15 
109  Петр   2025-06-10

➖ Таблица заказы — orders (id, user_id, created_at).
Атрибуты:
➖orders.id — уникальный идентификатор заказа, первичный ключ.
➖orders.user_id — внешний ключ на users .id, связывает заказ с пользователем.
➖orders.created_at — дата (или дата и время) создания заказа.

🪙 Полный состав таблицы orders по данным:

id   user_id  created_at
201  101      2025-07-21 
203  101      2025-07-20 
206  101      2025-07-22 
202  105      2025-07-19 
204  105      2025-07-22 
205  105      2025-07-23 
207  105      2025-07-24 
208  105      2025-07-25 
209  105      2025-07-26 
210  105      2025-07-27

💳 Пример запроса с CTE для решения задачи:

WITH active_users AS (
    SELECT id AS user_id
    FROM users
    WHERE last_login > CURRENT_DATE - INTERVAL '30 days'
)
SELECT 
    u.user_id,
    COUNT(o.id) AS orders_count
FROM active_users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id;

💳 Разберем запрос:

WITH active_users AS (...)

Создаём временный набор данных с именем active_users, в который попадут только те пользователи, чей last_login был в последние 30 дней.

CURRENT_DATE - INTERVAL '30 days'

Это дата 30 дней назад от текущей.

💳Пример вывода отдельно по CTE active_users:

user_id
101
105
109

💳 Разберем основной запрос:

SELECT 
    u.user_id,
    COUNT(o.id) AS orders_count
FROM active_users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id;

➖ Присваиваем псевдоним u для CTE active_users
➖ Соединяем с таблицей orders по user_id
➖ Считаем количество заказов через COUNT(o. id)
➖ Группируем по пользователю, чтобы посчитать заказы отдельно для каждого.

💳 Итоговый результат:

user_id     orders_count
101         3
105         7

Пользователь 109, у которого нет заказов, не попадёт в результат — INNER JOIN исключает строки без соответствующих заказов. Чтобы включить таких пользователей и показать orders_count = 0, используйте LEFT JOIN

Когда точно стоит использовать CTE:
➖ У вас многоуровневая логика запроса.
➖ Один и тот же подзапрос используется несколько раз.
➖ Вы готовите пайплайн данных — шаг за шагом.
➖ Нужно рекурсивно обрабатывать данные (например, иерархии категорий).

#CTE #SQL

📱 Подписаться на канал
💻 Курс автора по SQL DDL
🌎 Мой ИТ-стартап