Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

Common Table Expressions i iterativni ciklusi

Monday, 21.01.2008 – Zidar

Upotreba CTE i iterativni procesi

Kako bismo u MS SQL ispisali prvih 50 celih brojeva tako da svaki broj bude u posebnom redu?
1
2
3
…
50

Nešto ovako bi možda pomoglo:

DECLARE @i int 
SET @i = 0 
WHILE @i<=50 
BEGIN 
 SET @I = @i + 1 
 SELECT @i AS i 
 CONTINUE 
END

Nažalost, svaki SELECT se izvrsava za sebe:

          i 
----------- 
          1

(1 row(s) affected)

          i 
----------- 
          2

(1 row(s) affected)

          i 
----------- 
          3

Ovo je vec bolje:

DECLARE @i int 
SET @i = 0 
WHILE @i<=50 
BEGIN 
 SET @I = @i + 1 
-- SELECT @i AS i 
 PRINT @i 
 CONTINUE 
END

Međutim, s tim ne možemo ništa korisno da uradimo. Možda da rezultat cut/paste u Excel pa to da importujemo u SQL bazu, pa onda uradimo.. itd… itd….  Isuviše veliki posao za tako prostu stvar.

Na sreću, MS SQL 2005 ima Common Table Expressions. Probajte ovo:

WITH MyCTE AS 
(  
SELECT 1 AS i 
 UNION ALL 
SELECT i+1 
FROM MyCte 
WHERE i<=50 
) 
SELECT * FROM MyCTE

Posto je ovo CTE, imamo posla u stvari sa temp tabelom i rezultat se moze upotrebiti u nekom SQL izrazu na način na koji smo pokazali u jednom od prethodnih napisa.

Pre nego objasnimo kako ovo radi i šta se dešava, evo još jednog prostog primera. Zadatak je ispisati sve dane u zadatom mesecu. Mogu da probam ovako:

WITH Mesec AS -- za mesec Mart 2008 
( 
SELECT CAST('2008/03/01' AS datetime) AS Dan 
 UNION ALL 
SELECT Dateadd(day,1,Dan ) AS Dan 
FROM Mesec 
WHERE Dan <= CAST('2008/03/30' AS datetime) 
) 
SELECT * FROM Mesec

Možemo da uopštimo problem i da tražimo da se vrate svi datumi između dva zadata datuma (datumski opseg, ‘date range’) Ovako nekako bi moglo:

DECLARE @OdDatuma as datetime, @DoDatuma as datetime 
SET @OdDatuma = '2007/11/1' 
SET @DoDAtuma = '2007/11/30' 
 ; WITH OpsegDatuma AS -- ako WITH nije prva linija u batchu, stavlja se tacka zarez ispred 
 ( 
 SELECT @OdDatuma AS Datum 
  UNION ALL 
 SELECT Dateadd(day,1,Datum) AS Datum 
 FROM OpsegDatuma 
 WHERE Dateadd(day,1,Datum) <= @DoDatuma 
 ) 
 SELECT  * FROM OpsegDatuma

A možemo da napičemo i funkciju, OpesgDatuma, koja za zadata dva datuma vraća sve dane izmedju njih.

CREATE FUNCTION dbo.OpsegDatuma(@OdDatuma as datetime, @DoDatuma as datetime) 
RETURNS TABLE 
AS 
RETURN 
( 
 WITH OpsegDatuma AS 
 ( 
 SELECT @OdDatuma AS Datum 
  UNION ALL 
 SELECT Dateadd(day,1,Datum) AS Datum 
 FROM OpsegDatuma 
 WHERE Dateadd(day,1,Datum) <= @DoDatuma 
 ) 
 SELECT Datum FROM OpsegDatuma 
)

Sada može da se napiše i ovo:

SELECT * FROM dbo.OpsegDatuma('2007/11/1','2007/11/30')

ali i ovo:

SELECT 
 D.Datum 
 , COALESCE(COUNT(N.NarudzbaID),0) AS [Broj narudzbi na ovaj dan] 
FROM dbo.OpsegDatuma('2007/11/1','2007/11/30') AS D 
LEFT JOIN Narudzbe  AS N 
ON D.Datum = F.DatumNarudzbe 
GROUP BY D.Datum

Poslednji izraz vraća SVE datume u zadatom opsegu, pa i one kad nije bilo narudžbi.
Bez funkcije i bez Common Table Expression ovakav izraz bi bio veoma zahtevan za pisanje.

Vratimo se na pocčtak, da pokušamo da objasnimo šta se dešava. Izraz

WITH MyCTE AS 
(  
SELECT 1 AS i 
 UNION ALL 
SELECT i+1 
FROM MyCte 
WHERE i<=50 
) 
SELECT * FROM MyCTE

ima 3 dela, kao i svaki CTE.

WITH MyCTE AS

jeste deklaracija CTE izraza, gde je MyCTE ime koje smo dodelili.

Izraz u zagradi

(  
SELECT 1 AS i 
 UNION ALL 
SELECT i+1 AS i 
FROM MyCte 
WHERE i<=50 
)

je definicija naše temp tabele, šta će CTE da nam vrati.

Na kraju izrazom

SELECT * FROM MyCTE

pozivamo CTE.  Ništa novo u odnosu na ono što smo već ranije znali.

Izraz u zagradi je ono što nas interesuje. I tu imamo 3 dela:

SELECT 1 AS i

zove se ‘anchor member’, u slobodnom prevodu ‘prvi red CTE tabele’

UNION ALL

je deo sintakse i jednostavno tako mora  da se pise, tako trazi Microsoft
Treći deo je glavna stvar. Tu se dešavaju itaracije.

SELECT i+1 
FROM MyCTE 
WHERE i<=50

Zapazite FROM MyCTE. Izraz u zagradi je dakle unija dva seta, prvi deo vraća jedan red, a drugi iskaz poziva samu CTE tabelu tabelu. Otud rekurzija. Kao u matematici, funkcija poziva samu sebe.

Šta se dešava u pozadini? Anchor member vraća tačno jedan red. U tom momentu CTE ima taj jedan red. To se unionizuje sa izrazom

SELECT i+1 AS i 
FROM MyCTE

što vracć još jedan red, sa uvećanim i za 1, koliko smo stavili u SELECT i+1
Sada već imamo dva reda u tabeli, vrednsti su 1,2. Ciklus se nastavlja dok važi WHERE uslov.
Posto imamo i=2, to je manje od 50, ciklus se nastavlja dok ne stignemo do 50. Ne pitajte me zašto ni kako, ali tako to radi.

Mnogo detaljnije i lepše objasnjenje imate ovde:
http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/
I zapamtite ime Nigel Rivett. Ot tog gospodina se ima mnogo sta naučiti.

Jos neke važne stvari da na pomenem. WHERE nije obavezno i u tom slucčju će, teorijski, ciklus biti beskonačan. Pošto beskonačnost postoji samo u teoriji, Microsoft je to ograničio na 100. Ako dakle odradite

WITH MyCTE AS 
(  
SELECT 1 AS i 
 UNION ALL 
SELECT i+1 
FROM MyCte 
) 
SELECT * FROM MyCTE

dobićete 101 red. Kakav bi to alat bio da se ne može primeniti na više od 100 ciklusa?

Probajmo

WITH MyCTE AS 
(  
SELECT 1 AS i 
 UNION ALL 
SELECT i+1 
FROM MyCte 
WHERE i<=1000 
) 
SELECT * FROM MyCTE

i dobićemo grešku:

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

Microsoft je mislio i na to. Postoji klauzula option (maxrecursion N) koja se dodaje na kraj svega, da bi se povecao broj iteracija, ako treba do vrednosti N.

WITH MyCTE AS 
(  
SELECT 1 AS i 
 UNION ALL 
SELECT i+1 
FROM MyCte 
WHERE i<=1000 
) 
SELECT * FROM MyCTE 
option (maxrecursion 5000)

I ovde ima ograničenje. N u option (maxrecursion N) može da ide do 32767.
Svakako da bi i ovo u praksi bilo malo. Zasto samo 32767? Samo Bog zna, a posto Boga nema, onda ne zna niko (Ovu rečenicu izgovorio je Zoran Radmilovic kao Radovan III, ja nemam ništa stim i ne odgovaram na moguće komentare)
Postoji nedokumentovana opcija  (ili bar slabo dokumentovana) (maxrecursion 0)

WITH MyCTE AS 
(  
SELECT 1 AS i 
 UNION ALL 
SELECT i+1 
FROM MyCte 
WHERE i<=50000 
) 
SELECT * FROM MyCTE 
option (maxrecursion 0)

Ako ste probali da odradite poslednjih nekoliko primera, uočite kakvom brzinom se proizvede recimo 50,000 brojeva.
Postoje egzotični nacčni da se isti rezultat dobije jos brzž, ali meni je i manje od 1 sec za 50,000 redova dovoljno brzo.
Svi ostali načini kojih se obični smrtnici mogu setiti su za dva reda velicine sporiji od WITH sa rekurzijom.
Ako želite nešto brze od ovoga, pogledajte knjigu ‘Inside SQL Server 2005: T-SQL Querying’, autori Itzik Ben-Gan, Lubor Kollar i Dejan Sarka. Treći autor je očigledno naš covek, a i Lubor nije daleko, tek da se zna 🙂

Igrajte se neko vreme sa slicnim primerima, i svakako posetite sajt na kome piše Nigel Rivett. Kad svarimo ove ‘jednostavne’ upoterbe CTE izraza, pokušaću da se pozabavim hijerarhijama i mrežama.

 Ono što smo videli do sada čini mi se definitivno ubija potrebu za kursorima.