SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Generowanie zbioru – wektor dyskretnych i ciągłych wartości

W praktyce pisania zapytań SQL, nieraz spotkałem się z potrzebą wygenerowania automatycznej tabeli. Na przykład osi czasu z uwzględnieniem każdego dnia albo wektora kolejnych liczb z zadanego przedziału.

W artykule tym, zademonstruję jak wygenerować za pomocą CTE, zbiór zawierający inkrementowane elementy z określonego przedziału.

Generowanie wektora danych za pomocą CTE

Zastosowań takiego ciągłego, dyskretnego zbioru jest wiele. Rozważmy taki scenariusz.

Chcę utworzyć raport, zagregowanej sumy wszystkich złożonych zamówień w zadanym przedziale dat. Istotnym dla mnie jest zachowanie skali czasu – tak aby pokazane były na niej wszystkie dni z zadanego okresu. Również te, w których nie spłynęło ani nie zostało zrealizowane żadne zamówienie. Chcę także na tym samym wykresie pokazać informacje o realizacji zamówień.

To co potrzebuję na początek, to tabeli w postaci wektora ze wszystkimi kolejnymi dniami np. od ‚2014-02-01’ do ‚2014-02-14’. Do takiego zbioru (osi czasu) będę odnosił się analizując już szczegółowo daty zamówień.

Można stworzyć taką tabelę za pomocą wielu operacji UNION (niewygodne). Można też ładować w pętli wartości do tabeli tymczasowej. Nie są to jednak „ładne” i uniwersalne sposoby.

Do utworzenia takiego ciągłego zbioru, idealnie nadaje się rekurencyjne CTE. W SQL Server 2012, można za ich pomocą tworzyć nieograniczone (rekursywnie) zbiory. Opisuję w detalach strukturę rekurencyjnych CTE w ramach kursu SQL.

Kwerenda, która wygeneruje interesujący mnie zbiór, będzie wyglądała tak :

WITH Daty AS
(
	-- Rekurencyjne Common Table Expression 
          -- domyślnie max 100 przebiegów rekurencyjnych, czyli będzie działało 
          -- do 2014-05-12 :) potem trzeba użyć MAXRECURSION
 
	SELECT cast('2014-02-01' as SmallDatetime) as Data , 1 as Liczba
 
	UNION ALL
 
	SELECT Data+1, Liczba+1
	FROM Daty a
	WHERE a.Data < getdate()-1
 
)
SELECT * FROM Daty

CTE_wektor_wartosci

Szczególnie fajną rzeczą w CTE, jest możliwość korzystania z nich w różnych strukturach – np. widokach. Tworzenia tabel tymczasowych i technik programistycznych nie wpleciemy w zwykły widok.

Rekurencyjne CTE są domyślnie ograniczone do 100 przebiegów. Możemy jednak sterować ich maksymalną liczbą, stosując opcję MAXRECURSION.

Parametr ten przyjmuje wartości od 0 (nieograniczona ilość przebiegów) do 32767. Tworzenie np. zbioru (wektoru) z 1000 kolejnych liczb, wymaga zastosowania opcji MAXRECURSION. W przeciwnym razie, po wykonaniu setnego (domyślnie) przebiegu rekurencyjnego, otrzymamy błąd :

Msg 530, Level 16, State 1, Line 3
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Zatem jeśli potrzebujemy większy wektor, na przykład wspomniany 1000-elementowy, to utworzymy go w ten sposób :

with Liczby as 
(
	select  1 as Liczba
	UNION ALL
	select Liczba+1
	from Liczby a where a.Liczba < 1000
 
)
Select * from Liczby
OPTION (MAXRECURSION 0)
Liczba
-----------
1
2
3
. . .
998
999
1000

(1000 row(s) affected)

Wróćmy jednak do naszego scenariusza. Skrypt generujące przykładowe dane, z których korzystam w dalszej części znajdziesz tutaj.

Kwerenda odnosiła się będzie bezpośrednio do utworzonego wektora czasu (pierwsze CTE – Daty). Jest on główną osią na której pokazywać będę zagregowane i narastające sumy zleceń.

with Daty as 
(
	SELECT cast('2014-02-01' as date) as Data 
 
	UNION ALL
 
	SELECT DateAdd(dd,1,a.Data)
	from Daty a
	where a.Data < getdate()-1
 
),
Orders_raport as (
 
	SELECT a.Data,  count(distinct o.Order_id) as Orders_QTY, 
	     SUM(count(distinct o.Order_id)) OVER(order by a.Data)  as Agg_Orders_Qty,
	     count(distinct o2.Order_id) as Comp_Orders_QTY, 
	     SUM(count(distinct o2.Order_id)) OVER(order by a.Data)  as Agg_Comp_Order_qty
	FROM Daty a 
	     left join dbo.Orders o on a.Data =  cast(o.create_date as date) 
                         and cast(o.create_date as date) > '2014-01-31'
	      left join dbo.Orders o2 on a.Data = cast(o2.completion_date as date) 
                         and cast(o2.create_date as date) > '2014-01-31'
	GROUP BY a.Data
 
) 
SELECT *
FROM Orders_raport
ORDER BY  DATA

CTE_wektor_wartosci2

Teraz na podstawie tak przetworzonych danych, łatwo np. w Excelu wygenerować wykres – co należy podkreślić – w ciągłej dziedzinie czasu.

CTE_wektor_wartosci3

Leave a comment

Twój adres email nie zostanie opublikowany.

Uzupełnij równanie (SPAM protection) *