Dotazovací jazyky I Datová krychle
Soběslav Benda
Obsah z
Úvod do problematiky z
Varianty přístupu uživatelů ke zdrojům dat z
z
z
Datová analýza
Motivace z
z
OLTP vs. OLAP
Vytvoření křížové tabulky
Datová krychle z z z z z
Teorie Operátory CUBE a ROLLUP Použití SQL syntax SŘBD
Úvod do problematiky (1) z
z
V relačních databázích zaznamenáváme různé subjekty a vztahy mezi nimi Subjekty mají různé vlastnosti (atributy) z z
z
Výrobek – název, čas_výroby, kategorie Měření teploty – naměřená_hodnota, čas_měření, území
Subjekty mají vztahy k různým dimenzím z
Dimenze = typické kategorie pro analýzu obchodních dat z
z
čas, geografická poloha, kategorie, …
Příklad: z z
Kolik jsem vyrobil ve kterém měsíci? Jaké byly průměrné denní teploty v různých krajích?
Úvod do problematiky (2) z
z
Na data se obvykle potřebujeme dotazovat z různých perspektiv Dvě kategorie dotazů: z
Dotazy pro výběr konkrétních záznamů z z
z
Jaké filmy hrají dnes v kině Světozor? Jaké dvojice zákazníků mají stejnou adresu?
Dotazy pro zachycení celků z z
Kolik zákazníků žije v Praze? Kolik průměrně filmů hrála pražská kina v každém měsíci předchozího roku?
Úvod do problematiky (3) z z
Dvě kategorie přístupů k datům: Uživatelé přistupují k datům denně z z
OLTP (Online transakční zpracování) Řízení změn dat v tabulkách během provádění obchodních procesů z z
z
Načítání určitých záznamů Modifikace existujících záznamů
Uživatelé hledají smysl ohromného množství dat z z
OLAP (Online analytické zpracování) Sumární náhledy na data z z
z
Reporty Sestavy
Hledání vzorů v datech, rozhodování, udržení podniku na úrovni potřebné k úspěšnému přežití na trhu
Úvod do problematiky (4) z
Datová analýza z z z
Formulace dotazu Získání agregovaných hodnot Vizualizace výsledků z z z
z
Chceme všechny závislosti najednou - sledování vývoje, rozdílů Vizualizace 2D a 3D objektů Dimenzionální redukce - agregace přes počítané kategorie (histogramy), křížové tabulky, částečné součty, …
Analýza výsledků a formulace nových dotazů z
zpřesňování/zobecňování pohledů
Úvod do problematiky (5) z
Histogram = jeden ze způsobů vizualizace dat z z
Souhrny přes vypočítané kategorie Příklad: z z
z
Počasí(čas, z_šířka, z_výška, teplota) Pro každé území chceme minimální měsíční teplotu.
Moderní SQL systémy podporují histogramy přímo (není potřeba používat hnízděné dotazy jako v SQL92) SELECT měsíc, území, MIN(teplota) FROM Počasí GROUP BY Měsíc(čas) AS měsíc, Území(z_šířka,z_výška) AS území
z
Mnohem zajímavějším problémem jsou např. křížové tabulky
Motivace (1) z
Demonstrace vytvoření křížové tabulky z
z
Mějme tabulku obsahující záznamy o počtech zvířat v prodejních pobočkách. zvirata(druh, pobocka, počet) druh pobocka pocet pes
Praha
12
pes
Brno
14
pes
Ostrava
5
kočka
Ostrava
9
kočka
Praha
18
želva
Brno
4
želva
Ostrava
1
Motivace (2) z
Z uvedených dat chceme získat souhrnný report (křížovou tabulku):
pes
Praha
Brno
Ostrava
ALL
12
14
5
31
9
27
4
1
5
18
15
63
kočka 18 želva ALL z
30
Otázky: z z
Jaké nástroje můžeme v SQL systémech využít? Jak použít rychlou a efektivní cestu?
Motivace (3) z
Agregační funkce z z z z z
Vytváření základních souhrnných informací Možno aplikovat na všechny řádky vrácené klauzulemi FROM a WHERE Použití se vztahuje k hodnotám daného sloupce tabulky Agregační funkce vrací jednu hodnotu Standardní agregační funkce z z z z z
z z
COUNT() - možno použít na všechny sloupce SUM() MIN() MAX() AVG()
Mnoho SQL systémů přidává doménově specifické (statistické, fyzikální, finanční analýza, …) Některé systémy umožňují přidávat vlastní
Motivace (4) z
Výpis počtu všech zvířat: SELECT SUM(pocet) FROM zvirata
z
Výpis počtu psů ve všech pobočkách: SELECT SUM(pocet) FROM zvirata WHERE druh='pes'
z
Výpis počtu všech zvířat v pražské pobočce: SELECT SUM(pocet) FROM zvirata WHERE pobocka='Praha'
Motivace (5) z
Operátor GROUP BY z
z
z
z
Umožňuje aplikovat agregační funkce na několik skupin v jednom dotazu Řádky, které projdou filtrem WHERE se konceptuálně rozdělí na skupiny, pro které je hodnota zadaného sloupce (sloupců) stejná Získáváme tabulku skupin a na jednotlivé skupiny se pak aplikuje agregační funkce (každá skupina přispívá jedním řádkem do výsledné tabulky)
Syntaxe: SELECT { <sloupec>|
, … } FROM WHERE <podmínka> GROUP BY { , …}
Motivace (6) z
Výpis všech poboček s počtem zvířat: SELECT pobocka, SUM(pocet)
FROM zvirata GROUP BY pobocka z
Výpis všech druhů zvířat s jejich počtem ve všech pobočkách: SELECT druh, SUM(pocet) FROM zvirata GROUP BY druh
Motivace (7) z
z
Je možno získat data pro report jedním dotazem prostředky které známe? ALL(druh) = {kočka, pes, želva}
pes
pobočka
počet
ALL
ALL
63
ALL
Brno
18
ALL
Ostrava
15
ALL
Praha
30
kočka
Ostrava
9
Praha
Brno Ostrava ALL
kočka
Praha
18
12
14
5
31
kočka
ALL
27
9
27
pes
Brno
14
4
1
5
pes
Ostrava
5
18
15
63
pes
Praha
12
pes
ALL
31
želva
Brno
4
želva
Ostrava
1
želva
ALL
5
kočka 18 želva ALL
druh
30
Motivace (8) z
Použití GROUP BY a UNION z
Hodnoty ALL budou ve výsledné tabulce reprezentované jako řetězce
SELECT druh, pobocka, pocet FROM zvirata UNION SELECT druh,'ALL', SUM(pocet) FROM zvirata GROUP BY druh UNION SELECT 'ALL', pobocka, SUM(pocet) FROM zvirata GROUP BY pobocka UNION SELECT 'ALL','ALL', SUM(pocet) FROM zvirata
Motivace (9) z
Použití GROUP BY a UNION z z z
z
z
Zápis dotazu je dlouhý Pro každou požadovanou dimenzi je potřeba jeden UNION Výpočetně náročné = pro každý poddotaz se prochází data zvonu, třídění výsledků, hašové výpočty, … Nejednotný přístup = ALL jsou řetězce
Řešení = operátor CUBE z
Vytvoří tabulku, která bude mít všechny agregované hodnoty z
CUBE(druh, pobocka)
SELECT druh, pobocka, SUM(pocet) FROM zvirata GROUP BY druh, pobocka WITH CUBE
Motivace (10) SELECT druh,pobocka,SUM(pocet) FROM zvirata GROUP BY druh,pobocka WITH CUBE
z z
MS SQL Server 2005 2x rychlejší než GROUP BY a UNION
druh
pobočka
počet
ALL
ALL
63
ALL
Brno
18
ALL
Ostrava
15
ALL
Praha
30
kočka
Ostrava
9
kočka
Praha
18
kočka
ALL
27
pes
Brno
14
pes
Ostrava
5
pes
Praha
12
pes
ALL
31
želva
Brno
4
želva
Ostrava
1
želva
ALL
5
Datová krychle (1) z
N-Dimenzionální zobecnění agregací: z z z z z
0-D … agregační funkce 1-D … GROUP BY 2-D … křížová tabulka 3-D … datová krychle >3-D … datová hyper-krychle
Datová krychle (2) z
Příklad: demonstrace 3D-agregace z z
Prodej pečiva (kusy v řádech tisíců) Tabulka: prodej_pečiva(datum, město, druh, prodáno)
Datová krychle (3) z
Množinový význam CUBE(A, B, C) z z z z z z z
z
z
(A) (B) (C) (A, B) (A, C) (B, C) (A, B, C)
Pro CUBE na N atributech bude ve výsledné tabulce 2N - 1 souhrnných hodnot CUBE(druh, pobočka) z (druh), (pobočka), (druh, pobočka)
druh
pobočka
počet
ALL
ALL
63
ALL
Brno
18
ALL
Ostrava
15
ALL
Praha
30
kočka
Ostrava
9
kočka
Praha
18
kočka
ALL
27
pes
Brno
14
pes
Ostrava
5
pes
Praha
12
pes
ALL
31
želva
Brno
4
želva
Ostrava
1
želva
ALL
5
Datová krychle (4) z
Kardinalita výsledné kostky z CUBE nad N atributy, které mají kardinalitu C1, C2, …, CN je kardinalita výsledné kostky (horní odhad) = z
z
(C1+1) x … x (CN+ 1)
Příklad: z z z z
zvirata(druh, pobocka, počet) Druh = {pes, kočka, želva}, |C1| = 3 Pobočka = {Praha, Brno, Ostrava}, |C2| = 3 CARD(CUBE) = (3 + 1) x (3 + 1) = 16 z
Počet řádků v naší kostkce bylo 14
Datová krychle (5) z
Někdy nepotřebujeme všechny agregace z z
Všechny kombinace nemusí dávat smysl Stačí produkovat hierarchii dle pořadí atributů
z
Vybudování celé kostky je drahá operace
z
ROLLUP(A, B, C) z
z
(A, B, C), (A, B), (A)
ROLLUP(druh, pobočka) z
(druh, pobočka), (druh)
Praha
Brno
Ostrava
ALL
pes
12
14
5
31
kočka
18
9
27
1
5
želva ALL
4
63
Datová krychle (6) z z
Operátory tvoří hierarchii GROUP BY, ROLLUP a CUBE mají zajímavou algebru: z z z
z z
CUBE(ROLLUP) = CUBE CUBE(GROUP BY) = CUBE ROLLUP(GROUP BY) = ROLLUP
Operátory můžeme skládat “Nejsilnější“ varianta: GROUP BY <sloupce> ROLLUP <sloupce>
CUBE <sloupce>
Datová krychle (7) z
Příklad – kombinování operátorů z
prodej_pečiva(datum, město, druh, prodáno)
SELECT datum, město, druh, SUM(prodáno) FROM prodej_pečiva GROUP BY datum, ROLLUP(město, druh) z z
Agregace pro všechna města a všechna města a druhy GROUP BY nad datum
Datová krychle (8) - syntaxe z
GROUP BY WITH CUBE z z
z
Návrh standardu MS SQL Server 2005
GROUP BY CUBE z
SQL99 z z z
Oracle IBM DB2 MS SQL Server > 2005
Datová krychle (9) - syntaxe z
GROUP BY WITH ROLLUP z z
z
Návrh standardu MS SQL Server 2005
GROUP BY ROLLUP z
SQL99 z z z
Oracle IBM DB2 MS SQL Server > 2005
Datová krychle (10) – hodnota ALL z z
z
Co je to hodnota ALL? Každá hodnota ALL v podstatě reprezentuje množinu (množinu přes kterou byla spočtena agregace) Příklad: z z
z
ALL(druh) = {pes, kočka, želva} ALL(pobočka) = {Praha, Brno, Ostrava}
Nechceme zavádět nové klíčové slovo z
Podobné problémy jako s NULL
Datová krychle (11) – hodnota ALL z z
Jak se s ALL vypořádat? Nahrazení ALL symbolem NULL, tzv. seskupovací NULL z
z
Nový predikát GROUPING(sloupec), který dokáže odlišit seskupovací NULL od běžných NULL Návratové hodnoty: z z
1 = hodnota NULL je seskupovací 0 = jinak
Datová krychle (12) – hodnota ALL SELECT CASE WHEN GROUPING(druh)=1 THEN 'xxx' ELSE druh END AS druh, CASE WHEN GROUPING(pobocka)=1 THEN 'xxx' ELSE pobocka END AS pobočka, SUM(počet) as počet, FROM zvirata GROUP BY druh,pobocka WITH CUBE
druh
pobočka
počet
xxx
xxx
63
xxx
Brno
18
xxx
Ostrava
15
xxx
Praha
30
kočka
Ostrava
9
kočka
Praha
18
kočka
xxx
27
pes
Brno
14
pes
Ostrava
5
pes
Praha
12
pes
xxx
31
želva
Brno
4
želva
Ostrava
1
ž
Datová krychle (13) z
Doporučení pro implementace výpočtů agregací: z z
výpočty agregací právědet na co nejnižší systémové úrovni používat pole a hašování k organizaci agregačních sloupců v paměti z z
z z
Mapování dlouhých řetězců do celých čísel Agregace mohou být uloženy v N-dimenzionální polích
Možno používat hybridní hašování pro velké množství dat Používání paralelního zpracování pro obrovské množství dat z
Data mohou být roztroušena na více discích nebo uzlech
Datová krychle (14) z
Druhy agregačních funkcí z
Distributivní: F(X) = G(F(X)) z z z z
z
Algebraické: F(X) = H(G(X)) z z
z
COUNT MIN MAX SUM AVG Směrodatná odchylka
Holistické: z
MostFrequent
Datová krychle (15) – použití z
Reporty z
z
Křížové tabulky, histogramy, grafy, …
OLAP (Online analytické zpracování) z
Cíle: z z
z
z z z z
Analyzovat obrovské množství dat Vygenerovat různé varianty souhrnů a agregací
Oproti reportům interaktivní (materializované kostky) – práce přímo s kostkou Celá databáze je do určité míry denormalizovaná Připuštěna určitá míra redundance Databáze označujeme jako dimenzionální Dimenze krychle = typické kategorie pro analýzu obchodních dat z
čas, geografická poloha, skupiny výrobků apod.
Datová krychle (16) z
Údržba materializovaných krychlí je jiná než jejich pouhé vytváření jako u reportů z
z
Algebraické funkce z
z
Laciná údržba
Distributivní funkce z
z
Potřeba měnit hotovou krychli dynamicky - spouště (triggers)
Drahá údržba
Holistické funkce z
Velmi drahá údržba
Závěr (1) z
Operátory CUBE a ROLLUP z z
Zobecnění GROUP BY (1D agregace) Optimalizované získávání agregací přes více dimenzí z
z
z
Vhodné používat nad velkými daty
Zjednodušení zápisu dotazů
Použití datových krychlí: z
Generátory reportů z
z
Použití CUBE a ROLLUP v SQL
OLAP systémy z
Materializované krychle
Závěr (2) z
Operátor CUBE z
z
Operátor ROLLUP z
z
Celá kostka = agregace přes všechny dané atributy (dimenze) Část kostky = produkuje hierarchie dle pořadí daných atributů (dimenzí)
Predikát GROUPING z
Umožňuje odlišit hodnoty ALL, resp. seskupovací NULL ve výsledných tabulkách
Zdroje z
[1] J. Gray a kol.: Data cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and SubTotals, New Orleans, March 1996
z
[2] SQL1999. ansi-iso-9075-2-1999, September 1999
z
[3] http://www2.cs.uregina.ca/~hamilton/courses/831/notes/dcubes/dcubes.html
z
[4] R.Vieira: SQL Server 2000 Programujeme Profesionálně, Computer Press, Brno, 2001
Děkuji za pozornost
