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
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
Teraz na podstawie tak przetworzonych danych, łatwo np. w Excelu wygenerować wykres – co należy podkreślić – w ciągłej dziedzinie czasu.
