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

MS SQL Common Table Expressions

Friday, 04.01.2008 – Zidar

MS SQl 2005 doneo je nekoliko značajnih novih rešenja. Jedna od novosti je upotreba Common Table Expressions (CTE). CTE su u stvari virtualne temp tabele. Šta ovo znači?

Pokušaću da pokažem na primeru o čemu se radi. Ne jednom forumu neko je postavio sledeće pitanje:

Zadate su tabele:

Partner(partner_sif#, ime, prezime)
Faktura(faktura_br#, partner_sif, faktura_datum_fakt)
Stavke_fakture(faktura_br#,roba_sif#,faktura_kolicina, faktura_cijena)

Traži se SQL upit koji za rezultat daje partnera s kojim je ostvaren najveći promet.

Pretpostavimo da je promet = suma(kolicina*cena)

Pretpostavka je da postoji tačno jedan partner sa najvećim prometom.

Zadatak bi se logički mogao razbiti na tri dela, ovako:

1. utvrditi koliki je promet ostvario svaki partner
2. izabrati najveći od svih prometa
3. utvrditi ko je partner sa najvećim prometom

Da krenemo:

Utvrdimo  koliki je promet ostvario svaki partner, is a;uvajmo SELECT kao view

CREATE VIEW v_Promet
AS
SELECT
partner_sif#
     , Promet = SUM(faktura_kolicina * faktura_cijena)
FROM Stavke_fakture

Sada utvrdimo koji je to promet najveći. Evo još jedan view, koji poziva onaj što smo napravili u prvom koraku.

CREATE VIEW v_NajveciPromet
AS
SELECT
     NajveciPromet = MAX(Promet)
FROM v_Promet

Sada ćemo utvrditi ko je partner sa najvećim prometom

SELECT
     partner_sif#
     , Promet
FROM v_Promet AS P
JOIN v_NajveciPromet AS M
ON M.NajveciPromet = P.Promet

Za sada sve izgleda relativno jasno. Nažalost,  DBA ne dozvoljava da kreiramo views, pa sve mormo da strpamo nekako u jedan SQL iskaz. Tu se stvari komplikuju. Rezultujući kveri mogao bi da izgleda ovako:

SELECT
        P.partner_sif#
        , P.Promet
FROM
        (SELECT
partner_sif#
, Promet = SUM(faktura_kolicina * faktura_cijena)
        FROM Stavke_fakture
         ) AS P
JOIN
        (SELECT
NajveciPromet = MAX(Promet)
FROM v_Promet
) AS M
ON M.NajveciPromet = P.Promet

Samo sam zamenio nazive view-a odgovarajucim SELECT iskazima. Ovo se zove upotreba in-line tabela.

Konacni SQL iskaz nije naročito komplikovan, ali nema baš previše ljudi koji mogu ovakav iskaz da napisu iz cuga. A dovoljno je nečitljiv, i pored truda uloženog u formatiranje.

SQL 2005 donosi rešeneje – CTE (Common Table Expressions). CTE su virtualne tabele, koje se definišu i žive u jednom baču. U našem slučaju, ono sto su bili views, postaće CTE.

Ovako to izgleda u našem zadatku:

WITH  CTE_Promet AS
(SELECT
    partner_sif#
     , Promet = SUM(faktura_kolicina * faktura_cijena)
FROM Stavke_fakture
)
,  CTE_NajveciPromet AS
(
SELECT
     NajveciPromet = MAX(Promet)
FROM CTE_Promet
)
SELECT
     partner_sif#
     , Promet
FROM CTE_Promet AS P
JOIN CTE_NajveciPromet AS M
     ON M.NajveciPromet = P.Promet

Mozete da zakljucite da sintaksa ide nekako ovako:

WITH
CTE_1 AS
(
SELECT koji definise CTE_1
)
, CTE_2 AS
(
SELECT koji definise CTE_2
ovde moze da se poziva prethodno definisani CTE
)
, CTE_X AS
(
SELECT koji definise CTE_X, koji
moze da poziva sve prethodno definisane CTE
)
SELECT f( CTE_1,  CTE_2,  f(CTE_X), završni izraz poziva bilo koji CTE)

Dakle, počinjemo službenom reči WITH. Onda definisemo jedan ili vise CTE virtualnih tabela. Svaki od prethodno definisanih može se upotrebit u definisanju sledećih. Na kraju pišemo izraz koji koristi bar jedan od definisanih CTE..

Na ovaj način se mnogi komplikovani izrazi sa subkverijima postaju mnogo jasniji. Ovakav jedan izraz, koji počinje sa WITH i definiše CTE može slobodno da se sačuva kao view, ili da se upotrebi u stored proceduri. Ako je u stored proceduri, svaki CTE može da koristi  koje god hoćete parametre.

CTE je dakle neka vrsta temp tabele koja postoji u jednom baču.

Postoji i jedna druga upotreba CTE, kad CTE sam sebe poziva i rezultat je sličan onome što proizvode iterativne petlje DO WHILE i kursori. Na ovaj način potreba za kursorima se svodi na minimum. Ako tržite HELP za CTE, videćete primer gde se CTE koristi kao alat za iterativno procesiranje hijerarhijskog skupa. Primer nije preterano jasan pa ću u neko skorije vreme pokušati da nešto napišem o tome.

🙂