Что такое cte в sql

Обобщенное табличное выражение (CTE)

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

Синтаксис

WITH common_table_expression [, . ] common_table_expression view_identifier [ ( column_identifier [, . ] ) ] [ AS ] ( query ) 

Параметры

• view_identifier Идентификатор, по которому можно ссылаться common_table_expression на
• column_identifier Необязательный идентификатор, по которому можно ссылаться на столбец common_table_expression объекта . Если column_identifier указано значение s, их число должно соответствовать количеству столбцов, возвращаемых query объектом . Если имена не указаны, имена столбцов являются производными от query .
• Запроса Запрос, создающий результирующий набор.

Примеры

-- CTE with multiple column aliases > WITH t(x, y) AS (SELECT 1, 2) SELECT * FROM t WHERE x = 1 AND y = 2; 1 2 -- CTE in CTE definition > WITH t AS ( WITH t2 AS (SELECT 1) SELECT * FROM t2) SELECT * FROM t; 1 -- CTE in subquery > SELECT max(c) FROM ( WITH t(c) AS (SELECT 1) SELECT * FROM t); 1 -- CTE in subquery expression > SELECT (WITH t AS (SELECT 1) SELECT * FROM t); 1 -- CTE in CREATE VIEW statement > CREATE VIEW v AS WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4) SELECT * FROM t; > SELECT * FROM v; 1 2 3 4 -- CTE names are scoped > WITH t AS (SELECT 1), t2 AS ( WITH t AS (SELECT 2) SELECT * FROM t) SELECT * FROM t2; 2 

Табличные выражения SQL

Прием № 1, чтобы писать хорошие читаемые SQL-запросы — это табличные выражения (CTE). Люди их боятся, а зря. Давайте разберемся за три минуты, читать увесистую книгу по SQL или проходить курсы не придется.

Проблема

Допустим, у нас есть таблица продаж по месяцам за два года:

┌──────┬───────┬───────┬──────────┬─────────┐ │ year │ month │ price │ quantity │ revenue │ ├──────┼───────┼───────┼──────────┼─────────┤ │ 2019 │ 1 │ 60 │ 200 │ 12000 │ │ 2019 │ 2 │ 60 │ 660 │ 39600 │ │ 2019 │ 3 │ 60 │ 400 │ 24000 │ │ 2019 │ 4 │ 60 │ 300 │ 18000 │ │ 2019 │ 5 │ 60 │ 440 │ 26400 │ │ 2019 │ 6 │ 60 │ 540 │ 32400 │ │ 2019 │ 7 │ 60 │ 440 │ 26400 │ │ 2019 │ 8 │ 60 │ 440 │ 26400 │ │ 2019 │ 9 │ 60 │ 250 │ 15000 │ │ 2019 │ 10 │ 60 │ 420 │ 25200 │ │ . │ . │ . │ . │ . │ └──────┴───────┴───────┴──────────┴─────────┘ 

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

Для начала посчитаем среднемесячную выручку по годам:

select  year,  avg(revenue) as avg_rev from sales group by year; 

┌──────┬─────────┐ │ year │ avg_rev │ ├──────┼─────────┤ │ 2019 │ 25125.0 │ │ 2020 │ 48625.0 │ └──────┴─────────┘ 

Теперь можно выбрать только те записи, revenue в которых не уступает avg_rev :

select  sales.year,  sales.month,  sales.revenue,  round(totals.avg_rev) as avg_rev from sales  join (  select  year,  avg(revenue) as avg_rev  from sales  group by year  ) as totals  on sales.year = totals.year where sales.revenue >= totals.avg_rev; 

┌──────┬───────┬─────────┬─────────┐ │ year │ month │ revenue │ avg_rev │ ├──────┼───────┼─────────┼─────────┤ │ 2019 │ 2 │ 39600 │ 25125.0 │ │ 2019 │ 5 │ 26400 │ 25125.0 │ │ 2019 │ 6 │ 32400 │ 25125.0 │ │ 2019 │ 7 │ 26400 │ 25125.0 │ │ . │ . │ . │ . │ └──────┴───────┴─────────┴─────────┘ 

Решили с помощью подзапроса:

• внутренний запрос считает среднемесячную выручку;
• внешний соединяется с ним и фильтрует результаты.

Запрос в целом получился сложноват. Если вернетесь к нему спустя месяц — наверняка потратите какое-то время на «распутывание». Проблема в том, что такие вложенные запросы приходится читать наоборот:

• найти самый внутренний запрос, осознать;
• мысленно присоединить к более внешнему;
• присоединить к следующему внешнему, и так далее.

Хорошо, когда вложенных уровня два, как в нашем примере. На практике же я часто встречаю трех- и четырехуровневые подзапросы. Форменное издевательство над читателем.

Решение

Вместо подзапроса можно использовать табличное выражение (common table expression, CTE). Любой подзапрос X :

select a, b, c from (X) where e = f 

Механически превращается в CTE:

with cte as (X) select a, b, c from cte where e = f 

В нашем примере:

with totals as (  select  year,  avg(revenue) as avg_rev  from sales  group by year )  select  sales.year,  sales.month,  sales.revenue,  round(totals.avg_rev) as avg_rev from sales  join totals on totals.year = sales.year where sales.revenue >= totals.avg_rev; 

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

with totals as (. ) select . from sales_ru join totals . union all select . from sales_us join totals . 

Табличные выражения SQL чем-то похожи на функции в обычном языке программирования — они уменьшают общую сложность:

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

CTE против подзапроса

Существует миф, что «CTE медленные». Он пришел из старых версий PostgreSQL (11 и раньше), которые всегда материализовали CTE — вычисляли полный результат табличного выражения и запоминали до конца запроса.

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

with cte as (select * from foo) select * from cte where id = 500000; 

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

PostgreSQL 12+ и другие современные СУБД поумнели и больше так не делают. Материализация применяется, когда от нее больше пользы, чем вреда. Плюс, многие СУБД позволяют явно управлять этим поведением через инструкции MATERIALIZED / NOT MATERIALIZED .

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

Как понять, когда использовать подзапрос, а когда CTE? Я вывел для себя простое правило, которое пока ни разу не подвело:

Чего и вам желаю.

P.S. Хотите освоить современный SQL? Обратите внимание на Оконные функции

Подписывайтесь на канал, чтобы не пропустить новые заметки ��

WITH обобщенное_табличное_выражение (Transact-SQL)

Задается временно именованный результирующий набор, называемый обобщенным табличным выражением (ОТВ). Он получается при выполнении простого запроса и определяется в области выполнения одиночной инструкции SELECT, INSERT, UPDATE, DELETE или MERGE. Это предложение может использоваться также в инструкции CREATE VIEW как часть определяющей ее инструкции SELECT. Обобщенное табличное выражение может включать ссылки на само себя. Такое выражение называется рекурсивным обобщенным табличным выражением.

Синтаксис

[ WITH [ . n ] ] ::= expression_name [ ( column_name [ . n ] ) ] AS ( CTE_query_definition ) 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

expression_name

Является допустимым идентификатором для обобщенного табличного выражения. expression_name должно отличаться от имени другого обобщенного табличного выражения, определенного в том же предложении WITH , но expression_name может совпадать с именем базовой таблицы или представления. Любая ссылка на аргумент expression_name в запросе использует обобщенное табличное выражение, но не базовый объект.

column_name

Задается имя столбца в обобщенном табличном выражении. Повторяющиеся имена в определении одного обобщенного табличного выражения (CTE) не допускаются. Количество заданных имен столбцов должно совпадать с количеством столбцов в результирующем наборе CTE_query_definition. Список имен столбцов необязателен только в том случае, если всем результирующим столбцам в определении запроса присвоены уникальные имена.

CTE_query_definition

Задается инструкция SELECT, результирующий набор которой заполняет обобщенное табличное выражение. Инструкция SELECT для CTE_query_definition должна соответствовать таким же требованиям, что и при создании представления, за исключением того, что CTE не может определять другое CTE. Дополнительные сведения см. в подразделе «Замечания» и статье CREATE VIEW (Transact-SQL).

Если определено несколько объектов CTE_query_definition, определения запроса необходимо объединить с помощью одного из следующих операторов над множествами: UNION ALL, UNION, EXCEPT или INTERSECT.

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

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

• За обобщенным табличным выражением (ОТВ) должны следовать одиночные инструкции SELECT , INSERT , UPDATE или DELETE , ссылающиеся на некоторые или на все столбцы ОТВ. Обобщенное табличное выражение может задаваться также в инструкции CREATE VIEW как часть определяющей инструкции SELECT представления.
• Несколько определений запросов обобщенных табличных выражений (ОТВ) могут быть определены в нерекурсивных ОТВ. Определения могут объединяться одним из следующих операторов над множествами: UNION ALL , UNION , INTERSECT или EXCEPT .
• Обобщенные табличные выражения (ОТВ) могут иметь ссылки на самих себя, а также на ОТВ, определенные до этого в том же предложении WITH . Ссылки на определяемые далее обобщенные табличные выражения недопустимы.
• Задание в одном CTE нескольких предложений WITH недопустимо. Например, если CTE_query_definition содержит вложенный запрос, этот вложенный запрос не может содержать вложенное предложение WITH, определяющее другое CTE.
• Следующие предложения не могут использоваться в CTE_query_definition:
  • ORDER BY (за исключением случаев задания предложения TOP )
  • INTO
  • Предложение OPTION с указаниями запроса
  • FOR BROWSE

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

  Следующие рекомендации применимы к определению рекурсивных обобщенных табличных выражений.

  • Определение рекурсивного обобщенного табличного выражения должно содержать по крайней мере два определения обобщенного табличного выражения запросов — закрепленный элемент и рекурсивный элемент. Может быть определено несколько закрепленных элементов и рекурсивных элементов, однако все определения запросов закрепленного элемента должны быть поставлены перед первым определением рекурсивного элемента. Все определения обобщенных табличных выражений запросов (ОТВ) являются закрепленными элементами, если только они не ссылаются на само ОТВ.
  • Закрепленные элементы должны объединяться с помощью одного из следующих операторов над множествами: UNION ALL, UNION, INTERSECT или EXCEPT. UNION ALL является единственным оператором над множествами, который может находиться между последним закрепленным элементом и первым рекурсивным элементом, а также может применяться при объединении нескольких рекурсивных элементов.
  • Количество столбцов членов указателя и рекурсивных элементов должно совпадать.
  • Тип данных столбца в рекурсивном элементе должен совпадать с типом данных соответствующего столбца в закрепленном элементе.
  • Предложение FROM рекурсивного элемента должно ссылаться на обобщенное табличное выражение expression_name только один раз.
  • Следующие элементы недопустимы в определении CTE_query_definition рекурсивного элемента:
    • SELECT DISTINCT
    • GROUP BY
    • PIVOT (Если уровень совместимости базы данных равен 110 или выше, см. статью Критические изменения в функциях ядро СУБД в SQL Server 2016 г..)
    • HAVING
    • Скалярное агрегирование
    • TOP
    • LEFT , RIGHT , OUTER JOIN ( INNER JOIN допускается)
    • Подзапросы
    • Указание, применимое к рекурсивной ссылке на обобщенное табличное выражение в определении CTE_query_definition.

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

    • Все столбцы, возвращаемые рекурсивным обобщенным табличным выражением, могут содержать значения NULL, независимо от того, могут ли иметь значения NULL столбцы, возвращаемые участвующими инструкциями SELECT .
    • Неправильно составленное рекурсивное ОТВ может привести к бесконечному циклу. Например, если определение запроса рекурсивного элемента возвращает одинаковые значения как для родительского, так и для дочернего столбца, то образуется бесконечный цикл. Для предотвращения бесконечного цикла можно ограничить количество уровней рекурсии, допустимых для определенной инструкции, при помощи указания MAXRECURSION и значения в диапазоне от 0 до 32 767 в предложении OPTION инструкции INSERT , UPDATE , DELETE или SELECT . Это дает возможность контролировать выполнение инструкции до тех пор, пока не будет разрешена проблема с кодом, из-за которой происходит зацикливание программы. Серверное значение по умолчанию равно 100. Если указано значение 0, ограничения не применяются. В одной инструкции может быть указан только одно значение MAXRECURSION . Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).
    • Представление, содержащее рекурсивное обобщенное табличное выражение, не может использоваться для обновления данных.
    • Курсоры могут определяться на запросах при помощи обобщенных табличных выражений. Обобщенное табличное выражение является аргументом select_statement, который определяет результирующий набор курсора. Для рекурсивных обобщенных табличных выражений допустимы только однонаправленные и статические курсоры (курсоры моментального снимка). Если в рекурсивном обобщенном табличном выражении указан курсор другого типа, тип курсора преобразуется в статический.
    • В обобщенном табличном выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах. Если на удаленный сервер имеются ссылки в рекурсивном элементе обобщенного табличного выражения, создается буфер для каждой удаленной таблицы, так что к таблицам может многократно осуществляться локальный доступ. Если это запрос CTE, операторы Index Spool/Lazy Spool отображаются в плане запроса и будут иметь дополнительный предикат WITH STACK . Это один из способов подтверждения надлежащей рекурсии.
    • Аналитические и агрегатные функции в рекурсивной части обобщенных табличных выражений применяются для задания текущего уровня рекурсии, а не для задания обобщенных табличных выражений. Такие функции, как ROW_NUMBER , работают только с подмножествами данных, которые передаются им текущим уровнем рекурсии, но не со всем множеством данных, которые передаются в рекурсивную часть обобщенного табличного выражения. Дополнительные сведения см. в примере «И». Использование аналитических функций в указанном ниже рекурсивном CTE.

    Возможности и ограничения общих табличных выражений в Azure Synapse Analytics и Система платформы аналитики (PDW)

    Текущая реализация CTEs в Azure Synapse Analytics и analytics Platform System (PDW) имеет следующие возможности и ограничения:

    • Обобщенное табличное выражение можно задать в инструкции SELECT .
    • Обобщенное табличное выражение можно задать в инструкции CREATE VIEW .
    • Обобщенное табличное выражение можно задать в инструкции CREATE TABLE AS SELECT (CTAS).
    • Обобщенное табличное выражение можно задать в инструкции CREATE REMOTE TABLE AS SELECT (CRTAS).
    • Обобщенное табличное выражение можно задать в инструкции CREATE EXTERNAL TABLE AS SELECT (CETAS).
    • Обобщенное табличное выражение может ссылаться на внешнюю таблицу.
    • Обобщенное табличное выражение может ссылаться на внешнюю таблицу.
    • В обобщенном табличном выражении можно задать несколько определений запросов обобщенных табличных выражений (ОТВ).
    • За обобщенным табличным выражением должна следовать одиночная инструкция SELECT . Инструкции INSERT , UPDATE , DELETE и MERGE не поддерживаются.
    • Обобщенное табличное выражение, которое включает ссылки на себя (рекурсивное обобщенное табличное выражение), не поддерживается.
    • Задание в одном CTE нескольких предложений WITH недопустимо. Например, если CTE_query_definition содержит вложенный запрос, этот вложенный запрос не может содержать вложенное предложение WITH , определяющее другое обобщенное табличное выражение.
    • Предложение ORDER BY невозможно использовать в определении CTE_query_definition, кроме случаев, когда задано предложение TOP .
    • Если обобщенное табличное выражение используется в инструкции, являющейся частью пакета, то за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.
    • При использовании в инструкциях, подготовленных методом sp_prepare , обобщенные табличные выражения будут вести себя так же, как другие инструкции SELECT в PDW. Однако если ТСЗ используются в рамках CETAS, подготовленной sp_prepare , поведение может отложиться от SQL Server и других инструкций PDW из-за способа реализации привязки. sp_prepare Если инструкция SELECT , ссылающаяся на обобщенное табличное выражение, использует неправильный столбец, который отсутствует в CTE, метод sp_prepare будет передан без обнаружения ошибки, а ошибка вместо этого будет создана при выполнении метода sp_execute .

    Примеры

    А. Создание обобщенного табличного выражения

    В следующем примере показано общее количество заказов на продажу в год для каждого представителя по продажам в Adventure Works Cycles.

    -- Define the CTE expression name and column list. WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. ( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) -- Define the outer query referencing the CTE name. SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM Sales_CTE GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear; 

    B. Использование обобщенного табличного выражения для ограничения общего и среднего количества отчетов

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

    WITH Sales_CTE (SalesPersonID, NumberOfOrders) AS ( SELECT SalesPersonID, COUNT(*) FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID ) SELECT AVG(NumberOfOrders) AS "Average Sales Per Person" FROM Sales_CTE; 

    C. Использование нескольких определений CTE в одном запросе

    В следующем примере показано, как определить несколько ОТВ в одном запросе. Обратите внимание, что для разделения определений запросов обобщенных табличных выражений используется запятая. Функция FORMAT, используемая для отображения денежных сумм в формате валюты, доступна в SQL Server 2012 и более поздних версиях.

    WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear) AS -- Define the first CTE query. ( SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID, YEAR(OrderDate) ) , -- Use a comma to separate multiple CTE definitions. -- Define the second CTE query, which returns sales quota data by year for each sales person. Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear) AS ( SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear FROM Sales.SalesPersonQuotaHistory GROUP BY BusinessEntityID, YEAR(QuotaDate) ) -- Define the outer query by referencing columns from both CTEs. SELECT SalesPersonID , SalesYear , FORMAT(TotalSales,'C','en-us') AS TotalSales , SalesQuotaYear , FORMAT (SalesQuota,'C','en-us') AS SalesQuota , FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota FROM Sales_CTE JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear ORDER BY SalesPersonID, SalesYear; 

    Здесь приводится частичный результирующий набор.

    SalesPersonID SalesYear TotalSales SalesQuotaYear SalesQuota Amt_Above_or_Below_Quota ------------- --------- ----------- -------------- ---------- ---------------------------------- 274 2005 $32,567.92 2005 $35,000.00 ($2,432.08) 274 2006 $406,620.07 2006 $455,000.00 ($48,379.93) 274 2007 $515,622.91 2007 $544,000.00 ($28,377.09) 274 2008 $281,123.55 2008 $271,000.00 $10,123.55 

    D. Использование рекурсивного обобщенного табличного выражения для отображения нескольких уровней рекурсии

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

    -- Create an Employee table. CREATE TABLE dbo.MyEmployees ( EmployeeID SMALLINT NOT NULL, FirstName NVARCHAR(30) NOT NULL, LastName NVARCHAR(40) NOT NULL, Title NVARCHAR(50) NOT NULL, DeptID SMALLINT NOT NULL, ManagerID SMALLINT NULL, CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC), CONSTRAINT FK_MyEmployees_ManagerID_EmployeeID FOREIGN KEY (ManagerID) REFERENCES dbo.MyEmployees (EmployeeID) ); -- Populate the table with values. INSERT INTO dbo.MyEmployees VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16, NULL) ,(273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1) ,(274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273) ,(275, N'Michael', N'Blythe', N'Sales Representative', 3, 274) ,(276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274) ,(285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273) ,(286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285) ,(16, N'David', N'Bradley', N'Marketing Manager', 4, 273) ,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16); 

    WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports ORDER BY ManagerID; 

    Использование рекурсивного обобщенного табличного выражения для отображения двух уровней рекурсии

    В следующем примере представлены руководители и отчитывающиеся перед ними служащие. Количество возвращаемых уровней ограничено двумя.

    WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports WHERE EmployeeLevel  

    Использование рекурсивного обобщенного табличного выражения для отображения иерархического списка

    В следующем примере добавляются имена руководителя и сотрудников, а также соответствующие им должности. Иерархия руководителей и служащих дополнительно выделяется с помощью соответствующих отступов на каждом уровне.

    WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS (SELECT CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName), e.Title, e.EmployeeID, 1, CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName) FROM dbo.MyEmployees AS e WHERE e.ManagerID IS NULL UNION ALL SELECT CONVERT(VARCHAR(255), REPLICATE ('| ' , EmployeeLevel) + e.FirstName + ' ' + e.LastName), e.Title, e.EmployeeID, EmployeeLevel + 1, CONVERT (VARCHAR(255), RTRIM(Sort) + '| ' + FirstName + ' ' + LastName) FROM dbo.MyEmployees AS e JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT EmployeeID, Name, Title, EmployeeLevel FROM DirectReports ORDER BY Sort; 

    Использование подсказки MAXRECURSION для отмены инструкции

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

    --Creates an infinite loop WITH cte (EmployeeID, ManagerID, Title) AS ( SELECT EmployeeID, ManagerID, Title FROM dbo.MyEmployees WHERE ManagerID IS NOT NULL UNION ALL SELECT cte.EmployeeID, cte.ManagerID, cte.Title FROM cte JOIN dbo.MyEmployees AS e ON cte.ManagerID = e.EmployeeID ) --Uses MAXRECURSION to limit the recursive levels to 2 SELECT EmployeeID, ManagerID, Title FROM cte OPTION (MAXRECURSION 2); 

    После исправления ошибки в коде подсказка MAXRECURSION больше не нужна. В следующем примере приводится правильный код.

    WITH cte (EmployeeID, ManagerID, Title) AS ( SELECT EmployeeID, ManagerID, Title FROM dbo.MyEmployees WHERE ManagerID IS NOT NULL UNION ALL SELECT e.EmployeeID, e.ManagerID, e.Title FROM dbo.MyEmployees AS e JOIN cte ON e.ManagerID = cte.EmployeeID ) SELECT EmployeeID, ManagerID, Title FROM cte; 

    Д. Использование обобщенного табличного выражения для выборочного прохождения рекурсивной связи в инструкции SELECT

    В следующем примере показана иерархия узлов и компонентов продукции, необходимых для создания велосипеда для ProductAssemblyID = 800 .

    USE AdventureWorks2022; GO WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS ( SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.EndDate, 0 AS ComponentLevel FROM Production.BillOfMaterials AS b WHERE b.ProductAssemblyID = 800 AND b.EndDate IS NULL UNION ALL SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, bom.EndDate, ComponentLevel + 1 FROM Production.BillOfMaterials AS bom INNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL ) SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate, ComponentLevel FROM Parts AS p INNER JOIN Production.Product AS pr ON p.ComponentID = pr.ProductID ORDER BY ComponentLevel, AssemblyID, ComponentID; 

    F. Использование рекурсивного CTE в инструкции UPDATE

    В следующем примере обновляется значение PerAssemblyQty для всех деталей, используемых при сборке продукта Road-550-W Yellow, 44 (ProductAssemblyID``800 ). Обобщенное табличное выражение возвращает иерархический список деталей, которые непосредственно используются для сборки ProductAssemblyID 800 , и компонентов, которые используются для сборки этих деталей и т. д. Изменяются только строки, возвращенные обобщенным табличным выражением.

    USE AdventureWorks2022; GO WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS ( SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.EndDate, 0 AS ComponentLevel FROM Production.BillOfMaterials AS b WHERE b.ProductAssemblyID = 800 AND b.EndDate IS NULL UNION ALL SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, bom.EndDate, ComponentLevel + 1 FROM Production.BillOfMaterials AS bom INNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL ) UPDATE Production.BillOfMaterials SET PerAssemblyQty = c.PerAssemblyQty * 2 FROM Production.BillOfMaterials AS c JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID WHERE d.ComponentLevel = 0; 

    H. Использование нескольких привязок и рекурсивных элементов

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

    -- Genealogy table IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person; GO CREATE TABLE dbo.Person(ID int, Name VARCHAR(30), Mother INT, Father INT); GO INSERT dbo.Person VALUES(1, 'Sue', NULL, NULL) ,(2, 'Ed', NULL, NULL) ,(3, 'Emma', 1, 2) ,(4, 'Jack', 1, 2) ,(5, 'Jane', NULL, NULL) ,(6, 'Bonnie', 5, 4) ,(7, 'Bill', 5, 4); GO -- Create the recursive CTE to find all of Bonnie's ancestors. WITH Generation (ID) AS ( -- First anchor member returns Bonnie's mother. SELECT Mother FROM dbo.Person WHERE Name = 'Bonnie' UNION -- Second anchor member returns Bonnie's father. SELECT Father FROM dbo.Person WHERE Name = 'Bonnie' UNION ALL -- First recursive member returns male ancestors of the previous generation. SELECT Person.Father FROM Generation, Person WHERE Generation.ID=Person.ID UNION ALL -- Second recursive member returns female ancestors of the previous generation. SELECT Person.Mother FROM Generation, dbo.Person WHERE Generation.ID=Person.ID ) SELECT Person.ID, Person.Name, Person.Mother, Person.Father FROM Generation, dbo.Person WHERE Generation.ID = Person.ID; GO 

    I. Использование аналитических функций в рекурсивном CTE

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

    DECLARE @t1 TABLE (itmID INT, itmIDComp INT); INSERT @t1 VALUES (1,10), (2,10); DECLARE @t2 TABLE (itmID INT, itmIDComp INT); INSERT @t2 VALUES (3,10), (4,10); WITH vw AS ( SELECT itmIDComp, itmID FROM @t1 UNION ALL SELECT itmIDComp, itmID FROM @t2 ) , r AS ( SELECT t.itmID AS itmIDComp , NULL AS itmID , CAST(0 AS BIGINT) AS N , 1 AS Lvl FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID) UNION ALL SELECT t.itmIDComp , t.itmID , ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N , Lvl + 1 FROM r JOIN vw AS t ON t.itmID = r.itmIDComp ) SELECT Lvl, N FROM r; 

    Следующие результаты являются ожидаемыми результатами выполнения запроса.

    Lvl N 1 0 1 0 1 0 1 0 2 4 2 3 2 2 2 1 

    Следующие результаты являются фактическими результатами выполнения запроса.

    Lvl N 1 0 1 0 1 0 1 0 2 1 2 1 2 1 2 1 

    N возвращает 1 для каждого прохода рекурсивной части ОТВ, так как в ROWNUMBER передается только подмножество данных для данного уровня рекурсии. Для каждой итерации рекурсивной части запроса в ROWNUMBER передается только одна строка.

    Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

    J. Использование обобщенного табличного выражения в инструкции CTAS

    В следующем примере создается новая таблица, содержащая общее количество заказов на продажу в год для каждого представителя продаж в Adventure Works Cycles.

    USE AdventureWorks2022; GO CREATE TABLE SalesOrdersPerYear WITH ( DISTRIBUTION = HASH(SalesPersonID) ) AS -- Define the CTE expression name and column list. WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. ( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) -- Define the outer query referencing the CTE name. SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM Sales_CTE GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear; GO 

    K. Использование обобщенного табличного выражения в инструкции CETAS

    В следующем примере создается новая внешняя таблица, содержащая общее количество заказов на продажу в год для каждого представителя по продажам в Adventure Works Cycles.

    USE AdventureWorks2022; GO CREATE EXTERNAL TABLE SalesOrdersPerYear WITH ( LOCATION = 'hdfs://xxx.xxx.xxx.xxx:5000/files/Customer', FORMAT_OPTIONS ( FIELD_TERMINATOR = '|' ) ) AS -- Define the CTE expression name and column list. WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. ( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) -- Define the outer query referencing the CTE name. SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM Sales_CTE GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear; GO 

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

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

    WITH CountDate (TotalCount, TableName) AS ( SELECT COUNT(datekey), 'DimDate' FROM DimDate ) , CountCustomer (TotalAvg, TableName) AS ( SELECT COUNT(CustomerKey), 'DimCustomer' FROM DimCustomer ) SELECT TableName, TotalCount FROM CountDate UNION ALL SELECT TableName, TotalAvg FROM CountCustomer; 

    См. также

    • CREATE VIEW (Transact-SQL)
    • DELETE (Transact-SQL)
    • EXCEPT и INTERSECT (Transact-SQL)
    • INSERT (Transact-SQL)
    • SELECT (Transact-SQL)
    • UPDATE (Transact-SQL)

    Common Table Expressions и деление таблиц на страницы

    Common Table Expressions или по русский обобщенные табличные выражения – это технология, которая появилась в MS SQL Server 2005 и представляет собой одну из форм повторного использования результатов одного SQL запроса в другом.

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

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

    Вот пример того, какой запрос использовался на моем форуме до оптимизации:

    select * from forummessages where TopicID=310 order by messageid

    при его выполнении было произведено 7815 логических чтений.

    А вот пример запроса, использующего CTE

    declare @pagenumber int, @pagesize int
    set @pagesize=20
    set @pagenumber=10
    ;with rowpaging
    as
    (select ROW_NUMBER() over(order by messageid) as rn,messageid from forummessages where TopicID=310)
    select * from ForumMessages as m JOIN rowpaging as r ON m.MessageID=r.MessageID
    where rn between @pagesize*(@pagenumber-1)+1 and @pagesize*@pagenumber
    order by m.messageid

    при его выполнении было произведено 68 логических чтений.

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

    UPD:
    Давайте рассмотрим, как работает запрос на LINQ с использованием методов Skip() и Take().

    Вот пример кода на linq для извлечения тех же 10 строк:

    DBM dbm = new DBM();
    var items = (from m in dbm.Context.ForumMessages
    where m.TopicID == 310
    orderby m.MessageID
    select m).Skip(200).Take(10);
    string r = "";
    foreach (var x in items)
    r += x.Body;

    и вот то что выполняется на SQL Server (отловлено при помощи SQL Profile)

    SELECT TOP (10)
    [Filter1].[MessageID] AS [MessageID],
    [Filter1].[TopicID] AS [TopicID],
    [Filter1].[UserID] AS [UserID],
    [Filter1].[Body] AS [Body],
    [Filter1].[CreationDate] AS [CreationDate],
    [Filter1].[Visible] AS [Visible],
    [Filter1].[IPAddress] AS [IPAddress],
    [Filter1].[Rating] AS [Rating],
    [Filter1].[Deleted] AS [Deleted],
    [Filter1].[WhoDelete] AS [WhoDelete]
    FROM ( SELECT [Extent1].[MessageID] AS [MessageID], [Extent1].[TopicID] AS [TopicID], [Extent1].[UserID] AS [UserID], [Extent1].[Body] AS [Body], [Extent1].[CreationDate] AS [CreationDate], [Extent1].[Visible] AS [Visible], [Extent1].[IPAddress] AS [IPAddress], [Extent1].[Rating] AS [Rating], [Extent1].[Deleted] AS [Deleted], [Extent1].[WhoDelete] AS [WhoDelete], row_number() OVER (ORDER BY [Extent1].[MessageID] ASC) AS [row_number]
    FROM [dbo].[ForumMessages] AS [Extent1]
    WHERE 310 = [Extent1].[TopicID]
    ) AS [Filter1]
    WHERE [Filter1].[row_number] > 200
    ORDER BY [Filter1].[MessageID] ASC

    В результате выполнения этого запроса мы получаем 4889 логических чтений, что почти в 72 раза больше чем в случае использования CTE и в полтора раза меньше чем извлечение всех строк выборки, неразделенной на страницы.

    Читать:

    Как очистить кэш на айфоне xr в telegram