Operator APPLY w TSQL

Do napisania dzisiejszego artykułu skłonił mnie fakt, iż naprawdę niewiele osób piszących zapytania TSQL używa tzw. zaawansowanych elementów języka. Dlaczego tak się dzieje? Nie jestem pewien ale wydaje mi się, że słowo “zaawansowanych” już dostatecznie odstrasza potencjalnych zainteresowanych – w moim mniemaniu całkowicie niesłusznie. Jednym ze “straszniejszych” elementów TSQL jest bez wątpienia APPLY – część z was z pewnością  go używa, inni słyszeli ale nie widzą zastosowania, dla niektórych to zupełna nowość. Poniżej postaram się krótko przedstawić gdzie i w jaki sposób możemy niniejszego narzędzia użyć, a jest to narzędzie naprawdę potężne. Zaczynajmy!

Aby zrozumieć specyfikę działania naszego dzisiejszego bohatera musimy przypomnieć sobie teorię z zakresu operatorów złączenia czyli popularnych JOINów.  W teorii relacyjnych baz danych mamy do czynienia z tak zwanymi złączeniami wewnętrznymi (ang. INNER JOIN) oraz zewnętrznymi (ang. OUTER JOIN). W zależności od rodzaju złączenia dwa zestawy danych są ze sobą w określony sposób porównywane i zwracany jest rezultat. To co jest niezmierne istotne w działaniu złączeń to fakt, iż oba zestawy danych (na obrazku nazwane Table 1 oraz Table 2) muszą być predefiniowane – silnik musi mieć dane na temat obu stron operatora:

Co natomiast jeśli jedna ze stron, które chcemy dołączać nie zawiera predefiniowanego zbioru danych i nie możemy go zapisać przy pomocy znanych nam metod? Możemy użyć APPLY! Poniższy rysunek w pełni oddaje istotę działania APPLY.

Jak można zauważyć APPLY oczekuje predefiniowanego zestawu po lewej stronie złączenia. Tak więc jeśli chcemy dla przykładu użyć funkcji tabelarycznej wiersz po wierszu nie możemy tego zrobić przy użyciu tradycyjnych złączeń. W tym momencie może Wam przyjść na myśl użycie kursorów – myślę, że możecie zapomnieć o tej myśli tak szybko jak przyszła Wam do głowy. Jak to działa sprawdźmy to na przykładzie! W naszych przykładach standardowo będziemy używać bazy Adventure Works. Jako nasz problem biznesowy weźmiemy sobie typową sytuację: potrzebujemy zapytania, które w dynamiczny sposób zwróci nam N ostatnich dat zamówień dla określonego klienta.

Po pierwsze potrzebujemy funkcji tabelarycznej, która nam coś takie zwróci – nie skupijamy się konkretnie jak ona działa – przyjmijmy ją jako czarną skrzynkę:

CREATE FUNCTION [dbo].[test]
(
   @custId int,
   @topn int
)
RETURNS TABLE
AS
RETURN
SELECT DISTINCT TOP (@topn) 
      dc.CustomerKey,
      fis.OrderDate 
FROM 
      AdventureWorksDW2012.dbo.DimCustomer AS dc
JOIN 
      AdventureWorksDW2012.dbo.FactInternetSales AS fis
ON 
      fis.CustomerKey=dc.CustomerKey
WHERE 
      dc.CustomerKey=@custId

Przetestujmy jej działanie w pierwszym parametrze podając identyfikator wybranego klienta, w drugim natomiast liczbę dat jakie chcemy otrzymać.

SELECT 
    * 
FROM 
    dbo.test(11001,2)

Jak widać poniżej funkcja działa zgodnie z przeznaczeniem.

Wreszcie nadszedł czas na przetestowanie naszego APPLY. Złączmy tabelę z klientami tak aby pokazać dwie ostatnie daty zakupów dla każdego z klientów:

USE AdventureWorksDW2012
GO

SELECT 
      dc.CustomerKey,
      t.OrderDate
FROM 
      dbo.DimCustomer AS dc
CROSS APPLY 
      dbo.test(dc.CustomerKey,2) AS t
ORDER BY 
      1 ASC,
      2 DESC

Rezultat jest taki jak się spodziewaliśmy – powinniśmy pamiętać o tym, że konkretnie CROSS APPLY logicznie działa bardzo podobnie do złączenia wewnętrznego czyli zwraca tylko te wiersze, które znajdują się w obu zestawach.

Wygląda to świetnie, co jednak gdy chcemy poznać klientów, którzy w ogóle nie złożyli żadnego zamówienia? Jak już wspomniałem CROSS APPLY działa jak INNER JOIN – tak więc czy jest coś podobnego do OUTER JOIN? Oczywiście! Mamy do dyspozycji OUTER APPLY. Sprawdźmy to na przykładzie. W bazie Adventureworks2012DW nie ma klienta, który nigdy nie złożył zamówienia, więc możemy sobie to zasymulować np. poprzez dodanie wiersza do tabeli DimCustomer. W zapytaniu dodamy również klauzulę WHERE gdzie pokażemy sobie tylko tych klientów, którzy nie mają połączenia do tabeli z zamówieniami.

USE AdventureWorksDW2012
GO

SELECT 
     dc.CustomerKey,
     t.OrderDate
FROM
     dbo.DimCustomer AS dc
OUTER APPLY 
     dbo.test(dc.CustomerKey,2) AS t
WHERE 
     OrderDate IS NULL
ORDER BY 
      1 ASC,
      2 DESC

Jak widać poniżej w rezultacie otrzymaliśmy jednego klienta, który nigdy nie złożył żadnego zamówienia.

Tak więc użycie operatorów APPLY jest naprawdę proste i przyjemne w użyciu. Ponadto rozwiązują one całą masę problemów w naszej codziennej pracy. Co jednak z kwestą wydajności? Jak możecie się domyślać nie jest najlepiej ze względu na fakt, iż funkcje tabelaryczne nie są najwydajniejszą strukturą – jednak mimo wszystko warto znać te funkcjonalności języka TSQL gdyż w wielu przypadkach naprawdę się sprawdzają.