SQLShack (Čeština)

V několika předchozích článcích této série nastavili jsme základy, jak vytvořit zprávu. Uděláme o krok dále a uvidíme, jak používat operátor tabulky PIVOT na serveru SQL Server. Začneme od jednoduchého dotazu a pomalu postupujeme k dynamickému SQL a PIVOT. Začněme.

Datový model a obecná myšlenka

Datový model, který použijeme, je stejný, jaký používáme v této sérii.

Naším dnešním úkolem je vytvořit zprávu (pomocí operátoru SQL Server PIVOT), kde bude každé město v samostatném řádku a kde spočítáme počet všech výsledků volání souvisejících s každým městem. Proto všechny výsledky (všechny hodnoty ze slovníku) budou v naší zprávě sloupci.

Protože „obrázek má hodnotu tisíce slov“, popíšeme s ním také náš konečný cíl.

Abychom toho dosáhli, budeme muset použít data ze 4 tabulek call, call_outcome, customer a city. bylo by dobré, kdybyste se podrobněji podívali na níže uvedený model a analyzovali, jak tyto tabulky souvisejí.

Začneme zkoumáním údajů v tabulkách a při každém dotazu se posuneme o krok blíže k požadovaný výsledek (dotaz). Zde použitý přístup je stejný jako přístup použitý v Learn SQL: Vytvořit sestavu ručně pomocí článku s dotazy SQL.

Kategorie a data sestavy

první sada dotazů, kterou začneme, je ta, která zkoumá data aktuálně přítomná ve všech 4 tabulkách, které budeme muset použít k vytvoření zprávy. Budeme to dělat, abychom mohli potvrdit, že konečná zpráva vrátila to, co by měla. Poté vytvoříme dotazy jejich návratové kategorie a data sestavy, stejně jako dotazy na tabulky PIVOT na serveru SQL Server.

Tyto dotazy jsou tak jednoduché, jak by mohly být, takže ohledně jejich syntaxe není nic zvláštního komentovat. Pokud jde o vrácená data, měli bychom si být vědomi následujícího:

• Tabulka měst obsahuje 6 měst, a proto bychom v závěrečné zprávě měli mít 6 řádků
• Existují 3 možné výsledky v tabulce call_outcome, takže bychom měli mít 3 sloupce pro výsledky (celkem 4 sloupce – > jeden je pro název města)
• 10 řádků (pouze 8 na obrázku výše), takže v konečné tabulce by měl být součet všech výsledků 10 (každý hovor může mít přesně 1 výsledek)

Další věc, kterou musíte udělat je připravit kategorie hlášení. Chceme mít kombinaci všech měst a všech možných výsledků volání. Abychom toho dosáhli, použijeme CROSS JOIN (kartézský produkt).

Všechny kategorie přehledů zajistí, že budeme mít v sestavě řádek, bez ohledu na to, zda tato dvojice má data nebo ne. A to je to, co chceme – vidět 0 v této kategorii přehledů a tuto kategorii si nenechat ujít úplně.

Další věcí, kterou musíte udělat, je spojit všechny 4 tabulky obsahující data, která potřebujeme.

Samotný dotaz není složitý. Ve výsledku bychom si měli všimnout, že máme 10 řádků, stejné číslo jako počet hovorů, které máme v databázi. Také jsem použil funkci DATEDIFF k vrácení trvání každého hovoru v sekundách. Jednoduše bych sem mohl dát call.id, ale chtěl jsem si připomenout funkci DATEDIFF, kterou jsme zmínili v tomto článku. Tuto dobu bychom mohli použít, pokud potřebujeme SUM / AVG sekund strávených na každou kategorii přehledů (město & výsledek hovoru).

Sestava bez SQL Server PIVOT TABLE

Nyní jsme připraveni se připojit ke kategoriím a datům. Oba dříve zmíněné dotazy použijeme jako poddotazy a spojíme je pomocí LEFT JOIN (aby byly v konečném výstupu přítomny všechny kategorie).

Máme všech 18 kombinací (6 různých měst * 3 různé výsledky volání) a máme zde také všech 10 hovorů (řádky s dobou trvání hovoru < > NULL).

Vytvořme nyní dotaz SQL Server PIVOT.

Tabulka SQL Server PIVOT (statická)

Zatím se nám podařilo vytáhnout data, která potřebujeme, a máme je jako seznam. Mohli bychom exportovat data do Excelu a provádět tam transformace. Ve skutečnosti to uděláme v nadcházejícím článku.Přesto to dnes chceme vyřešit pomocí operátoru SQL Server PIVOT. V této části se budeme zabývat „statickým“ SQL Serverem PIVOT. Pojďme se nejprve podívat na použitý dotaz a výsledek.

Můžeme si všimnout, že výsledek je přesně to, co jsme chtěli. Každé město máme v jednom řádku a všechny 3 kategorie pro výsledky volání v samostatných sloupcích. Pokud pro určitý pár město – výsledek nejsou k dispozici žádná data, tato buňka musí obsahovat hodnotu 0.

Pojďme nyní komentovat dotaz PIVOT serveru SQL Server. Zde je několik věcí, které považuji za důležité zmínit zde:

• V první části dotaz – report_data, zkopírovali jsme dotaz z předchozí části. Jedinou změnou bylo, že jsme v této části dotazu neměli ORDER BY a GROUP BY, protože je zde nelze použít (měli by jít po vygeneruje se konečný výsledek dotazu)
• Část PIVOT dotazu se skládá ze 2 částí. V první definujeme, kterou agregační funkci chceme použít. V našem případě je to – COUNT ( call_duration). V FOR část qu ery, definujeme sloupce. Literární seznam všech hodnot, které chceme mít, jako sloupce. To je pevně zakódováno. Přidáme-li do tabulky novou hodnotu, nebude to mít dopad na dotaz (a měl by).

Tento dotaz plní svoji úlohu a bude fungovat perfektně, pokud se nikdy nezměníme (přidat nový , mazat, aktualizovat jejich jména) výsledky volání v související tabulce. To by se mohlo ukázat jako problém v případech, kdy potřebujeme provést změny ve slovníku. Nechceme přemýšlet, jestli existuje dotaz, který nefunguje podle očekávání. Abychom to vyřešili, budeme muset použít nový koncept – dynamický SQL.

Dynamic SQL Server PIVOT TABLE

Před přechodem na kód si krátce vysvětlíme, co vlastně dynamický SQL je. je (podrobněji se tomu budeme věnovat v následujících článcích). Nejjednodušší vysvětlení je, že v SQL můžete „sestavit“ své dotazy jako řetězce a poté tento řetězec předat jako parametr pro uloženou proceduru serveru SQL Server. Tento postup provede příkazy SQL uložené v daném řetězci (samozřejmě , pokud je syntaxe v pořádku).

I když to může znít jako něco, co se tak běžně nepoužívá, má poměrně málo míst, kde vám mnohem usnadní život. Vytvoření dotazu na tabulku SQL Server PIVOT s neznámým počet sloupců je přesně jeden takový případ.

Myšlenka tohoto přístupu je následující:

• Deklarujeme proměnnou, kam uložit všechny názvy sloupců (@columns ) a proměnná, kam uložit celý dotaz (@query)
• Pomocí dotazu SELECT vyhledáme všechny hodnoty uložené ve sloupci result_text. Následující kód – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, připojí název sloupce k seznamu všech předchozích názvů sloupců vrácených dotazem. Výsledkem je, že máme všechny názvy sloupců uložené v th Proměnná @columns
• V proměnné @query uložíme kompletní dotaz z předchozí části, s výjimkou části, kde jsou definovány sloupce. Tuto část získáme z proměnné @columns
• Poslední věcí, kterou musíme udělat, je předat celý dotaz jako parametr proceduře systému SQL Server sp_executesql

Konečný výsledek je stejný jako v předchozí části, ale tentokrát jsme si jisti, že náš dotaz bude fungovat, i když provedeme změny v hodnoty result.outcome_text. Můžete také snadno upravit tento dotaz a vypočítat jakékoli další hodnoty, např. Délku hovoru SUM / AVG na pár město – výsledek.

Závěr

Operátor SQL Server PIVOT vám dává zcela nový pohled na to, čeho můžete dosáhnout přímo ve vrstvě databáze. V kombinaci s dynamickým SQL to jde ještě dále. Důrazně doporučuji, abyste si s tím hráli – není lepší způsob učení, než si to sami vyzkoušet. V příštím článku si ukážeme, jak bychom mohli použít výstup dotazu SQL a vytvářet tabulky a grafy v aplikaci Excel (to bude fungovat nejen na serveru SQL Server, ale obecně).

Obsah

Naučte se SQL: VYTVOŘIT DATABÁZE & VYTVOŘIT TABULKOVÉ operace

Naučit se SQL: VLOŽIT DO TABULKY

Learn SQL: Primary Key

Learn SQL: Foreign Key

Learn SQL: SELECT statement

Learn SQL: INNER JOIN vs LEFT JOIN

Naučte se SQL: Skripty SQL

Naučte se SQL: Typy vztahů

Naučte se SQL: Připojte se více tabulek

Naučte se SQL: agregační funkce

Naučte se SQL: Jak napsat složitý SELECT dotaz?

Naučte se SQL: Databáze INFORMATION_SCHEMA

Naučte se SQL: Datové typy SQL

Naučte se SQL: Teorie množin

Naučte se SQL: Funkce definované uživatelem

Naučte se SQL: Uživatelem definované uložené procedury

Naučte se SQL: Zobrazení SQL

Naučte se SQL: Spouštěče SQL

Naučte se SQL: Procvičujte dotazy SQL

Naučte se SQL: Příklady dotazů SQL

Naučte se SQL: Vytvoření sestavy ručně pomocí dotazů SQL

Naučte se SQL: Funkce data a času na serveru SQL

Naučte se SQL: Vytvoření sestav na serveru SQL pomocí funkcí data a času

Naučte se SQL: kontingenční tabulky serveru SQL

Naučte se SQL: Export serveru SQL do aplikace Excel

Naučte se SQL: Úvod do smyček serveru SQL

Naučte se SQL: Kurzory serveru SQL

Naučte se SQL: Osvědčené postupy SQL pro mazání a aktualizaci dat

Naučte se SQL: Konvence pojmenování

Naučte se SQL: Úlohy související s SQL

Naučte se SQL: Non-Equi se připojuje na SQL Server

Naučte se SQL: Injekce SQL

• autor
• poslední příspěvky

Emil je databázový profesionál s více než 10 lety zkušeností ve všem, co souvisí s databázemi. V průběhu let pracoval v IT a finančním průmyslu a nyní pracuje na volné noze.
Jeho dřívější i současné angažmá se liší od návrhu a kódování databází, přes výuku, konzultace a psaní o databázích. Nezapomeňte také na BI, tvorba algoritmů, šachy, filatelie, 2 psi, 2 kočky, 1 manželka, 1 dítě …
Najdete ho na LinkedIn
Zobrazit všechny příspěvky od Emila Drkusica

Poslední příspěvky Emila Drkusica (zobrazit všechny)

• Learn SQL: SQL Injection – 2. listopadu 2020
• Learn SQL: Non-Equi Joins in SQL Server – 29. září 2020
• Learn SQL: Úlohy související s SQL – 1. září 2020