Úvod do datové analýzy - lekce 12

SQL6 - Pohledy a CTE

Tato část je zaměřena na pohledy a jejich alternativy. Po dokončení této části byste měli mít přehled o tom, co jsou pohledy a CTE a jak je lze využít k psaní přehlednějšího kódu.

Pohledy

Pohledy jsou v zásadě uložené dotazy (velmi zjednodušeně). Důvodů, proč pohledy využívat, je celá řada. Jedním z nejčastějších je psaní přehlednějšího kódu. Představte si například, že potřebujete často pracovat s tržbami podle státu a výrobce. V takovém případě je vhodné vytvořit si pohled, jehož řádky obsahují vždy údaje o státu, výrobci a tržbách tohoto výrobce v daném státě. Takový pohled by vypadal následovně:

CREATE VIEW StateManufacturerView AS
(
SELECT
c.State,
m.Manufacturer,
SUM(s.Revenue) AS TotalRevenue
FROM
Sales s
JOIN Country c ON s.Zip = c.Zip
JOIN Product p ON s.ProductID = p.ProductID
JOIN Manufacturer m
ON p.ManufacturerID = m.ManufacturerID
GROUP BY
c.State,
m.Manufacturer
)

Jakmile máte tento pohled k dispozici, můžete velmi snadno psát různé dotazy, které ho využívají. Pokud budete například chtít zjistit pět nejvýdělečnějších výrobců na Floridě, stačí jednoduše zapsat

SELECT TOP 5
Manufacturer,
TotalRevenue
FROM
StateManufacturerView
WHERE
State = N'FL'
ORDER BY
TotalRevenue DESC

Jak vidíte, je tento dotaz skutečně jednoduchý. Stejného efektu byste mohli dosáhnout i úpravou původního SELECTu obsahujícího spojení čtyř tabulek. Takový dotaz by ovšem byl podstatně méně přehledný. Další důvody, proč používat pohledy, jsou uvedeny ve videu.

POZOR: To, že je dotaz přehlednější a jednodušší, neznamená, že není výpočetně náročný. Při provedení takového dotazu musí SQL Server na pozadí stále provést spojení čtyř tabulek, seskupení atd. Při používání dotazů je třeba mít to na paměti a kontrolovat, zda například pro dotaz, který by šel napsat jednoduše, nevyužíváte zbytečně složitý pohled. To platí obzvláště u vnořených pohledů, kdy může výpočetní náročnost růst opravdu extrémně.

CTE

Common Table Expressions (CTE) jsou alternativou k pohledům. Umožňují nám obalit výsledek dotazu do jakési „virtuální tabulky“. Tato „tabulka“ existuje pouze po dobu provádění dotazu a nemá v rámci databáze žádné perzistentní uložení. Můžete ji však využít k provádění dalších dotazů. To je obzvláště vhodné v případě, že lze problém, pro který píšete dotaz, rozdělit do několika navazujících kroků. Jako příklad můžeme použít poslední dotaz ze samostatných úkolů z předchozí části. Zadání zní: „Kteří výrobci neprodali od 1.1.2015 žádný výrobek?“ Řešení této otázky můžeme rozdělit na dvě části. V první části najdeme všechny výrobce, kteří od 1. 1. 2015 nějaký výrobek prodali. Tento výsledek obalíme do CTE a s pomocí této „tabulky“ následně z tabulky výrobců vybereme ty, kteří se v mezivýsledku nenachází. Výsledný dotaz by tedy vypadal následovně:

;WITH T AS
(
   SELECT
       Manufacturer
   FROM
       Manufacturer m
       JOIN Product p ON m.ManufacturerID = p.ManufacturerID
       JOIN Sales s ON s.ProductID = p.ProductID
   WHERE
       s.Date >= '2015-01-01'
   GROUP BY
       Manufacturer
)
SELECT
   m.Manufacturer
FROM
   Manufacturer m
   LEFT JOIN T ON m.Manufacturer = T.Manufacturer
WHERE
   T.Manufacturer IS NULL

Výsledek tohoto dotazu je stejný jako při použití kombinace LEFT a RIGHT JOINu a pro řadu lidí může být tento zápis čitelnější. Nevýhodou může být vyšší výpočetní náročnost, protože CTE nemohou obsahovat indexy, které mohou výpočet výrazně urychlit. V takovém případě může být výkonově výhodnější využít kombinaci levého a pravého vnějšího spojení. O indexech se dozvíte více v následující části.

Další materiály

Pokud se chcete dozvědět více o tabulkových proměnných, dočasných tabulkách a CTE, využijte následující zdroje.

CTE - https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql
Tabulkové proměnné - https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql
Dočasné tabulky (v článku v části Temporary Tables) - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql

Kontrolní otázky

1. Pohledy nám umožňují …
2. obalit dotaz a uložit jej v databázi
3. dostat se k datům, k nimž nemáme přístup
4. bez práce zrychlit provádění dotazu
5. CTE …
6. jsou trvale uloženy v databázi
7. existují po dobu trvání relace
8. existují pouze po dobu běhu dotazu
9. Pohledy lze vytvářet i nad již existujícími pohledy.
10. Ano
11. Ne

‍