CTE = Common Table Expression

Általános jelenség scriptelés közben, hogy valahol felbukkan egy subquery, pl. a “minden számla, ahol a vevő budapesti” típusú kérdésre. Ez lehet join vagy subquery, általában mindegy, a query optimizer ugyanazt a végrehajtási tervet produkálja belőlük.

-- a subquery
SELECT * FROM Szamlak
WHERE 
   vevoid in
   (SELECT vevoid FROM Vevo Where varos = 'Budapest')
-- a join
SELECT s.* FROM Szamlak s
   JOIN Vevo v
   ON v.vevoid = s.vevoid
   WHERE v.varos = 'Budapest'

És vannak esetek, amikor még ez sem elég, azokra a nehéz napokra ott van a CTE, vagyis Common Table Expression. Mielőtt belemennék, hogy mi is ez, nézzük meg a fenti példát CTE-vel, ami teljesen értelmetlen, de legalább a szintaktikát felismerjük:

WITH BpVevok (vevoid)
AS (
   SELECT vevoid FROM Vevo Where varos = 'Budapest'
)
SELECT s.* FROM Szamlak s
   JOIN BpVevok
   ON BpVevok.vevoid = s.vevoid

Semmivel nem tűnik egyszerűbbnek, nem is gyorsabb, akkor meg mire jó? A CTE nem más, mint tuljadonképpen egy ideiglenes nézet, egy olyan view, amit a query elején a WITH clause-zal definiálunk, aztán a queryben meg felhasználjuk, akár többször is. Ez az egyik előnye, hogy újrahasznosítható, és akkor viszont már egyszerűség meg gyorsaság is felbukkan. Viszont amiért én pénteken hozzányúltam, az valami egészen más tulajdonsága: szereti a rekurziót is, és lehet “rekurzív nézeteket” definiálni benne. Ez nagyon jól jön például hierarchikus adatoknál, amikor van egy rekordazonosító meg egy szülőazonosító oszlop. De nézzük inkább, hogy én mire használtam, az talán jobb, mintha értekeznék.

Adott egy tábla, amiben fájlszerverről szóló adatok vannak, többek között: objektumazonosító; fájl, illetve könyvtárnév; projekt; lejárati idő; plusz a szülő könyvtár azonosítója. minta:
id|fajlnev|projektid|lejarat|szuloid
12234|feladatok.doc|4334|2011-12-31|3654
3654|output|4334|2012-01-31|1111
1111|teszt|4334|2020-01-20|NULL

A teljes feladathoz pedig az egyik részfeladat az, hogy minden fájlnak kell a teljes elérési útvonala is, amit subquerykben/joinokban kell majd használni, tehát pl.
12234|feladatok.doc|\teszt\output\feladatok.doc|4334|2011-12-31|3654

Akár a gyökérelem azonosítóját is hozzáadhatnánk, nem bonyolítjuk a helyzetet. Transact-SQL-ben ez egy érdekes feladat lenne CTE nélkül, írhatnék rekurzív függvényt például, de a CTE sokkal cukibb:

WITH FileCTE (id, fajlnev,eleresiut,projektid,lejarat,szuloid)
AS (
  SELECT id, fajlnev, '\' + fajlnev AS eleresiut, projektid, lejarat, szuloid
  FROM filetabla
   WHERE szuloid IS NULL
   UNION ALL
   SELECT f.id, f.fajlnev, c.eleresiut + '\' + f.fajlnev AS eleresiut, f.projektid, f.lejarat, f.szuloid
   FROM filetabla f
     JOIN FileCTE c
     ON c.id = f.szuloid
)
-- itt jön az igazi kelérdezés
SELECT f.id, f.projektid 
FROM filetabla f
JOIN FileCTE c
ON f.id = c.id
-- join meg egy csomo minden...
WHERE c.eleresiut LIKE '%penzugyi terv%xls'

Hát erre jó a CTE. (Meg arra, hogy maga a kveri már átlátható, mert a szörnyű subquery-k ki vannak emelve az elejére… :)

One Comment

  1. Balássy György:

    Az újrafelhasználhatóság terén az a nagy gondom vele, hogy csak a WITH utáni első query látja. (Vagy rosszul tudom?) Ha többször kell, akkor marad a view, vagy a table valued function.

    De rekurzióra tényleg kiváló. És a példa is jó, köszi a cikket.

Leave a comment