SQL CTE neboli Common table expression je zjednodušeně dočasná tabulka (lépe řečeno result set) v podobě výrazu, po jehož deklarování pomocí klauzule WITH se na něj můžeme odkázat v SELECT, INSERT, DELETE nebo UPDATE skriptu.
Syntaxe SQL CTE s WITH
WITH Alias_Dotazu AS (
SELECT Sloupec1, Sloupec2
FROM dbo.Tabulka
)
SELECT *
FROM Alias_Dotazu;
Pomocí klauzule WITH definujeme alias (“Alias_Dotazu”) jako výsledek dotazu a na tento alias se následně můžeme odkázat. Při složitějších operacích tímto odpadají složité vnořené dotazy nebo ukládání průběžných výsledků do několika tempových tabulek. Výsledné skripty jsou tak mnohem elegantnější, lépe čitelné a kratší.
CTE je ale potřeba používat s rozumem, pokud pracujeme nad velkým objemem atomických dat a provádíme opravdu složité operace, bude CTE pomalejší než použití tempové tabulky. CTE není totiž na rozdíl od uložení do #tempu materializováno.
Příklad SQL CTE s WITH klauzulí – nerekurzivní dotaz
Mějme situaci, kdy máme tabulku s tržbami a chtěli bychom zobrazit TOP 10 dní dle tržeb. Nejprve je potřeba udělat ranking po dnech a poté vybrat top 10 dnů.
Dotaz by vypadal takto
WITH Poradi_Trzeb_Po_Dnech AS (
SELECT
DATEKey,
ROUND(SUM(Amount),2) AS SUM_Amount,
RANK() OVER (ORDER BY SUM(Amount) desc) AS Poradi
FROM AdventureWorksDW2014.dbo.FactFinance
GROUP BY DATEKey
)
SELECT *
FROM Poradi_Trzeb_Po_Dnech
WHERE Poradi<=10;
Nejprve pomocí t-sql CTE WITH aliasujeme dotaz, který orankuje všechny tržby po jednotlivých dnech sestupně a následným dotazem potom vybíráme TOP 10 dnů spolu s tržbami.
Na UPDATE, INSERT, DELETE můžete jít úplně stejně jako v příkladu, jen vyměníte SELECT a použijete správnou syntaxi.
Btw pokud by nás napadlo dotazovat se na TOP 10 viz. níže, bude výsledkem chyba. Proto jsem zvolil tento příklad, je to vcelku typické využití. Určitě jich ale najdete mnohem více
Dotazovat se na window funkce takto je chyba, proto jsme použili t-sql CTE s WITH:
SELECT
DATEKey,
ROUND(SUM(Amount),2) AS SUM_Amount,
RANK() OVER (ORDER BY SUM(Amount) desc) as Poradi
FROM AdventureWorksDW2014.dbo.FactFinance
GROUP BY DATEKey
HAVING RANK() OVER (ORDER BY SUM(Amount) desc) <= 10;
Výsledkem skriptu bude chyba: Windowed functions can only appear in the SELECT or ORDER BY clauses
Rekurzivní dotazy si nechám na někdy jindy, hierarchie jsou velké téma a určitě zaberou celý článek.