SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Skalarne funkcje użytkownika

Funkcje skalarne, zwracają zawsze pojedynczą wartość określonego typu. Z tego względu, można je stosować w różnych miejscach np.:

  • w SELECT tworząc wyrażenia, czy pojedyncze kolumny wynikowe.
  • budując warunki filtracji w WHERE, HAVING lub złączeń we FROM.
  • w definicjach tabel – określając wartości domyślne kolumn lub ograniczeniach sprawdzających (CHECK CONSTRAINT)
  • w innych obiektach programistycznych.

W artykule tym przedstawiam sposób tworzenia i zastosowanie skalarnych funkcji użytkownika w pisaniu zapytań SQL.


Stosowanie skalarnych funkcji użytkownika

Najlepiej wyjaśnić sens funkcji na praktycznym przykładzie.

Stwórzmy funkcję, która będzie zwracała liczbę dni roboczych pomiędzy dwoma datami.

W pracy zawodowej szczególnie raportując np. efektywność procesów musiałem po takie informacje sięgać.

Na początek wersja okrojona, aby nie komplikować zbytnio tematu i skupić się na czystej konstrukcji oraz sposobie użycia. W naszym uproszczeniu, zakładam że liczba dni roboczych pomiędzy datami, to różnica w dniach pomiędzy datą START i END, minus liczba sobót i niedziel występujących w tym przedziale czasu.

Za pomocą T-SQL możemy zapisać ten wzór w następujący sposób (dla przykładu liczba dni roboczych w marcu 2014):

SELECT	(DATEDIFF(dd, '2014-03-01', '2014-03-31') + 1)
	-(DATEDIFF(wk, '2014-03-01', '2014-03-31') * 2)
   	-(CASE WHEN DATENAME(dw, '2014-03-01') = DATENAME(dw,6) THEN 1 ELSE 0 END)
	-(CASE WHEN DATENAME(dw, '2014-03-31') = DATENAME(dw,5) THEN 1 ELSE 0 END)
       as WorkingDays
WorkingDays
-----------
21

(1 row(s) affected)

Jak widać, wzór ten nie uwzględnia żadnych innych dni wolnych (świąt) poza sobotami i niedzielami. W pełni funkcjonalną wersję, uwzględniającą dodatkowe dni wolne oraz szczegółowe wyjaśnienie powyższego wzoru znajdziesz tutaj.

Skoro już wiemy jak obliczyć liczbę dni roboczych, zastosujmy tą wiedzę w praktyce.
Napiszmy kwerendę, która zwróci z bazy Northwind informacje o zleceniach, które były realizowane dłużej niż 20 dni roboczych. Pokażemy informacje o tych zleceniach oraz o czasie ich realizacji dniach roboczych.

USE Northwind 
GO
 
SELECT OrderId, OrderDate, ShippedDate, (DATEDIFF(dd, OrderDate, ShippedDate) + 1)
	-(DATEDIFF(wk, OrderDate, ShippedDate) * 2)
   	-(CASE WHEN DATENAME(dw, OrderDate) = DATENAME(dw,6) THEN 1 ELSE 0 END)
	-(CASE WHEN DATENAME(dw, ShippedDate) = DATENAME(dw,5) THEN 1 ELSE 0 END) as WorkingDays
FROM dbo.Orders
WHERE   (DATEDIFF(dd, OrderDate, ShippedDate) + 1)
	-(DATEDIFF(wk, OrderDate, ShippedDate) * 2)
   	-(CASE WHEN DATENAME(dw, OrderDate) = DATENAME(dw,6) THEN 1 ELSE 0 END)
	-(CASE WHEN DATENAME(dw, ShippedDate) = DATENAME(dw,5) THEN 1 ELSE 0 END) > 20
ORDER BY WorkingDays DESC
OrderId     OrderDate  ShippedDate WorkingDays
----------- ---------- ----------- -----------
10660       1997-09-08 1997-10-15  28
10777       1997-12-15 1998-01-21  28
10924       1998-03-04 1998-04-08  26
…
10788       1997-12-22 1998-01-19  21
10840       1998-01-19 1998-02-16  21
10978       1998-03-26 1998-04-23  21

(35 row(s) affected)

Przyznasz, że samo zapytanie jest długaśne i mało czytelne. Biorąc pod uwagę fakt, że informacja o liczbie dni roboczych pomiędzy datami, może się przydać wielokrotnie, warto utworzyć z tego wzoru funkcję użytkownika. Zresztą tylko w tym przykładzie, powtarzam obliczenia według tego długaśnego wzoru dwukrotnie – w WHERE oraz w SELECT.


Tworzenie i modyfikacja funkcji skalarnych

Funkcje tworzymy, modyfikujemy lub usuwamy za pomocą klasycznych konstrukcji komend DDL (Data Definition Language). Służą do tego polecenia CREATE, ALTER lub DROP FUNCTION.

Tworząc nową funkcję, można skorzystać z szablonu, dostępnego w Management Studio (prawy strzał na podkatalogu z funkcjami skalarnymi > New Scalar-Valued Function).

Funkcje_Skalarne_SQL_UDF_01

W jej definicji, po nazwie, mamy możliwość określenia parametrów wejściowych. Są opcjonalne i można ich zdefiniować całkiem sporo – max. 1024. Następnie po słowie kluczowym RETURNS, koniecznie musi znaleźć się deklaracja typu, zwracanej wartości np. całkowitej (RETURNS int).

Właściwe ciało funkcji, umieszczamy w bloku BEGIN …. END. Słowem kluczowy RETURN wywołujemy jej zakończenie – jawne zwrócenie podanej za nim wartości.

Definicję naszej nowej funkcji możemy zapisać w ten sposób :

  CREATE FUNCTION dbo.LiczbaDniRoboczych 
  (   
       -- Funkcja nie uwzględnia świąt, innych dni wolnych poza sobotą i niedzielą
       -- określenie parametrów wejściowych – są opcjonalne
       -- u nas konieczne są dwa, określające zakres dat
      @StartDate datetime,
      @EndDate datetime
  )
  -- określenie typu zwracanej wartości (to obowiązkowo)
  RETURNS int 
  AS 
  -- ciało funkcji 
  BEGIN
 
	RETURN  (DATEDIFF(dd, @StartDate, @EndDate) + 1)
	        -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
   	        -(CASE WHEN DATENAME(dw, @StartDate) =  DATENAME(dw,6) THEN 1 ELSE 0 END)
    	        -(CASE WHEN DATENAME(dw, @EndDate) =  DATENAME(dw,5) THEN 1 ELSE 0 END)
 
  END;

Nasza funkcja zawiera dwa parametry – początek i koniec zakresu dat. W wyniku jej działania, zwracana jest wartość liczbowa całkowita – typ integer.


Wywoływanie funkcji skalarnych

Korzystanie ze skalarnych funkcji użytkownika jest bardzo proste, ale trzeba pamiętać o kilku zasadach. Przede wszystkim wywołujemy je zawsze za pomocą nazwy przynajmniej dwuczłonowej, zawierającej schemat (u nas jest to dbo) w którym została utworzona.

Dzięki zastosowaniu funkcji, nasza długaśna kwerenda zostanie zgrabnie odchudzona do takiej postaci :

SELECT OrderId, OrderDate, ShippedDate, 
	dbo.LiczbaDniRoboczych(OrderDate, ShippedDate) as WorkingDaysFROM dbo.Orders
WHERE dbo.LiczbaDniRoboczych(OrderDate, ShippedDate) > 20ORDER BY WorkingDays DESC

Zauważ, że funkcja ta jest deterministyczna i z uwagi na swoją prostotę (nie sięga do żadnych innych zbiorów), jej wydajność przetwarzania będzie identyczna jak w przypadku umieszczenia całej logiki w zapytaniu. Nie ponosimy, więc żadnych dodatkowych kosztów a znacznie łatwiej zarządzać takim kodem.


Wartości domyślne

Funkcje skalarne mogą mieć określone wartości domyślne dla parametrów wejściowych. Używa się ich inaczej niż w przypadku procedur składowanych. Wywołując taką funkcję musimy jawnie deklarować chęć użycia wartości domyślnej za pomocą słowa DEFAULT.

W kolejnym przykładzie, utworzymy funkcję, której zadaniem będzie konwersja liczby dziesiętnej na postać liczbową o innej podstawie.

Domyślnie będzie konwertowała zadaną liczbę na postać binarną (podstawa 2). Można też będzie podać jawnie inną podstawę (parametr @Podstawa) w zakresie 2-10.

CREATE FUNCTION dbo.KonwersjaDziesietnej
(
	@LiczbaDoKonwersji bigint,
	@Podstawa int = 2 -- domyślnie na binarną
)
RETURNS varchar(1000)
AS
BEGIN
	-- funkcja działa poprawnie tylko dla podstawy <2-10>
	IF NOT (@Podstawa between 2 and 10) RETURN -1
 
	DECLARE @Wynik varchar(1000) = ''
 
	WHILE @LiczbaDoKonwersji > 0 BEGIN
 
		SET @Wynik = @Wynik + CAST((@LiczbaDoKonwersji % @Podstawa) AS varchar)
		SET @LiczbaDoKonwersji = @LiczbaDoKonwersji / @Podstawa
 
	END
 
	RETURN REVERSE(@Wynik)
 
END
 
 
-- domyślnie czyli z DEFAULT konwersja liczby dziesiętnej na binarną
SELECT dbo.KonwersjaDziesietnej (54,DEFAULT) as Bitowo

Funkcje_Skalarne_SQL_UDF_02
Zwróć uwagę, że tym razem pokazałem ciut większą złożoność programistyczną, którą możemy się posługiwać w definicjach funkcji. Poprzednio całość była zawarta w jednym poleceniu zwracającym wartość – RETURN(). W tym przykładzie wykonuję szereg „skomplikowanych” obliczeń :) dla zwracanej przez tą funkcję wartości.

Zaprezentowane powyżej funkcje, wykonywały stosunkowo proste przekształcenia, nie sięgając właściwie do żadnych zbiorów (tabel). Oczywiście możemy wykonywać za ich pomocą znacznie bardziej złożone operacje.


Porównanie wydajności funkcji użytkownika z innymi metodami

Napiszemy teraz funkcję, która dla danego Klienta będzie zwracała wartość średnią jego zleceń. Porównamy wydajność jej działania z zapytaniem skorelowanym oraz zwykłym łączeniem tabel.

Chcemy wyświetlić zlecenia naszych Klientów, których wartość jest większa niż średnia liczona dla każdego z nich osobno.

Zadanie takie realizować może na kilka sposobów, np. za pomocą podzapytania skorelowanego :

-- Query 1 – z podzapytaniem skorelowanym
SELECT  o1.CustomerID, o1.OrderID , SUM(od1.UnitPrice * od1.Quantity) as OrdValue
FROM dbo.Orders o1 INNER JOIN dbo.[Order Details] od1 on o1.OrderID = od1.OrderID
GROUP BY o1.CustomerID, o1.OrderID
HAVING SUM(od1.UnitPrice * od1.Quantity) > 
(
	SELECT  AVG( OrdValue )  -- obliczenie średniej
	FROM 
	(
	   SELECT o2.CustomerID, o2.OrderID, SUM(od2.UnitPrice * od2.Quantity) as OrdValue
	   FROM dbo.Orders o2 INNER JOIN dbo.[Order Details] od2 on o2.OrderID = od2.OrderID
	   WHERE o2.CustomerID = o1.CustomerID -- dla zleceń danego Klienta
	   GROUP BY o2.CustomerID, o2.OrderID
	) a 
)

Jednym z kluczowych problemów tego zadania, jest obliczanie średniej wartości zleceń, dla każdego z Klientów osobno. Napiszmy więc funkcję obliczającą taką wartość, która będzie przyjmowała jako parametr identyfikator Klienta.

CREATE FUNCTION dbo.CustomerAvgOrderValue
(
	@CustomerID nchar(5)
)
RETURNS decimal(10,2)
BEGIN	
      RETURN ( 
	SELECT AVG( OrdValue )  -- obliczenie średniej
	FROM 
	(
	    SELECT SUM(od2.UnitPrice * od2.Quantity) as OrdValue
	    FROM dbo.Orders o2 INNER JOIN dbo.[Order Details] od2 
                                              ON o2.OrderID = od2.OrderID
	    WHERE o2.CustomerID = @CustomerID -- dla zleceń danego Klienta
	    GROUP BY o2.OrderID
	) a 
       )
END

Teraz możemy przepisać pierwszą kwerendę, na taką, która korzysta z nowo utworzonej funkcji. Będzie z pewnością bardziej czytelna, ale sprawdźmy co stanie się z wydajnością.

-- Query 2 – z użyciem funkcji skalarnej 
 
SELECT o1.CustomerID, o1.OrderID , SUM(od1.UnitPrice * od1.Quantity) as OrdValue 
FROM dbo.Orders o1 INNER JOIN dbo.[Order Details] od1 ON o1.OrderID = od1.OrderID
GROUP BY o1.CustomerID, o1.OrderID
HAVING SUM(od1.UnitPrice * od1.Quantity) > dbo.CustomerAvgOrderValue(o1.CustomerID)
go

Na pierwszy rzut oka, analizując plany wykonania i statystykę odczytów, włączone za pomocą polecenia

SET STATISTICS IO ON

wydaje się że poprawiliśmy dzięki niej wydajność (i to znacznie) :

Funkcje_Skalarne_SQL_UDF_03
Funkcje_Skalarne_SQL_UDF_04

Jeśli przyjrzymy się czasowi wykonania kwerendy i analizie statystyk IO za pomocą np. Profilera lub Extended Events, wynik okaże się znacznie gorszy. Co więcej widać że zdarzają się sytuację w których nie można polegać na prezentowanym planie wykonania zapytania i tylko dogłębna analiza wydajności doprowadzi nas do prawdy o tym co jest grane. Więcej na ten temat znajdziesz w artykule dotyczącym pomiarów wydajności zapytań.

CREATE EVENT SESSION queryperf ON SERVER 
ADD EVENT sqlserver.sql_statement_completed 
ADD TARGET package0.event_file(SET filename=N'D:\PerfTest\PerfTest.xel',
	max_file_size=(2),max_rollover_files=(100)) 
WITH (  MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS, 
             MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB, 
             MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON); 
 
ALTER EVENT SESSION [queryperf] ON SERVER STATE = START; 
 
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT
 
SELECT  /* Query1 */ o1.CustomerID, o1.OrderID , SUM(od1.UnitPrice * od1.Quantity) as OrdValue
FROM dbo.Orders o1 INNER JOIN dbo.[Order Details] od1 on o1.OrderID = od1.OrderID
GROUP BY o1.CustomerID, o1.OrderID
HAVING SUM(od1.UnitPrice * od1.Quantity) > 
(
	SELECT  AVG( OrdValue )  -- obliczenie średniej
	FROM 
	(
	   SELECT o2.CustomerID, o2.OrderID, SUM(od2.UnitPrice * od2.Quantity) as OrdValue
	   FROM dbo.Orders o2 INNER JOIN dbo.[Order Details] od2 on o2.OrderID = od2.OrderID
	   WHERE o2.CustomerID = o1.CustomerID -- dla zleceń danego Klienta
	   GROUP BY o2.CustomerID, o2.OrderID
	) a 
)
 
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT
 
SELECT /* Query2 */ o1.CustomerID, o1.OrderID , SUM(od1.UnitPrice * od1.Quantity) as OrdValue
FROM dbo.Orders o1 INNER JOIN dbo.[Order Details] od1 ON o1.OrderID = od1.OrderID
GROUP BY o1.CustomerID, o1.OrderID
HAVING SUM(od1.UnitPrice * od1.Quantity) > dbo.CustomerAvgOrderValue(o1.CustomerID)
 
DROP EVENT SESSION queryperf ON SERVER; 
 
SELECT *  
FROM   ( 
       SELECT  duration=e.event_data_XML.value('(//data[@name="duration"]/value)[1]','int') 
       ,       cpu_time=e.event_data_XML.value('(//data[@name="cpu_time"]/value)[1]','int') 
       ,       physical_reads=e.event_data_XML.value('(//data[@name="physical_reads"]/value)[1]','int') 
       ,       logical_reads=e.event_data_XML.value('(//data[@name="logical_reads"]/value)[1]','int') 
       ,       writes=e.event_data_XML.value('(//data[@name="writes"]/value)[1]','int') 
       ,       statement=e.event_data_XML.value('(//data[@name="statement"]/value)[1]','nvarchar(max)') 
       ,       TIMESTAMP=e.event_data_XML.value('(//@timestamp)[1]','datetime2(7)') 
       ,       * 
       FROM    ( 
               SELECT CAST(event_data AS XML) AS event_data_XML 
               FROM sys.fn_xe_file_target_read_file(N'D:\PerfTest\PerfTest*.xel', NULL, NULL, NULL) 
               )e 
       )q 
WHERE  q.[statement] LIKE '%Query%'  
ORDER  BY q.[timestamp] ASC

Extended_Test


Podsumowanie

Funkcje skalarne przede wszystkim poprawiają czytelność i wygodę pisania zapytań. Korzystać z nich należy z rozwagą, bo zazwyczaj wykonywane będą dla każdego rekordu niezależnie. Czasem analiza wydajności i wykorzystania ich, wymaga dokładnego sprawdzenia w jaki sposób silnik wykonuje naszą kwerendę. Pochopne wnioski (dziękuję @GRUM za erratę :)) mogą prowadzić do błędnych wniosków i w konsekwencji poważnych problemów wydajnościowych.

Pozostałe użyteczne funkcje skalarne SQL

W SQL Server, oprócz opisanych w poprzednich artykułach, funkcjach daty i czasu, tekstowych czy matematycznych, istnieje wiele innych kategorii. Związane mogą być one z rozszerzeniami analitycznymi SQL (funkcje analityczne w SQL), bezpieczeństwem czy wyciąganiem informacji o strukturach i środowisku (metadane).

Opisywanie ich wszystkich w ramach kursu SQL dla początkujących mija się z celem. Chciałbym zwrócić Twoją uwagę na kilka dodatkowych, służących weryfikacji typu danych.

Dzięki nim możesz dokonywać podstawowego sprawdzenia typu danych, np. podczas importu z zewnętrznych źródeł (plików, innych baz danych).


Pierwszą z nich jest funkcja ISDATE ( atrybut ) – zwraca true (1) – jeśli wartość jest datą lub istnieje możliwość niejawnej konwersji na datę. Jej działanie obrazuje poniższy przykład.

-- tylko dwa ostatnie przypadki Exp5 i 6 nie są datami
-- choć obie mogą nimi być np. Exp 5 YYYYDDMM
 
SELECT ISDATE( getdate() ) as Exp1,
       ISDATE( '2013-01-02' ) as Exp2,
       ISDATE( '20130102' ) as Exp3,
       ISDATE( '2013/01/02' ) as Exp4,
       ISDATE( '20131402' ) as Exp5,
       ISDATE( '2013 01 02' ) as Exp6

FN_ISDATE
Używać jej możesz zarówno w SELECT jak i w warunkach WHERE (żeby wyfiltrować tylko takie rekordy, które mają wpisaną datę a nie jakieś inne ciągi znakowe).


Drugą funkcją sprawdzania typu danych jest ISNUMERIC ( atrybut ) – działa analogicznie jak poprzednia. Zzwraca wartość true, jeśli badany atrybut (stała, zmienna, wartość danej kolumny), jest liczbą lub może zostać niejawnie przekonwertowana na liczbę.

-- jeśli jest możliwa niejawna konwersja na dowolny typ liczbowy
-- ISNUMERIC zwróci wartość true
 
SELECT ISNUMERIC('133') as Exp1,
       ISNUMERIC('133 ') as Exp2,
       ISNUMERIC('133.1') as Exp3,
       '133' + 123 as Exp4,
       ' 133 ' + 123.32 as Exp5,
       ' 133.1' - 132.1 as Exp6

FN_ISNUMERIC_01
Zauważ, że niektóre z tych stringów, nie do końca są poprawnymi liczbami (spacje). Jeśli jest to możliwe, silnik bazy danych wykona na nich niejawną konwersję typu na liczbowy, dlatego możliwe jest wykonanie na nich operacji matematycznych bez użycia CAST / CONVERT.

Z tą funkcją wiążą się jednak pewne zagrożenia. Jej działanie jest rozszerzone także na wartości „monetarne”. Akceptuje pewne znaki, których później nie można niejawnie przekonwertować np. na postać float, decimal czy int. Zwraca true również dla wartości zawierających takie symbole jak , . $ E + – etc..

SELECT ISNUMERIC ( '-,') as Result

FN_ISNUMERIC_02
Skoro jest to liczba, to dlaczego nie można zrobić tak :

SELECT '-,' + 10
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '-,' to data type int.

Z drugiej strony, możemy najpierw przekonwertować naszą „problematyczną” liczbę na wartość typu money / smallmoney a dopiero następnie wykonywać na niej operacje matematyczne. To zadziała.

SELECT CAST('-,' as smallmoney) + 10 as Result

FN_ISNUMERIC_03


Na koniec, jeszcze jedna funkcja DATALENGTH ( atrybut ), która zwraca informację o faktycznej liczbie bajtów, ile zajmuje dana wartość.

Możemy przekonać się, jaka jest różnica pomiędzy ciągiem znaków zapisanych standardowo na 8 bitach (1bajt na każdy znak) versus kodowanie w UNICODE (stąd przedrostek nvarchar, nchar oraz N przed stringami) na dwóch bajtach, dla każdego znaku. Zauważ, że funkcja LEN() zwraca informację o liczbie znaków (nie bajtów).

SELECT  DATALENGTH(  'Ala ma kota' ) , 
	DATALENGTH( N'Ala ma kota' ) as DataLen,
	LEN (  'Ala ma kota' ) as ASCIILen, 
	LEN ( N'Ala ma kota' ) as UNICODELen

FN_DATALENGTH

Funkcje matematyczne w SQL

Wbudowane, skalarne funkcje matematyczne, służą do operacji na liczbach. Poniżej znajdziesz opis kilku wybranych, najczęściej używanych.

ROUND ( wartość_liczbowa, precyzja ) – zaokrągla wartość liczbową, zmiennoprzecinkową do zadenj precyzji.

USE AdventureWorks2008
GO
 
SELECT SalesOrderId, TotalDue, 
       ROUND(TotalDue,2) as TotalDueRounded 
FROM Sales.SalesOrderHeader
WHERE TotalDue BETWEEN 123 AND 124

FN_Maths_01_ROUND


RAND () – funkcja pseudolosowa, zwraca liczbę typu float z zakresu 0-1. Łatwo w oparciu o nią stworzyć generator liczb pseudolosowych zwracający liczby losowe z określonego przedziału.

-- przedział 10 - 20
SELECT 10 + CONVERT(INT, (20-10+1) * RAND())
-- Przedział 0-10
SELECT 0 + CONVERT(INT, (10-0+1) * RAND())

Poniżej przykład skryptu w którym celem sprawdzenia działania generatora, wrzucam do tabeli tymczasowej #test, 1000 losowych wartości z przedziału 1-100. Na koniec sprawdzam MAX, MIN i średnią z tych wartości.
Średnia powinna dążyć do 51 – im więcej rekordów tym bliżej tej wartości (zakładając rozkład jednorodny).

IF (OBJECT_ID('tempdb.dbo.#test') is not null ) drop table dbo.#test;
 
CREATE TABLE dbo.#test
( 
	wart int
)
GO
 
SET NOCOUNT ON
GO
 
DECLARE @min int,@max int
-- określenie zakresu przedziału
SELECT @min = 1, @max =100
 
INSERT INTO dbo.#test(wart)  
-- losujemy liczby całkowite z zakresu 1-100
 
SELECT @min + CONVERT(INT, (@max - @min +1) * RAND())
GO 1000
 
SELECT MAX(wart) as MaxValue, MIN(wart) as MinValue, 
	AVG(cast(wart as float)) as Avarage, COUNT(*) as IleLiczb 
FROM dbo.#test

FN_Maths_02_RAND

Istotną cechą funkcji RAND() jest jej determinizm. Wywołana w pojedynczym batchu (logicznej jednostce), zwraca wartość losową identyczną dla każdego wiersza (podobnie jak getdate()).

Funkcja RAND() może przyjmować parametr, będący „ziarnem losowości”. Jeśli potrzebujesz wygenerować wartość losową dla KAŻDEGO wiersza w jednym zapytaniu, trzeba sprawić aby funkcja stała się niedeterministyczna.

with tabelka as
(
	select 1 as kol
	union
	select 2 
	union 
	select 3 
)
SELECT kol, RAND() as Rnd, getdate() as Dt, RAND(CHECKSUM(NEWID())) as RowRand
FROM tabelka

Niedeterministryczne_RAND

Temat liczb pseudolosowych jest szeroki i informacje tu zawarte są jedynie prostym przykładem ich zastosowania.


FLOOR( liczba_ułamkowa ) – to zaokrąglenie dowolnej liczby zmiennoprzecinkowej w dół – biorąc pod uwagę jej wartość ułamkową. Analogicznie działa CEILING( liczba_ułamkowa ) – zaokrągla zawsze do pełnej wartości w górę, niezależnie od wartości części po przecinku.

SELECT FLOOR (123.78) , FLOOR (-123.78) , CEILING(123.78),  CEILING(-123.78)

FN_Maths_02_FLOOR


POWER (liczba, potęga) oraz SQRT(liczba) to funkcje potęgowania oraz pierwiastkowania (kwadratowego)


Poza wyżej wymienionymi funkcjami, SQL udostępnia także wbudowane funkcje trygonometryczne, logarytmiczne i kilka innych o których możesz poczytać w np. w BOL czy na stronach MSDN.

Istotne jest aby pamiętać, że SQL to język operowania na zbiorach danych. Jeśli potrzebujesz wykonywać skomplikowane obliczenia – powinieneś wykorzystać do tego celu możliwości języków programowania do tego celu stworzonych. Dzięki integracji CLR w SQL, możesz tworzyć funkcje, procedury w środowisku .NET i wywoływać je bezpośrednio z kodu T-SQL.

Funkcje daty i czasu

Kolejną istotną grupą wbudowanych funkcji skalarnych są obiekty związane z przetwarzaniem typów danych daty i czasu.

YEAR ( data ), MONTH ( data ), DAY ( data ) – dokonują ekstraktu z daty, odpowiednio roku, miesiąca oraz dnia.

-- ekstrakt poszczególnych części daty
 
SELECT  YEAR ( '2013-02-12' ) as Rok,
        MONTH( '2013-02-12' ) as Miesiac,
        DAY  ( '2013-02-12' ) as Dzien

FN_datetime_01


Najczęściej stosowane funkcje zwracające date i czas systemowy to GETDATE() oraz SYSDATETIME(). Są to funkcje nie przyjmujące żadnych argumentów i zwracają po prostu bieżącą datę i czas systemowy.

-- funkcje zwracające aktualny czas i datę systemową
 
SELECT SYSDATETIME(), 
       SYSDATETIMEOFFSET(),
       GETDATE(),
       GETUTCDATE()

FN_datetime_02


DATEADD ( datepart, liczba, data ) – dodaje (lub odejmuje) liczbę jednostek daty/czasu określonych za pomocą datepart np dni (day, dd, d), lat (years,yy,yyyy), miesięcy (month,mm,m), minut (minute,mi,n) etc. do zadanej daty. Jednostki określone mogą być za pomocą pełnej nazwy, lub skrótu. Pełny ich opis znajdziesz tutaj. Stosowana często w warunkach filtracji, np. wszystkie zlecenia z ostatnich 14 dni. Funkcja DATEADD, jest też bardzo użyteczna w określaniu zakresów

-- DATEADD - dodawanie/odejmowanie jednostek określonego typu z zadanej daty
 
SELECT  DATEADD ( dd,-DAY( GETDATE()-1 ), GETDATE() ) as FirstDayCurrMonth,
	DATEADD ( dd,-DAY( GETDATE() ), GETDATE() ) as LastDayPrevMonth

FN_datetime_03


DATEDIFF ( datepart, startdate, enddate ) – różnica pomiędzy dwiema datami (end – start) wyrażona w jednostkach określonych przez datepart. Wiek pracowników :

USE Northwind
GO
 
-- DATEDIFF - określanie różnicy wyrażonej w konkretnych jednostkach 
-- pomiędzy dwiema datami
 
SELECT  FirstName, LastName, BirthDate, 	
	DATEDIFF ( yy , BirthDate , GETDATE() )  as Age
FROM dbo.Employees

FN_datetime_04


DATEPART( datepart, data ) – wyciąga określoną parametrem datepart, jednostkę podanej daty.

-- DATEPART - ekstrakt określonej części daty /czasu
 
SELECT  DATEPART( yy, GETDATE() ) as CurrentYear,
	DATEPART( mm, GETDATE() ) as CurrentMonth,
	DATEPART( dd, GETDATE() ) as CurrentDay,
	DATEPART( ww, GETDATE() ) as CurrentWeek

FN_datetime_05


DATENAME ( datepart, data ) – pododbna do DATEPART, zwraca wartość znakowa, określonej parametrem datepart, części daty w tym nazwę dnia tygodnia, miesiąca zgodnie z ustawieniami @@LANGID (bieżący język dla sesji)

SELECT  DATENAME(dw, GETDATE() ) as DzienTygodnia,
	DATENAME(mm, GETDATE() ) as Miesiac

FN_datetime_06


Funkcje tekstowe w SQL

Przekształcenia ciągów znakowych (stringów) w SQL

Funkcje operujące na ciągach znaków, są jednymi z najczęściej stosowanych. Ich nazewnictwo jest intuicyjne i często bardzo podobne lub nawet identyczne, ze stosowanym w innych językach programowania.

W tym artykule przedstawiam najczęściej używane funkcje związane z przetwarzaniem wartości ciągów znakowych typu (‚n)char/varchar.


LEFT( exp, n ) oraz RIGHT( exp, n ) – to jedne z najprostszych funkcji tekstowych. Zwracają n znaków od lewej lub prawej z wyrażenia exp na którym działa.

USE Northwind
GO
 
-- LEFT/RIGHT - n znaków z lewej / prawej strony
 
SELECT LEFT(FirstName,1) + '. ' + LastName as FullName,  
       RIGHT(HomePhone,8) as ShortPhone,
       FirstName, HomePhone 
FROM dbo.Employees

FN_Strings_01_LEFT
W tym przykładzie funkcja LEFT(FirstName,1) zwraca zawsze sensowną wartość – inicjał od imienia. Z kolei RIGHT (HomePhone,8) – zwróci zawsze tylko ostatnie 8 znaków. Jeśli byśmy chcieli wyciągnąć zmienną liczbę znaków np. od lewej (numer kierunkowy) – potrzebujemy wsparcia kolejnej funkcji, która nam namierzy ten zmienny punkt.


CHARINDEX ( exp1 , exp2 , start_location ) – szuka pierwszego wystąpienia ciągu znaków exp1 w wartości znakowej podanej jako argument exp2. Rozpoczyna poszukiwania od pozycji 1 (jeśli nie jest podana) lub innej określonej przez start_location. Zwraca numer znaku w którym rozpoczyna się pierwsze wystąpienie poszukiwanego stringu.

Połączmy więc wiedzę dotyczącą funkcji LEFT oraz CHARINDEX, do wyciągniecia informacji o numerze kierunkowym z kolumny HomePhone :

-- CHARINDEX namierza pierwsze wystąpienie znaku w stringu (zwraca numer)
 
SELECT CHARINDEX (' ', HomePhone) as FirstSpace,
       LEFT(HomePhone, CHARINDEX (' ', HomePhone)-1 ) as Direction,
       HomePhone
FROM dbo.Employees

FN_Strings_02_charindex
W zagnieżdzaniu funkcji trzeba pamiętać o jednej zasadzie. Funkcja zagnieżdzona musi zwrócić odpowiedni typ danych dla argumentu funkcji zewnętrzenej. W tym przypadku funkcja CHARINDEX – zwróciła wartość typu integer, która jest oczekiwanym typem danych, drugiego argumentu funkcji LEFT (określającym liczbę znaków).


UPPER (string) oraz LOWER(string) zamienia wszystkie litery na duże lub małe.


LEN( exp ) – funkcja zwraca wartość typu integer, równej liczbie znaków (długości) wyrażenia będącego jej argumentem. Często stosowana z innymi funkcjami – np. SUBSTRING.

-- UPPER / LOWER  - WIELKIE/ male litery, LEN - długość stringu
 
SELECT ProductName, UPPER( ProductName ) as UpperName, 
	LOWER( ProductName ) as LowerName, 
        LEN( ProductName ) NameLength
FROM dbo.Products
WHERE LEN( ProductName ) < 10

FN_Strings_03_UPPER
Zauważ, że zastosowałem tutaj funkcję LEN m.in. w warunku filtracji WHERE. Trzeba być świadomym, że takie zastosowanie, spowoduje jej uruchomienie dla każdego zwróconego rekordu dwukrotnie. Po raz pierwszy, zostanie ona wykonana dla każdego wierszy tabeli dbo.Products (w WHERE). Drugi – tylko dla tych których nazwa jest krótsza niż 10 znaków (w SELECT).

Stosowanie funkcji w warunkach filtracji może mieć znaczący wpływ na wydajność zapytań (co nie oznacza, że nie możemy ich tu stosować) – więcej o tym w ostatnim rozdziale tego kursu.


SUBSTRING ( exp , start_location , n ) – zwraca fragment tekstu, liczbę n znaków z wyrażenia podanego w parametrze exp, startując od zadanego miejsca start_location. Napiszmy zapytanie wyciągające tylko nazwę domeny z pełnego adresu URL. W tym celu musimy wyciągnąć fragment tekstu z pominięciem http:// (od ósmego znaku) do pierwszego wystąpienia znaku / (licząc również od ósmej pozycji)

-- SUBSTRING - wyciąga konrketny framgent stringu  
 
SELECT  WebPage, 
	SUBSTRING(WebPage,8, CHARINDEX('/',webPage,8)-8 ) as OnlyDomain
FROM 
(
        -- potraktuj to jak tabelę z dwoma wierszami ;)
	SELECT 'http://www.sqlpedia.pl/pisanie-zapytan-w-jezyku-sql-kurs/' as WebPage
	UNION
	SELECT 'http://sqlpedia.pl/kurs-sql/' 
 
) as Pages

FN_Strings_04_SUBSTRING
Łącząc ze sobą podstawowe funkcje, można osiągnąć całkiem skomplikowane przekształcenia.


RTRIM ( exp ) oraz LTRIM ( exp ) – obcięcie z prawej/lewej znaków spacji danego wyrażenia exp. Szczególnie często spotykane przy łączeniu stringów typu CHAR o stałej długości.

CREATE TABLE #tabelka
(
	opis char(10)
);
 
INSERT INTO #tabelka VALUES('jeden'),('dwa'),(' trzy'),(' cztery ');
 
-- LTRIM / RTRIM usuwa spacje z lewej / prawej strony
 
SELECT '!' + opis + '!' as Laczenie, 
       '!' + LTRIM( RTRIM(opis) ) + '!'  as Trimowane
FROM #tabelka;
 
DROP TABLE  #tabelka;

FN_Strings_05_TRIM


PATINDEX ( wzorzec, exp ) – wyszukiwanie pierwszego wystąpienia doapsowania maski – określonej wzorcem w danym wyrażeniu exp. Maska jest tworzona na tej samej zasadzie co porównywanie ciągów znakowych za pomocą operatora LIKE. Napiszmy zapytanie, wyciągające kod pocztowy ze stringu.

-- PATINDEX wyszukuje dopasowania wzorca w stringu 
-- i zwraca numer znaku, pierwszego wystąpienia
 
SELECT Adres, 
	   PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9]%',Adres) as KodPocztowyStart,
	   SUBSTRING(Adres,PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9]%',Adres), 6 ) as KodPocztowy
FROM
(
	-- potraktuj to jak regularną tabelę ;)
	SELECT '60-144 Poznań' as Adres 
	UNION
	SELECT 'Poznań, 60-186'
	UNION 
	SELECT 'Kod pocztowy 61-698, Poznań' 
	UNION 
	SELECT 'Poznań 61-698, Jana Pawła 16' 
 
) as Adresy

FN_Strings_06_PATINDEX
Pełną składnię tworzenia wzorców w LIKE, znajdziesz w artykule na temat filtrowania wartości znakowych w WHERE.


REPLACE ( exp , old_substring , new_substring ) – podmienia każde wystąpienie ciągu znaków old_substring na new_substring w przeszukiwanym wyrażeniu exp. Poniższy przykład, zamieni wszystkie odnalezione wystąpienia znaku „-„ na ciąg pusty czyli „”. Zauważ, że argumentem wejściowym funkcji REPLACE, będzie wynik działania RIGHT – czyli wartość kolumny HomePhone, obcięta do ośmiu znaków od prawej.

-- REPLACE podmienia wszystkie wystąpienia szukanego ciągu znaków, na inny
 
SELECT HomePhone , REPLACE ( RIGHT(HomePhone,8) , '-', '') as ShortPhone
FROM dbo.Employees

FN_Strings_07_REPLACE


REVERSE ( exp ) – odbicie lustrzane stringu czyli ostatni będą pierwszymi.

-- REVERSE - lustro
 
SELECT REVERSE('aidepLQS') as Mirror

FN_Strings_08_REPLACE


STUFF ( exp1, start, n, exp2 ) –usuwa n znaków wyrażenia exp1 zaczynając od pozycji określonej parametrem start. Następnie wrzuca określony ciąg znakowy exp2 do wyrażenia exp1, również od pozycji określonej w start.

-- STUFF kasuje wskazany fragment ciągu znakowego i wkleje zadany wewnątrz innego
 
SELECT STUFF('Agnieszka Maria Kwiatkowska', 3, 5, 'XXXXX');

FN_Strings_09_STUFF