CTE в SQL: Полное руководство с примерами

Как создать SQL CTE

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

WITH cte_name (column1, column2, ...)
AS (
    -- Запрос, который определяет CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Основной запрос
SELECT ...
FROM cte_name;

Где:

• WITH: Инициирует определение CTE, указывая, что следующее имя представляет собой временный набор результатов.

• cte_name: Название назначается CTE для ссылки на него в основном запросе.

• Необязательный список столбцов (column1, column2, …): Указывает имена столбцов для набора результатов CTE. Это полезно, когда имена столбцов нужно скорректировать.

• Запрос, определяющий CTE: Внутренний запрос, который выбирает данные и формирует временный набор данных.

• Основной запрос: Ссылается на CTE по его имени, используя его как таблицу.

Давайте посмотрим на следующий пример создания CTE с использованием плоского подхода. Предположим, у нас есть таблица Employees, и мы хотим создать CTE, который выбирает сотрудников, зарабатывающих зарплату выше $50,000.

Шаг 1: Напишите базовый запрос

Начнем с написания базового запроса SELECT:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Шаг 2: Оберните запрос, используя ключевое слово WITH, чтобы создать CTE

Используйте ключевое слово WITH, чтобы дать CTE имя.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Шаг 3: Используйте CTE в основном запросе

Наконец, обращайтесь к CTE в операторе SELECT, вызывая имя CTE, определенное выше.

-- Определение общего выражения таблицы (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- Используйте CTE для выбора высокооплачиваемых сотрудников
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Для обобщения вышеперечисленных шагов мы использовали ключевое слово WITH, чтобы определить CTE с именем HighEarningEmployees. Внутренний запрос использовался для создания временного набора данных. Основной запрос ссылается на HighEarningEmployees, чтобы отобразить указанные столбцы EmployeeID, FirstName и LastName.

Почему CTE в SQL полезны

Из вышеприведенного примера вы можете задаться вопросом, почему мы используем CTE, когда даже простые запросы дают те же результаты. Вот несколько причин:

Упростить сложные запросы

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

Предположим, у нас есть три таблицы: Orders, Customers и Products. Мы хотим найти общий доход, сгенерированный каждым клиентом, совершившим покупку в 2024 году. Когда мы пишем запрос, не используя CTE, он выглядит запутанным и трудным для чтения и понимания.

-- Выбрать имена клиентов и общий доход от их заказов
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- Присоединение для получения таблицы клиентов и продуктов
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

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

-- Определение CTE
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
--Основной запрос
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

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

CTE помогает избежать дублирования, позволяя использовать один и тот же набор данных в различных частях запроса. Если несколько вычислений или операций основаны на одном и том же наборе данных, вы можете определить его один раз в CTE и обращаться к нему по мере необходимости.

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

-- Определите CTE для расчета общих и средних продаж по каждой категории
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Выберите категорию, общие продажи и средние продажи из CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Другие приложения

Помимо упрощения запросов и повторного использования кода, CTE имеют и другие применения. Я не могу подробно рассмотреть все возможные способы использования CTE. Наш курс Манипулирование данными в SQL – отличный вариант, если вы хотите продолжать практиковаться. Однако я документирую некоторые из основных причин здесь:

• Организация запросов и удобочитаемость: Общие табличные выражения повышают удобочитаемость кода SQL, разделяя запросы на логические последовательные шаги. Каждый шаг в процессе запроса может быть представлен своим собственным общим табличным выражением, что делает весь запрос более легким для понимания.
• Траверсирование иерархических данных: Общие табличные выражения могут помочь в навигации по иерархическим отношениям, таким как организационные структуры, родительские-дочерние отношения или любая модель данных, которая включает вложенные уровни. Рекурсивные общие табличные выражения полезны для запросов иерархических данных, потому что они позволяют вам итеративно просматривать уровни.
• Многоуровневые агрегации: Общие таблицы выражений могут помочь выполнять агрегации на нескольких уровнях, такие как расчет объемов продаж на разных уровнях детализации (например, по месяцам, кварталам и годам). Использование общих таблиц выражений для разделения этих этапов агрегации гарантирует, что каждый уровень рассчитывается независимо и логически.
• Объединение данных из нескольких таблиц: Несколько общих таблиц выражений могут быть использованы для объединения данных из разных таблиц, что делает конечный этап объединения более структурированным. Такой подход упрощает сложные объединения и обеспечивает логическую организацию исходных данных для улучшения читаемости.

Продвинутые техники SQL с общими таблицами выражений

Общие таблицы выражений поддерживают продвинутые техники SQL, что делает их универсальными и полезными для различных случаев использования. Ниже приведены некоторые из продвинутых применений общих таблиц выражений.

Несколько общих таблиц выражений в одном запросе

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

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

WITH ProductSales AS (
    -- Шаг 1: Рассчитать общие продажи для каждого продукта
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- Шаг 2: Рассчитать средние общие продажи по всем продуктам
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- Шаг 3: Отфильтровать продукты с общими продажами выше среднего
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- Шаг 4: Ранжировать продукты с высокими продажами
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

В приведенном выше примере;

• Первое общее выражение (ProductSales) рассчитывает общие продажи для каждого продукта.

• Второй CTE (AverageSales) вычисляет средние общие продажи по всем продуктам.

• Третий CTE (HighSalesProducts) фильтрует продукты, общие продажи которых превышают среднее значение.

• Итоговый запрос ранжирует эти продукты на основе их общих продаж.

CTE в операторах UPDATE, DELETE и MERGE

При включении в операции UPDATE, DELETE и MERGE CTE могут упростить задачи манипулирования данными, особенно при работе с комплексными фильтрами или иерархическими данными.

Использование CTE с оператором UPDATE

Предположим, у нас есть таблица Employees с колонкой EmployeeSalary. Мы хотим увеличить зарплату на 10% всем сотрудникам, которые работают в компании более 5 лет.

-- Определение CTE для поиска сотрудников, проработавших более 5 лет
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- Обновление зарплат на 10% для долгосрочных сотрудников, идентифицированных в CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

CTE LongTermEmployees идентифицирует сотрудников, которые работали более пяти лет. Оператор UPDATE использует это CTE для выборочного увеличения зарплат.

Использование CTE с оператором DELETE

Теперь предположим, что у нас есть таблица с именем Products и мы хотим удалить все продукты, которые не были проданы за последние 2 года. Мы можем использовать CTE для фильтрации продуктов:

-- Определение CTE для идентификации продуктов, не проданных за последние 2 года
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Использование DATEADD для поиска продуктов с LastSoldDate более 2 лет назад
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- Удаление продуктов, идентифицированных как старые, из основной таблицы
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

CTE OldProducts идентифицирует продукты, которые не были проданы за последние два года, а затем оператор DELETE использует эту CTE для удаления этих продуктов.

Использование CTE с оператором MERGE

Оператор MERGE в SQL позволяет выполнить условное обновление, вставку или удаление в целевой таблице на основе данных из исходной таблицы. В следующем примере CTE MergedInventory объединяет новые и существующие данные инвентаря. Затем оператор MERGE обновляет количество существующих продуктов или вставляет новые продукты на основе данных из CTE.

-- CTE для объединения новых и существующих данных о запасах
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- Используйте LEFT JOIN, чтобы включить все новые данные, даже если их нет в текущем запасе
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- Объедините подготовленные данные в таблицу Inventory
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- Обновите существующие продукты с новыми количествами
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- Вставьте новые продукты, если их нет в инвентаре
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Рекурсивные общие таблицы выражений (CTE)

Рекурсивные CTE помогают выполнять сложные и повторяющиеся операции.

Введение в рекурсивные CTE

Рекурсивные CTE – это особый тип CTE, который ссылается на себя в своем определении, позволяя запросу выполнять повторяющиеся операции. Это делает их идеальными для работы с иерархическими или древовидными данными, такими как организационные схемы, структуры каталогов или сборочные единицы продукции. Рекурсивное CTE итеративно обрабатывает данные, возвращая результаты шаг за шагом, пока не будет достигнуто условие завершения.

Якорный член и рекурсивные члены

Рекурсивное CTE состоит из двух основных частей:

• Якорный член: Часть, которая определяет базовый запрос, запускающий рекурсию.
• Рекурсивный член: Часть, которая ссылается на сам CTE, позволяя ему выполнять “рекурсивные” операции.

Предположим, у нас есть таблица Employees, где каждая строка содержит EmployeeID, EmployeeName и ManagerID. Если мы хотим найти всех прямых и косвенных подчиненных для определенного менеджера, мы начинаем с якорного члена, который идентифицирует менеджера верхнего уровня. Якорный член начинается с сотрудника с EmployeeID = 1.

Рекурсивный член находит сотрудников, у которых ManagerID соответствует EmployeeID из предыдущей итерации. Каждая итерация извлекает следующий уровень иерархии.

WITH EmployeeHierarchy AS (
    -- Якорный член: выбрать менеджера верхнего уровня
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Начиная с менеджера верхнего уровня
    UNION ALL
    -- Рекурсивный член: находит сотрудников, которые отчитываются перед текущими менеджерами
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Потенциальные проблемы или ограничения CTE в SQL

Понимание особенностей и ограничений CTE важно для написания логичных и читаемых запросов. Давайте рассмотрим некоторые ограничения и потенциальные проблемы при использовании CTE в различных базах данных.

Ограничения SQL Server и Azure

Существуют некоторые ограничения, специфичные для среды, для SQL CTE при работе с SQL Server или Azure Synapse Analytics. Они включают в себя следующее:

• SQL Server: Максимальный уровень рекурсии по умолчанию для рекурсивных CTE составляет 100, который можно изменить, используя подсказку OPTION (MAXRECURSION). Если это ограничение превышается без корректировки, возникает ошибка. CTE не могут быть вложены непосредственно друг в друга или определены внутри другого CTE.

• Azure Synapse Analytics: CTE имеют ограниченную поддержку для определенных операций SQL, таких как INSERT, UPDATE, DELETE и MERGE. Кроме того, рекурсивные CTE не поддерживаются в облачных средах Azure Synapse Analytics, что ограничивает возможность выполнения определенных иерархических операций с данными.

Если вы работаете с SQL Server, помните, что у DataCamp есть много отличных ресурсов, которые могут помочь. Для начала я рекомендую пройти курс DataCamp Introduction to SQL Server, чтобы освоить основы SQL Server для анализа данных. Вы можете попробовать нашу карьерную программу SQL Server Developer, которая охватывает все, начиная от транзакций и обработки ошибок и заканчивая анализом временных рядов. Наш курс Hierarchical and Recursive Queries in SQL Server сразу переходит к основам написания расширенных запросов в SQL Server, включая методы, связанные с общими табличными выражениями.

Другие потенциальные проблемы

Хотя CTE полезны для упрощения сложных запросов, есть некоторые распространенные ошибки, о которых вам следует знать. Они включают в себя следующее:

• Бесконечные циклы в рекурсивных CTE: Если условие завершения для рекурсивного CTE не выполняется, это может привести к бесконечному циклу, вызывая бесконечное выполнение запроса. Чтобы избежать бесконечного выполнения рекурсивного CTE, используйте подсказку OPTION (MAXRECURSION N), чтобы ограничить максимальное количество рекурсивных итераций, где N – указанный предел.

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

Когда использовать CTE по сравнению с другими техниками

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

Заключение

Овладение CTE требует практики, как и любое другое: Я рекомендую попробовать карьерный трек Ассоциированный аналитик данных SQL от DataCamp, чтобы стать опытным аналитиком данных. Курс Отчетность в SQL также поможет вам стать опытным в создании сложных отчетов и панелей для эффективного представления данных. Наконец, вам следует получить Сертификат SQL Associate, чтобы продемонстрировать свое мастерство в использовании SQL для решения бизнес-проблем и выделиться среди других специалистов.