• Artykuł

Microsoft SQL Server

Arsenał programisty T-SQL – Common Table Expression (CTE) Udostępnij na: Facebook

Opublikowano: 3 marca 2010
Autor: Paweł Potasiński i Marek Adamczuk

CTE – podstawy  CTE – podstawy
Jedno CTE czy więcej?  Jedno CTE czy więcej?
Nie tylko SELECT  Nie tylko SELECT
Rekursywne CTE  Rekursywne CTE
Podsumowanie  Podsumowanie

 

Ostatnie dwie wersje systemu Microsoft SQL Server – 2005 i 2008 – dostarczają zestaw nowości w języku Transact-SQL (T-SQL), dzięki którym rozwiązywanie najbardziej wyrafinowanych problemów stawianych przed programistami baz danych staje się o wiele prostsze. Seria „Arsenał programisty T-SQL” ma na celu przedstawienie tych konstrukcji dostępnych w T-SQL w SQL Server 2008, które mogą się przydać programistom najbardziej.

Jedną z takich konstrukcji jest Common Table Expression (CTE).

CTE – podstawy

CTE to nazwane zapytanie reprezentujące tymczasowy zestaw rekordów definiowany w zasięgu jednego polecenia SELECT, INSERT, UPDATE lub DELETE. Składniowo CTE przypomina podzapytanie w z języka Transact-SQL lub elementy obliczane z języka MDX.

Składnię CTE łatwo rozpoznać po charakterystycznym słowie WITH. Słowo to jest w języku Transact-SQL wykorzystywane między innymi do podawania wskazówek (ang. hint) dla optymalizatora lub do deklarowania przestrzeni nazw XML (klauzula WITH XMLNAMESPACES).

Aby uniknąć błędów parsowania, definicję CTE należy oddzielić średnikiem od poprzedniego polecenia. Przy braku średnika przed słowem WITH parser zwróci następujący komunikat błędu:
Pobierz
Pobierz artykuł w pliku:
Pobierz Arsenał programisty T-SQL – Common Table Expression (CTE) (Paweł Potasiński, Marek Adamczuk).pdf
1.13 MB
Plik PDF
Pobierz Arsenał programisty T-SQL – Common Table Expression (CTE) (Paweł Potasiński, Marek Adamczuk).xps
667 kB
Plik tekstowy

Pobierz program Adobe Acrobat Reader

 

 
[Msg 319, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'with'. If this statement is a common table

expression, an xmlnamespaces clause or a change tracking context clause, the

previous statement must be terminated with a semicolon.

Przykład prostego CTE:

USE AdventureWorks;

GO

WITH MojePierwszeCTE AS (

    SELECT Name

    FROM HumanResources.Department

)

SELECT * FROM MojePierwszeCTE;

Name --------------------------
Document Control
Engineering
Executive
Facilities and Maintenance
...
Tool Design
(16 row(s) affected)

Zestawem generowanym przez CTE o nazwie MojePierwszeCTE jest zbiór wartości kolumny Name z tabeli HumanResources.Department. Zapytanie SELECT wybiera wszystkie dane ze zbioru generowanego przez CTE.

Zmieńmy nieco definicję CTE z powyższego przykładu.

USE AdventureWorks;

GO



WITH MojePierwszeCTE (Department) AS 

     SELECT Name

     FROM HumanResources.Department

)

SELECT * FROM MojePierwszeCTE;

Podobnie jak przy definiowaniu widoków, użytkownik może zdefiniować nazwy dla kolumn generowanych przez CTE (tu – kolumna została nazwana Department). Należy pamiętać, że nazwy kolumn w CTE muszą być unikalne. Nie mamy tu do czynienia z klasycznymi aliasami określanymi dla kolumn w zapytaniach. Analogicznie działa składnia, którą można by uznać za podanie aliasów dla kolumn:

USE AdventureWorks;

GO



WITH MojePierwszeCTE AS (

     SELECT Name AS Department

     FROM HumanResources.Department

)

SELECT * FROM MojePierwszeCTE;

W powyższym przykładzie ponownie słowo Department nie jest aliasem dla kolumny Name, ale jest nazwą kolumny generowanej przez CTE.

 Do początku strony Do początku strony

Jedno CTE czy więcej?

Definicji CTE nie można bezpośrednio zagnieżdżać. To ograniczenie można jednak łatwo obejść poprzez zastosowanie więcej niż jednego CTE dla pojedynczego polecenia DML. Ta właściwość CTE daje ogromne możliwości uzyskiwania krok po kroku pożądanego wyniku.

Każde kolejne definiowane CTE może odwoływać się do CTE wcześniej zdefiniowanych. W poniższym przykładzie, dla każdego z dostawców, do których złożyliśmy zamówienia prezentujemy datę ostatniego i ewentualnego poprzedniego zamówienia:

USE AdventureWorks;

GO

WITH

cte_Vendors AS

(

  SELECT v.BusinessEntityID AS VendorID, v.Name

  FROM Purchasing.Vendor v WHERE v.ActiveFlag = 1

),

cte_Orders AS

(

  SELECT

    ROW_NUMBER()

      OVER (PARTITION BY o.VendorID ORDER BY o.ShipDate DESC) rn,

    o.VendorID,

    o.ShipDate

  FROM Purchasing.PurchaseOrderHeader o

  INNER JOIN cte_Vendors cv 

  ON o.VendorID = cv.VendorID

)

SELECT

  cv.VendorID,

  cv.Name,

  co1.ShipDate LastShipDate,

  co2.ShipDate PrevShipDate

FROM cte_Vendors cv 

INNER JOIN cte_Orders co1

ON co1.VendorID = cv.VendorID AND co1.rn = 1

LEFT OUTER JOIN cte_Orders co2

ON co2.VendorID = cv.VendorID AND co2.rn = 2;

Przykład ten pokazuje również, w jaki sposób obejść ograniczenie składni zabraniające odwoływania się do funkcji rankingowych poza listą pól i klauzulą ORDER BY.

 Do początku strony Do początku strony

Nie tylko SELECT

O ile zapytanie stanowiące CTE jest edytowalne (reguły jak w widoku), to i na samym CTE możemy wykonywać operacje modyfikujące, tzn. INSERT, UPDATE czy DELETE.

Co więcej, dzięki CTE możliwe jest obejście pewnego ograniczenia związanego z nową składnią UPDATE i DELETE. Otóż od wersji SQL Server 2005 wprowadzono do składni poleceń UPDATE i DELETE klauzulę TOP, pozwalającą usuwać czy nadpisywać zadaną liczbę wierszy. Niestety, nowej składni nie rozszerzono o klauzulę ORDER BY, w ten sposób znacznie ograniczając użyteczność klauzuli TOP. Bez ORDER BY nie jesteśmy w stanie kontrolować, które konkretnie wiersze zostaną nadpisane lub usunięte. I tu znowu z pomocą spieszy nam CTE.

W poniższym przykładzie z tabeli dbo.Numbers o 100 kolejnych liczbach usuniemy najpierw ostatnie 30, a następnie pierwsze 30 wierszy.

WITH cte_Numbers AS (

  SELECT TOP 99.9999999999999 PERCENT * 

  FROM dbo.Numbers 

  ORDER BY Number DESC

)

DELETE TOP (30) FROM cte_Numbers; -- usuwa wiersze od 71 do 100

GO

WITH cte_Numbers AS (

  SELECT TOP 99.9999999999999 PERCENT * 

  FROM dbo.Numbers 

  ORDER BY Number ASC

)

DELETE TOP (30) FROM cte_Numbers; –- usuwa wiersze od 1 do 30

GO

Rozwiązanie polega na zastosowaniu żądanego ORDER BY wewnątrz CTE. Klauzula TOP 99.999999999 PERCENT jest niezbędna, bowiem bez niej w CTE nie możemy stosować ORDER BY, podobnie jak w widokach czy podzapytaniach. Nie możemy również użyć TOP 100 PERCENT, bowiem dla tej wartości ignorowane jest ORDER BY, jako niestanowiące warunku filtrowania dla TOP.

 Do początku strony Do początku strony

Rekursywne CTE

CTE w przeciwieństwie do podzapytań, ma pewną unikalną właściwość. Polega ona na możliwości odwołania CTE do samego siebie wewnątrz definicji. Rekursywne CTE składa się z co najmniej dwóch zapytań połączonych operatorem zbiorowym UNION ALL.

Pierwszy człon, to zapytanie inicjujące, np. szczyt hierarchii. Drugie zapytanie, odwołujące się do samej definicji CTE będzie rekursywnie dołączać rekordy do zbioru wynikowego.

Przy czym ważne, by pamiętać, że każda iteracja w rekursywnym CTE wykonywana jest w oparciu o rekordy zwrócone przez poprzednią iterację (oczywiście wyjątkiem jest tu zapytanie inicjujące). Drugie zapytanie w rekursywnym CTE będzie wykonywane dopóki będą zwracane jakiekolwiek nowe rekordy lub zostanie przekroczony poziom zagnieżdżenia.

Nie należy mylić poziomu zagnieżdżenia CTE z poziomem zagnieżdżenia wywołań obiektów zwracanym przez funkcję @@NESTLEVEL. Domyślna wartość poziomu zagnieżdżenia CTE jest ustawiona na 100 i nie ma niestety żadnego parametru, którym można byłoby ją regulować. Możliwa jest natomiast modyfikacja maksymalnego poziomu zagnieżdżenia w samym zapytaniu opcją MAXRECURSION. Ustawienie tej opcji na 0 powoduje wyłączenie kontroli poziomu zagnieżdżenia.

Zapytanie będące członem rekursywnym CTE jest obarczone pewnymi ograniczeniami – nie może zawierać:

  • słowa kluczowego DISTINCT,
  • klauzuli GROUP BY,
  • klauzuli HAVING,
  • agregacji skalarnej,
  • klauzuli TOP,
  • złączeń zewnętrznych (LEFT JOIN, RIGHT JOIN, FULL JOIN),
  • podzapytań,
  • wskazówek dla optymalizatora (jeżeli dotyczą samego CTE).

Poniższe fragmenty kodu ilustrują metodę generowania tabeli liczb (tu -od 1 do 100) oraz tabeli dat (tu – od 1 stycznia 1990 do 31 grudnia 2020).

  DECLARE
  
    @MinNumber int,
  
    @MaxNumber int;
  
  SELECT @MinNumber = 1, @MaxNumber = 100;  
  
  WITH Numbers AS (
  
    SELECT @MinNumber AS Number
  
    UNION ALL
  
    SELECT Number + 1
  
    FROM NumbersWHERE Number < @MaxNumber
  
  )
  
  SELECT Number
  
  INTO dbo.Numbers 
  
  FROM Numbers
  
  ORDER BY Number
  
  OPTION (MAXRECURSION 0); bez ograniczenia poziomu rekursji
  
  GO

  DECLARE
  
    @MinDate datetime,
  
    @MaxDate datetime;
  
  SELECT @MinDate = '19900101', @MaxDate = '20201231';  
  
  WITH Dates AS (
  
    SELECT @MinDate AS Date
  
    UNION ALL
  
    SELECT DATEADD(day, 1 , Date)
  
    FROM DatesWHERE Date < @MaxDate
  
  )
  
  SELECT 
  
    Date,
  
    YEAR(Date) AS Year,
  
    MONTH(Date) AS Month,
  
    DATENAME(weekday, Date) AS Weekday
  
  INTO dbo.Dates
  
  FROM Dates
  
  ORDER BY Date
  
  OPTION (MAXRECURSION 0);
  
  GO

Po wygenerowaniu w powyższy sposób tabele dobrze jest poindeksować (np. dodając klucze główne z indeksem grupowanym odpowiednio na kolumnach – Number i Date).

Dzięki rekursywnemu CTE można z łatwością wydobyć informacje o strukturze hierarchii, m.in. numer poziomu, na jakim znajduje się dany wiersz w hierarchii. Przykład:

USE AdventureWorksLT

GO

WITH Categories AS (

  SELECT 

    ProductCategoryID, 

    Name, 

    0 AS Level, 

    CAST('/' + Name AS nvarchar(max)) AS Path

  FROM SalesLT.ProductCategory

  WHERE ParentProductCategoryID IS NULL

  UNION ALL 

  SELECT 

    PC.ProductCategoryID, 

    PC.Name, 

    C.Level + 1, 

    CAST(C.Name + '/' + PC.Name AS nvarchar(max))

  FROM Categories AS C

  INNER JOIN SalesLT.ProductCategory AS PC

  ON PC.ParentProductCategoryID = C.ProductCategoryID

)

SELECT * FROM Categories;

GO

W powyższym przykładzie w CTE o nazwie Categories w pierwszej iteracji wybierane są kategorie, które nie mają kategorii.

 Do początku strony Do początku strony

Podsumowanie

Common Table Expression to potężna broń w rękach programisty T-SQL. Dzięki ogromnej elastyczności wynikającej z możliwości definiowania wielu CTE dla jednego zapytania oraz z tego, że w prosty sposób można zbudować w nich zaimplementować rekursywność, jest to jeden z najbardziej docenianych elementów składni języka T-SQL. Opanowanie CTE jest, w naszym odczuciu, obowiązkowe dla każdego, kto pracuje z systemem SQL Server 2005 lub 2008.

Paweł Potasiński Paweł Potasiński (MVP, MCT, MCDBA, MCSE, MCSD, MCITP)
Starszy programista w Asseco Business Solutions S.A. W codziennej pracy zajmuje się rozwojem aplikacji ERP oraz zagadnieniami z zakresu RD. Wykładowca akademicki na kilku uczelniach w Warszawie. Wcześniej pracował jako administrator baz danych oraz trener. Założyciel i lider Polskiej Grupy Użytkowników SQL Server. Wolontariusz organizacji GITCA wspierającej działalność grup pasjonackich IT Pro. Prelegent na licznych konferencjach krajowych i zagranicznych, m.in. European PASS Conference i Microsoft Technology Summit. Microsoft Most Valuable Professional (MVP) w kategorii SQL Server od lipca 2008 roku.
Marek Adamczuk Marek Adamczuk (MVP, MCSE, MCDBA, MCTS)
Kierownik Działu Zarządzania Wiedzą i Szlokeń w Asseco Business Solutions (dawniej Softlab). Współtworzy system klasy ERP zbudowany na platformie SQL Server. Aktywnie uczestniczy we wdrożeniach modułów Logistyka i Sprzedaż oraz CRM, a także w budowie specjalizowanych modułów na potrzeby poszczególnych wdrożeń. Specjalizuje się w budowie mechanizmów autogeneracji kodu T-SQL i zapewnienia wysokiej jego wysokiej jakości. Moderator i opiekun działu SQL Server na portalu wss.pl. Aktywny prelegent Polskiej Grupy Użytkowników SQL Server. Absolwent Kierunku Fizyka, specjalności Fizyka Komputerowa Uniwersytetu Marii Curie-Skłodowskiej w Lublinie. W lipcu 2009 roku wyróżniony przez Microsoft tytułem Most Valuable Professional (MVP) w kategorii SQL Server.
 Do początku strony Do początku strony

Microsoft SQL Server