V dnešní mňamce se podíváme na to, jak si usnadnit život při psaní komplexnějších queries, a hlavně jak jej usnadnit komukoliv, kdo po nás bude kód číst.

Je přirozené, že každému při psaní kódu vyhovuje jiný styl, existují však dva extrémy, kterým bychom se měli vyhnout.

Za prvé vytváření nesčetného množství tzv. temp tabulek, které slouží jen pro uložení dočasných výsledků z mezikroků a pro zjednodušení logiky sql transformací. Výledkem bývá dlouhý a nepřehledný kód, a vzniká problém při pojmenování tabulek (tmp1…, tmp2…, …, tmp8…, final…).

Opačným extrémem je snaha napsat celou logiku v jednom řádku, přeužívání window funkcí a subqueries.

Klauzule with nám pomůže napsat komplexní kód, který je přehledný a snadno čitelný. Pojďme si syntax ukázat rovnou na příkladu.

Máme tabulku s výsledky studentů ze srovnávacích testů. Zajímá nás, kteří studenti mají lepší hodnocení, než je průměr jejich třídy. Pomocí with vytvoříme virtuální dataset avg_trida, ve kterém vypočteme průměr pro každou třídu a který použijeme v hlavní query pro porovnání hodnocení studenta s průměrem jeho třídy. Po doběhnutí této query dataset avg_trida přestane existovat (nezůstane uložený v paměti).

WITH
--- vytvořím dočasný dataset s průměry pro jednotlivé třídy
avg_trida AS
	(
	SELECT
		trida,
		avg(hodnoceni) AS avg_hodnoceni_tridy
	FROM vysledky_studentu
	GROUP BY trida
	)
-- v hlavní query porovnám hodnocení s průměry napočtenými výše
SELECT
jmeno,
hodnoceni,
CASE WHEN hodnoceni > t.avg_hodnoceni_tridy THEN 1 ELSE 0 END AS vetsi_nez_avg_tridy
FROM vysledky_studentu v
LEFT JOIN avg_trida t -- připojení tmp datasetu
ON v.trida = t.trida

Výstupem je tato tabulka:

Alternativou by bylo oddělit výpočet průměru třídy do tmp tabulky, použití window funkce nebo použití subquery:

SELECT
jmeno,
hodnoceni
CASE WHEN hodnoceni > t.avg_hodnoceni_tridy THEN 1 ELSE 0 END AS vetsi_nez_avg_tridy
FROM vysledky_studentu v
LEFT JOIN
	(SELECT
	trida,
	avg(hodnoceni) AS avg_hodnoceni_tridy
	FROM vysledky_studentu
	GROUP BY trida) t
ON v.trida = t.trida

Kód s vnořeným selectem musíme číst “odspodu”, abychom věděli, co se jak napočítalo, nicméně u takto jednoduchého příkladu zatím největší výhody použití with nejdou moc vidět.

Proto zkusme přidat pořadí tříd podle průměrného hodnocení jejich žáků a porovnání hodnocení žáků s průměrem všech hodnocení.

WITH
---- spočítám průměr pro každou třídu
avg_trida AS
(
SELECT
trida,
avg(hodnoceni) AS avg_hodnoceni_tridy
FROM vysledky_studentu
GROUP BY trida
),
---- vyhodnotím pořadí tříd podle jejich průměru spočetného v query výše
trida_poradi AS
(
SELECT
trida,
RANK() OVER(ORDER BY avg_hodnoceni_tridy ASC) AS poradi_tridy
FROM avg_trida
),
---- spočítám průměr všech žáků
avg_all AS
(
SELECT
jmeno,
avg(hodnoceni) OVER () AS avg_hodnoceni_all
FROM vysledky_studentu
)
---- hlavní query
SELECT
vs.jmeno,
hodnoceni,
CASE WHEN hodnoceni > atr.avg_hodnoceni_tridy THEN 1 ELSE 0 END AS vetsi_nez_avg_tridy,
CASE WHEN hodnoceni > aa.avg_hodnoceni_all THEN 1 ELSE 0 END AS vetsi_nez_avg_all,
tp.trida,
tp.poradi_tridy
FROM vysledky_studentu vs
LEFT JOIN avg_trida atr
ON vs.trida = atr.trida
LEFT JOIN avg_all aa
ON vs.jmeno = aa.jmeno
LEFT JOIN trida_poradi tp
ON vs.trida = tp.trida