SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Funkcje tabelaryczne w SQL

Funkcje tabelaryczne, jak sama nazwa wskazuje, w wyniku działania zwracają tabelę. Różnią się od skalarnych tym, że skoro zwracają pełnoprawny zbiór, to można je używać bezpośrednio we FROM. Krótko mówiąc, możemy traktować je w zapytaniach jak zwykłe tabele.

W artykule tym przedstawię konstrukcje i możliwości funkcji tabelarycznych dostępnych w SQL Server. Sposób ich tworzenia oraz typowe zastosowania. Opiszę tu także, związane szczególnie z nimi, specjalne typy złączeń – CROSS APPLY / OUTER APPLY.


Funkcje tabelaryczne proste (inline)

W SQL Server mamy dostępne dwie grupy funkcji tabelarycznych. Pierwszy z nich – funkcje proste, zwracają wynik pojedynczego zapytania. Są nazywane również widokami sparametryzowanymi. Jest to chyba najlepsza analogia aby zapamiętać ich przeznaczenie. Działają w bardzo podobny sposób jak zwykłe widoki. Ich przewagą jest możliwość stosowania parametrów przekazywanych do ich wnętrza.

W definicji tego typu funkcji, możemy umieścić tylko jedno zapytanie. Nie mamy możliwości stosowania w nich żadnej wyrafinowanej logiki, procedur czy zmiennych – do tego służą funkcje złożone (multi-statement). Z tego powodu konstrukcja jest mocno uproszczona, nie zawiera bloku BEGIN … END. Całość zawarta jest w poleceniu RETURN().

Utwórzmy funkcję, która będzie zwracała numery, daty i wartości zamówień danego Klienta.

CREATE FUNCTION [dbo].[CustOrders] 
(	
  -- początek definicji identyczny jak dla funkcji skalarnych
  -- mamy możliwość określenia od 0 – 1024 parametrów
  @CustomerID nchar(5)
)
RETURNS TABLE      -- funkcja zwracać będzie tabelę
AS
RETURN 
(
   SELECT o.CustomerID, o.OrderID,  o.OrderDate,
          CAST( SUM( UnitPrice * Quantity * (1-Discount)) as smallmoney) as OrderValue
   FROM dbo.Orders o inner join dbo.[Order Details] od on o.OrderID = od.OrderId
   WHERE o.CustomerID = @CustomerID
   GROUP BY o.CustomerID, o.OrderID, OrderDate, ShipCity
 
)
GO

Taka funkcja, może posłużyć np. w aplikacji web do pobierania aktualnych informacji o zleceniach w profilu Klienta. Sposób jej wywołania jest bardzo prosty. Podobnie jak w przypadku innych funkcji (np. skalarnych) wywołujemy je po dwuczłonowej nazwie.

SELECT *
FROM dbo.CustOrders('BOLID') AS TabZam
ORDER BY OrderValue

Funkcje_tabelaryczne_SQL_01

Jak widać funkcja tabelaryczna, zwraca zbiór. Możemy z nią wykonywać złączenia identycznie jak z każdym innym zbiorem (tabelą). Pobierzmy, więc dodatkowe informacje o Kliencie :

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.City, c.Country, TabZam.*
FROM dbo.Customers C INNER JOIN dbo.CustOrders('BOLID') AS TabZam
	ON c.CustomerID = TabZam.CustomerID
ORDER BY OrderValue

Funkcje_tabelaryczne_SQL_02


CROSS APPLY i OUTER APPLY

Omawiając funkcje tabelaryczne, trzeba koniecznie wspomnieć o specjalnych typach złączeń. Poza standardowymi INNER, OUTER JOIN, mamy do dyspozycji dwa dodatkowe bazujące na operatorze APPLY. Dedykowane są dla wyrażeń tabelarycznych, czyli w szczególności funkcji. Są one bardzo przydatne i dość proste w użyciu.

Ich zastosowanie najlepiej od razu pokazać na przykładzie. W poprzednim zadaniu, połączyliśmy tabelę z funkcją, do której jawnie przekazaliśmy parametr CustomerID = ‘BOLID’.

W ten sposób, zostały zwrócone wszystkie zamówienia dla konkretnego Klienta, plus informacje o nim z tabeli dbo.Customers.

W sytuacji, gdy chcemy wielokrotnie uruchomić funkcję, czyli na przykład wywołać ją dla wszystkich Klientów z Hiszpanii, możemy wykorzystać do tego złączenia typu CROSS lub OUTER APPLY.

CROSS APPLY – używamy zazwyczaj do połączenia zbioru (np. tabeli) z dowolną funkcją tabelaryczną. Operator APPLY, pozwala przekazać jako parametr wejściowy, np. do funkcji tabelarycznej, wartość ze zbioru z lewej strony tego złączenia. Brzmi skomplikowanie? Jeden obraz (przykład) wart jest więcej niż 1000 słów.

Załóżmy, że chcemy wyświetlić detale zamówień i Klientów, podobnie jak w poprzednim przykładzie, ale dla wszystkich, pochodzących z Hiszpanii.

Jest ich pięciu :

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.Country  
FROM dbo.Customers c
WHERE Country = 'Spain'
ORDER BY c.CustomerID

Funkcje_tabelaryczne_SQL_03

Stosując operator łączenia wyrażeń tablicowych APPLY, możemy wykonać to zadanie, za pomocą jednej kwerendy. Kolejno dla każdego rekordu, ze zbioru stojącego po lewej stronie operatora, zostanie wywołana nasza funkcja.

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.Country  , TabZam.*
FROM dbo.Customers c 
                     CROSS APPLY                                   dbo.CustOrders(c.CustomerID) TabZam
WHERE Country = 'Spain'
ORDER BY c.CustomerID, OrderValue

Kolejno dla każdego pojedynczego wiersza z tabeli dbo.Customers, wykonywane jest złączenie typu CROSS JOIN. Stąd informacja o danym Kliencie jest połączona z każdy rekordem, zwracanym w danym wywołaniu przez naszą funkcję.

Funkcje_tabelaryczne_SQL_04

Zauważ że w wynikach brakuje Klienta o CustomerID = ‘FISSA’. CROSS APPLY – działa analogicznie do połączenia wewnętrznego INNER JOIN. Klient, nie złożył żadnych zamówień, funkcja dbo.CustOrders() nie zwróciła żadnego rekordu i dlatego identycznie jak w INNER JOIN, rekord został usunięty ze zbioru wynikowego.

Żeby otrzymać w wyniku również rekordy „niedopasowane” z tabeli po lewej stronie, trzeba użyć OUTER APPLY.

Działa analogicznie jak CROSS APPLY z tą różnicą, że jest odpowiednikiem połączenia zewnętrznego. Zatem podobnie jak w LEFT OUTER JOIN, na koniec przetwarzania, do zbioru wynikowego, dorzucone będą jeszcze wszystkie rekordy z tabeli po lewej stronie operatora.

Kwerenda uwzględniająca wszystkich Klientów, wyglądać będzie tak :

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.Country  , TabZam.*
FROM dbo.Customers c OUTER APPLY  dbo.CustOrders(c.CustomerID) TabZam
WHERE Country = 'Spain'
ORDER BY c.CustomerID, OrderValue

Funkcje_tabelaryczne_SQL_05

Jak widać sposób użycia i działanie złączeń bazujących na operatorze APPLY, jest proste i intuicyjne. Warto przy okazji dopowiedzieć, że za jego pomocą, możemy łączyć nie tylko funkcje ale też inne wyrażenia tablicowe – np. podzapytania.

Poprzednią kwerendę wykorzystującą funkcję tabelaryczną, można zapisać alternatywnie w ten sposób :

SELECT c.CustomerID, c.CompanyName, c.ContactName, c.Country  , TabZam.*
FROM dbo.Customers c OUTER APPLY  ( 
 -- zamiast funkcji, wyrażenie tabelaryczne
   SELECT o.CustomerID, o.OrderID,  o.OrderDate,
 CAST( SUM( UnitPrice * Quantity * (1-Discount)) as smallmoney) as OrderValue
   FROM dbo.Orders o inner join dbo.[Order Details] od on o.OrderID = od.OrderId
   WHERE o.CustomerID = c.CustomerID
   GROUP BY o.CustomerID, o.OrderID, OrderDate, ShipCity ) TabZam
 
WHERE Country = 'Spain'
ORDER BY c.CustomerID, OrderValue

Funkcje tabelaryczne złożone (multi-statement)

W sytuacjach, gdy potrzebujemy większej złożoności programistycznej, możemy wykorzystać funkcje typu Multi-Statement. Konstrukcja takiej funkcji jest odrobinę bardziej rozbudowana.

Poza parametrami, które definiujemy identycznie jak w pozostałych typach, obowiązkowa jest definicja zbioru wynikowego. Po słowie kluczowym RETURNS, nadajemy zwracanej tabeli nazwę, oraz określamy kolejno wszystkie jej atrybuty (kolumny). Podobnie jak w przypadku tworzenia zwykłej tabeli, kolumny muszą być opisane konkretnymi typami danych. Możemy przypisać im tutaj wartości domyślne, IDENTITY, czy klucz podstawowy.

W ramach przetwarzania takiej funkcji, musimy do tej wynikowej tabeli wrzucać jawnie (INSERT INTO) elementy.

Napiszmy praktyczny przykład takiej funkcji, która będzie dzieliła nam string na części (słowa). Kluczem podziału będzie separator, dowolnie określany przez użytkownika jako parametr.

Funkcja rozbijająca złączony tekst na kawałki, ma praktyczne zastosowanie. Przydaje się, gdy musimy poszatkować kolekcję przekazywanych argumentów lub dokonać podziału na etapie normalizacji.

Funkcję możemy zapisać przynajmniej na dwa sposoby. Na początek przykład obrazujący funkcje typu Multi-Statement czyli z logiką proceduralną.

CREATE  FUNCTION [dbo].[SplitString]
(
	@InputStr varchar(8000), 
	@Delimiter char(1) = ','
)
-- teraz definicja zbioru wynikowego
RETURNS @temptable TABLE 
(
	SingleStr varchar(8000)
) 
AS
BEGIN
 
	DECLARE @index int 
	DECLARE @tempStr varchar(8000) 
 
 
	SET @index = 1 
	IF LEN( @InputStr )<1 OR @InputStr IS NULL RETURN 
 
	WHILE @index != 0 
	BEGIN 
		SET @index = CHARINDEX(@Delimiter,@InputStr) 
 
		IF @index!=0 
			SET @tempStr = left(@InputStr,@index - 1) 
		ELSE 
			SET @tempStr = @InputStr 
 
		IF(LEN(@tempStr)>0)  -- tu piszemy do tabeli wynikowej
			INSERT INTO @temptable(SingleStr) VALUES(@tempStr) 
 
		SET @InputStr = right(@InputStr,LEN(@InputStr) - @index) 
		IF LEN(@InputStr) = 0 BREAK 
	END
RETURN
END

Z ciekawszych elementów tej konstrukcji jest jawne wrzucanie elementów do tabeli wynikowej (za pomocą INSERT INTO). W wyniku znajdą się tylko takie elementy, które zostaną w ten sposób dodane. Ostatecznie, słowem kluczowym RETURN, przerywamy działanie tej funkcji.

Sposób użycia – identyczny jak w przypadku prostych funkcji tabelarycznych. Na początek przykłady z dwoma różnymi separatorami (domyślny to przecinek) :

-- separator w postaci spacji
SELECT * FROM dbo.SplitString ('Ala ma kota',' ')
 
-- z separatorem domyślnym (przecinkiem)
SELECT * from dbo.SplitString ('12345,65489,23546',DEFAULT)

Funkcje_tabelaryczne_SQL_06

Teraz przykład użycia tej funkcji z operatorem APPLY. Potrzebnych będzie trochę danych testowych. Wygenerujemy tabelę tymczasową, która dla każdego Klienta będzie zawierała zagregowaną kolumnę z ostatnimi pięcioma zleceniami.

USE Northwind
GO
 
WITH Ord as 
(
	SELECT CustomerId, OrderId, 
               ROW_NUMBER() OVER(Partition BY CustomerId order by OrderId DESC) AS RN
	FROM dbo.Orders
)
SELECT o1.CustomerID, 
	STUFF((
 
		SELECT ',' + Cast(o2.OrderID as varchar )
		FROM Ord o2 
		WHERE o2.CustomerID = o1.CustomerID and o2.RN<=5
		FOR XML PATH('')
 
	),1,1,'') as Last5OrderNo into #tempdata
FROM  Ord o1
WHERE o1.RN <= 5
GROUP BY o1.CustomerID
 
SELECT * from #tempData
 
SELECT * from #tempData t CROSS APPLY dbo.[SplitString] (Last5OrderNo ,',')

Funkcje_tabelaryczne_SQL_07

Tabelaryczną funkcję dzielenia stringów na kawałki, można zapisać inaczej, z wykorzystaniem wspólnych wyrażeń tablicowych CTE. Jednak to rozwiązanie, pomimo że znacznie zgrabniejsze, okazuje się mniej efektywne.

Zresztą możesz samemu porównać :

CREATE FUNCTION [dbo].[SplitStringCTE]
(
	@InputStr varchar(8000), 
	@Delimiter char(1) = ','
)
 
RETURNS  TABLE 
AS
RETURN(
 
	WITH SplitString AS (
 
	SELECT
	  CASE WHEN CHARINDEX(@Delimiter, @InputStr)=0 THEN @InputStr
	   ELSE LEFT(@InputStr, CHARINDEX(@Delimiter, @InputStr)-1) END SPLITED,
	  CASE WHEN CHARINDEX(@Delimiter, @InputStr)=0 THEN NULL
	   ELSE RIGHT(@InputStr, LEN(@InputStr)-CHARINDEX(@Delimiter, @InputStr)) END TODO
 
	UNION ALL
 
	SELECT
	  CASE WHEN CHARINDEX(@Delimiter, TODO)=0 THEN TODO
	    ELSE LEFT(TODO, CHARINDEX(@Delimiter, TODO)-1) END SPLITED,
	  CASE WHEN CHARINDEX(@Delimiter, TODO)=0 THEN NULL
	    ELSE RIGHT(TODO, LEN(TODO)-CHARINDEX(@Delimiter, TODO)) END TODO
	FROM SplitString WHERE TODO IS NOT NULL
 
	)
	SELECT SPLITED FROM SplitString
 
)

Podsumowanie

Funkcje tabelaryczne oraz połączenia z operatorem APPLY, są dość często wykorzystywane w praktyce. W dyskusjach na temat wydajności, podobnie jak w przypadku podzapytań skorelowanych, pojawiają się skrajne stanowiska, że zawsze są kosztowne i powinno się ich unikać. Takie podejście mocno zniechęca do testowania alternatywnych rozwiązań, które nieraz okazują się całkiem efektywne.

Funkcje użytkownika

Funkcje użytkownika (User Defined Functions UDF) to obiekty programistyczne, które pozwalają znacząco rozszerzyć możliwości pisania kwerend.

Są one dość często stosowane – przedewszystkim w zapytaniach, dlatego postanowiłem umieścić ich opis w ramach tego kursu. Zakres funkcji w SQL, podobnie jak miejsc w których możemy je użyć jest naprawdę szeroki (procedury składowane, wartości domyślne, ograniczenia sprawdzające etc..). Rozdział ten ogranicza się tylko do funkcji użytkownika tworzonych w T-SQL w aspekcie pisania zapytań.

Obiekty te, podobnie jak w typowych językach programowania, mogą realizować rozmaite zadania. Używamy je do przetwarzania jakiś danych, celem uproszczenia zapisu kwerendy. Są opakowaniem logiki aplikacyjnej w wygodny obiekt i przeniesienie jej do warstwy serwera bazodanowego. Raz utworzone, mogą być stosowane w wielu miejscach.


Korzyści płynące ze stosowania funkcji użytkownika

  • poprawienie czytelności kodu i wygoda ich wielokrotnego użycia w różnych miejscach.
  • mogą zmniejszać ruch sieciowy – liczba danych (przesłanych bajtów) związanych z wywołaniem funkcji jest zazwyczaj znacznie mniejsza niż cała jej definicja, składowana na serwerze.
  • za pomocą funkcji CLR, można realizować zadania niemożliwe do wykonania w czystym T-SQL. Złożone obliczenia efektywniej jest wykonywać np. w obiektowych językach programowania.
  • mają możliwość wywołań rekurencyjnych.

Oczywiście jest też druga strona medalu. Funkcje zazwyczaj są kosztowne, szczególnie gdy wykonywane są dla każdego elementu niezależnie – skalarne (!). Z tego powodu bywają zmorą administratorów i są jednym z pierwszych punktów uwagi w momencie analizy wydajności zapytań. Ponadto dla funkcji tabelarycznych, szacowanie kardynalności zbioru zwykle jest wypaczone. Są traktowane jako black-box i przyjmowana wartość liczebności zbioru wynosi dla nich zawsze 1. Biorąc pod uwagę fakt, że za ich pomocą możemy wykonywać naprawdę skomplikowane działania – koszt ten czasem bywa akceptowalny. Jak widać odpowiedź na pytanie czy stosować funkcje czy nie, jak zwykle brzmi – to zależy ;)


Pisanie zapytań SQL z wykorzystaniem funkcji użytkownika

Funkcje ze swej definicji, mogą przyjmować parametry wejściowe i zwracają zawsze jakiś obiekt. W SQL, będzie to zawsze zbiór (tabela) lub wartość skalarna. Jeśli zaprotestujesz, że przecież wartość skalarna to też zbiór (jednoelementowy , opisany jedną kolumną) to znaczy, że uważnie przerabiałeś ten kurs lub po prostu dobrze czujesz koncepcję zbiorów.

Wyjątkowo w odniesieniu do funkcji, istnieje sztywny podział na takie, które zwracają wartości skalarne lub zbiory. Mamy więc do czynienia z dwoma głównymi rodzajami tych obiektów – odnajdziesz je w kategorii Programmability > Functions , w drzewie obiektów swojej bazy danych :

Funkcje_Uzytkownika_SQL

Ich strukturę, sposób tworzenia i przykłady zastosowań, opisuję szczegółowo w dedykowanych im artykułach :

  • FUNKCJE SKALARNE – zwracające pojedynczą wartość.
  • FUNKCJE TABELARYCZNE – zwracające zbiór. Wśród tych, rozróżniamy jeszcze dwa podtypy :
    • proste (inline table UDF)
    • złożone (multi-statement table valued functions)

Funkcje od strony programistycznej

Warto wiedzieć, że funkcje (zarówno wbudowane jak i użytkownika) możemy dodatkowo podzielić, ze względu na możliwość przewidzenia wyniku.

Deterministyczne to takie, które przyjmując określone parametry wejściowe, po każdym wywołaniu zwracają identyczny wynik. Są więc, można powiedzieć, z góry określone (łac. determinare – określić).
Przykładem takich funkcji jest większość wbudowanych matematycznych czy tekstowych, np. :

SELECT ABS(-2) as AbsVal, Left('Ala ma kota',3) as Lewy3
AbsVal      Lewy3
----------- -----
2           Ala

(1 row(s) affected)

Przy każdym wywołaniu funkcji wartości bezwzględnej ABS(), dla konkretnej wartości argumentu, równego np. -2, zwracany jest ten sam wynik. Podobnie jak w funkcji LEFT.

Z kolei funkcje niedeterministyczne, to takie, które pomimo tych samych wartości parametrów na wejściu, zwracają za każdym razem inny wynik. Przykładem może być funkcja NEWID(), generująca kolejny unikalny identyfikator czy GETDATE(), zwracająca datę i czas systemowy.

Select NEWID() as UniqueIdentifier, GETDATE() as DataCzas
UniqueIdentifier                     DataCzas
------------------------------------ -----------------------
EBF78558-F7E7-4A91-A1DA-17245C9CAA68 2014-03-06 09:31:25.893

(1 row(s) affected)

Co prawda powyższe, przykładowe funkcje nie przyjmują parametrów, ale przecież brak parametru to też konkretny przypadek wywołania.

Determinizm lub jego brak jest o tyle istotny, że w przypadku tych pierwszych, możliwe jest zastosowanie indeksów np. w widokach, których wartości kolumn są wyliczane na podstawie takich funkcji. Ponadto jest szansa, że funkcja deterministyczna zostanie wykonana tylko raz (np. GETDATE()) dla wszystkich rekordów.

Są to aspekty z zakresu projektowania i programowania baz danych i poruszam je w detalach na szkoleniach programistycznych.