SQL, Анализ данных

Способы использования CTE

Время прочтения: 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 разработчика.

Советуем почитать