Время прочтения: 3 мин.
Для иллюстрации основных концепций будем использовать тестовые таблицы из демо-базы AdventureWorks2019, созданную Microsoft для представления возможностей SQL Server.
Самый простой вариант использования CTE — это использовать CTE как представление, что повышает читабельность запроса. Давайте рассмотрим вариант использования CTE вместо подзапросов:
WITH Sales_CTE (SalePersonID, TotalSale, SaleYear)
AS
(
SELECT SalePersonID, SUM(TotalDue) AS TotalSale, YEAR(OrderDate) AS SaleYear
FROM Sales.SalesOrderHeader
WHERE SalePersonID IS NOT NULL
GROUP BY SalePersonID, YEAR(OrderDate)
)
,Sales_Quota_CTE (BusinessEntityID, SaleQuota, SaleQuotaYear)
AS
(
SELECT BusinessEntityID, SUM(SaleQuota)AS SaleQuota, YEAR(QuotaDate) AS SaleQuotaYear
FROM Sales.SalesPersonQuotaHistory
GROUP BY BusinessEntityID, YEAR(QuotaDate)
)
SELECT SalePersonID
, SaleYear
, FORMAT(TotalSale,'C','en-us') AS TotalSale
, SaleQuotaYear
, FORMAT (SaleQuota,'C','en-us') AS SaleQuota
, FORMAT (TotalSale -SaleQuota, 'C','en-us') AS Amt_Above_or_Below_Quota
FROM Sales_CTE s
JOIN Sales_Quota_CTE sq ON sq.BusinessEntityID = s.SalePersonID
AND s.SaleYear = sq.SaleQuotaYear
ORDER BY SalePersonID, SaleYear;
Основная цель данного запроса – повысить читабельность, поскольку производительность не повышается в сравнении с подзапросами.
Основная же сфера применения CTE это рекурсивные вычисления и обходы деревьев. Здесь наблюдается прирост как производительности, так и читабельности запроса.
Для примера рекурсивного вычисления рассмотрим задачу генерации английского алфавита. Код для генерации алфавита представлен ниже:
WITH ALPHABET(CODE,LETTER) AS (
SELECT ASCII('A'),char(ASCII('A'))
UNION ALL
SELECT CODE+1,CHAR(CODE+1)
FROM ALPHABET
WHERE CODE+1 <=ASCII('Z')
)
SELECT * FROM ALPHABET
На рисунке ниже представлен результат выполнения данного запроса.
Исходя из результата запроса можно сделать вывод, что результат предыдущего шага в рекурсивном CTE используется для вычисления последующего шага, а на последнем шаге при выполнении условия остановки происходит объединение результирующего набора. Данную схему вычислений можно продемонстрировать так, как показано на следующем рисунке
Стоит отметить, что на вход блок схемы в цикле подается Ri-1 и на основе этого вычисляется Ri.. И только после выполнения условия остановки рекурсии происходит объединение Ri-х в результирующий набор.
Обход по дереву выполняется аналогичным образом. Для примера рассмотрим тестовую таблицу Employee следующего вида:
Как можно увидеть из данных в таблице, присутствует иерархия по уровням управления, и мы можем провести обход данного дерева с помощью представленного ниже запроса:
WITH EmpHierarchy AS (
SELECT *, 0 AS EmpLevel
FROM dbo.MyEmployees
WHERE ManagerID IS NULL
UNION ALL (
SELECT e.*, EmpHierarchy.EmpLevel + 1
FROM dbo.MyEmployees e
INNER JOIN EmpHierarchy
ON e.ManagerID = EmpHierarchy.EmployeeID)
)
SELECT * FROM EmpHierarchy
Результат запроса дает упорядоченный относительно уровней дерева результирующий набор, с которым можно ознакомиться на рисунке.
Как можно увидеть схема итераций аналогична примеру с рекурсией.
Данный код более понятный, компактный м производительный по сравнению с другими приемами обхода иерархий и CTE является лучшей практикой для решения рекурсивных задач.
Таким образом, мы рассмотрели множество способов применения CTE и на практике убедились насколько это мощный и удобный инструмент в арсенале SQL разработчика.