1 / 46

CUBE - Operátor relační agregace

CUBE - Operátor relační agregace. David Hoksza. Aplikace pro analýzu dat. Formulace dotazu Extrakce dat Vizualizace výsledků Analýza výsledků. Modelování n-dimenzionálního problému plochými tabulkami. Počasí. Dimenzionální redukce (agregace) ve vizualizačních nástrojích. Histogramy

nomlanga-harper
Download Presentation

CUBE - Operátor relační agregace

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CUBE - Operátor relační agregace David Hoksza

  2. Aplikace pro analýzu dat • Formulace dotazu • Extrakce dat • Vizualizace výsledků • Analýza výsledků

  3. Modelování n-dimenzionálního problému plochými tabulkami Počasí

  4. Dimenzionální redukce (agregace) ve vizualizačních nástrojích • Histogramy • Křížové tabulky • Součty, podsoučty, …

  5. Dimenzionální redukce v SQL • Agregační funkce • COUNT() • SUM () • MIN() • MAX() • AVG() • Operátor GROUP BY

  6. Příklady agregace • SELECTAVG(Tepl) FROM Pocasi; • SELECTCOUNT(DISTINCTCas)FROM Pocasi; • SELECTCas, Vyska, AVG(Tepl) FROMPocasi GROUP BY Cas, Vyska;

  7. Problémy GROUP BY • Histogramy • Roll-up součty, drill-down podsoučty • Křížové tabulky

  8. GROUP BY a histogramy • SELECTDen, stat, MAX(Tepl) FROM Pocasi GROUP BYDen(Cas) AS Den, Stát(Sirka,Delka) AS stat; NELZE • SELECT Den, stat, MAX(Tepl) FROM( SELECT Den(Cas) AS Den, Stat(Sirka, Delka) AS stat, Tepl FROM Pocasi ) AS foo GROUP BY Den, stat;

  9. Řešení roll up není relační

  10. Řešení roll up 2N agregačních sloupců

  11. Řešení roll up – hodnota ALL(1) • Přetížení hodnot sloupce => přidání ALL • SELECT Model, ‘ALL’, ‘ALL’, SUM(Prodeje) FROM Prodeje WHERE Model = 'Chevy' GROUP BYModelUNION SELECT Model, Rok, ‘ALL’, SUM(Prodeje)FROM Prodeje WHEREModel = 'Chevy' GROUP BYModel,RokUNIONSELECT Model, Rok, Barva, SUM(Prodeje)FROM Prodeje WHERE Model = 'Chevy' GROUP BY Model, Rok, Barva;

  12. Řešení roll up – hodnota ALL(2) • Symetrická agregace: UNION SELECT Model, ALL, Barva, SUM(Prodeje) FROM Prodeje WHERE Model = 'Chevy' GROUP BY Model, Barva;

  13. Křížové tabulky (1) • cross-tabulation, cross tab • Předchozí jsou relační formou křížové tabulky • Obvyklá reprezentace v reportech

  14. Křížové tabulky (2)

  15. Problémy • Složitý SQL zápis (6D dotaz => 64 UNION) • Optimalizace

  16. Dimenze CUBE

  17. Celkové a dílčí agregace CUBE • CUBE tvoří tabulku se všemi dimenzemi • celková agregace funkcí f(): ALL, ALL, ….., ALL, f(*) • Dílčí agregace (vyšší dimenze) …, ALL, …ALL, …, f(*)

  18. Příklad (1) CUBE SELECT Model, Rok, Barva, SUM(prodeje) as Prodeje FROM Prodeje WHERE Model in (‘Ford’, ‘Chevy’) AND Rok BETWEEN 1900 AND 1992 GROUP BY CUBE Model, Rok, Barva

  19. Příklad (2) • SELECT Den, stat, MAX(Tepl) FROM Pocasi GROUP BY CUBE Den(Cas) AS Den, Zeme(Sirka, Delka) AS stat;

  20. Sémantika CUBE • Provádění CUBE: • Klasický GROUP BY přes <select list> • Postupná záměna za ALL => superagregáty

  21. Operátor ROLLUP • CUBE může být být moc • Chci pouze roll-up nebo drill-down • Funkční závislosti atributů • => vznik operátoru ROLLUP • Produkuje pouze superagregáty: (v1 , v2 , …., vn , f()), (v1 , v2 , …., ALL, f()), .. .. (v1 , ALL, …., ALL, f()), (ALL, ALL, …., ALL, f()).

  22. Výhody ROLLUP • Rychlejší • Výhodné pro kumulativní agregáty (přirozeně lineární množina výsledků)

  23. Algebra pro operátory GROUP, CUBE, ROLLUP • CUBE od GROUP BY je CUBE • CUBE od ROLLUP je CUBE • ROLLUP od GROUP BY je ROLLUP • Tedy: CUBE(ROLLUP) = CUBE ROLLUP(GROUP BY) = ROLLUP • Použití: GROUP BY <select list> ROLLUP <select list> CUBE <select list>

  24. Příklad složení SELECT Manufacturer, Rok , Mesic, Den, Barva, Model SUM(cena) AS Vynos FROM Prodeje GROUP BY Manufacturer, ROLLUP Rok(Cas) AS Rok , Mesic(Cas) ASMesic, Den(Cas) AS Den, CUBEBarva,Model;

  25. Syntaxe • Současná: GROUP BY {<column jmeno> [collate clause], …} • Rozšířená: • GROUP BY <aggregation list> • <aggregation list> ::= • {(<column jmeno> | <expression>) • [ AS <correlation jmeno> ] • [ <collate clause> ] • ,…} • Nová: • GROUP BY [ <aggregation list> ] • [ ROLLUP <aggregation list> ] • [ CUBE <aggregation list> ]

  26. Diskuse hodnoty ALL • Co je ALL? • Model.ALL = ALL(Model) = {Chevy, Ford} • Model.ALL = ALL(Rok) = {1990, 1991, 1992} • Model.ALL = ALL(Barva) = {red, white, blue} • hnízděné relace

  27. ALL() • ALL reprezentuje množinu • Funkce ALL() vrací množinu, nebo NULL • Zjištění, zda je sloupec agregát

  28. Přidání ALL do SQL • Nové klíčové slovo • ALL [NOT] ALLOWED do definice sloupce a systémového katalogu • Interpretace operátorů jako množinových (=) • Ostatní prvky domény => singltony

  29. Problémy ALL • Tvoří speciální případy • Nutí pracovat s hodnotami jako s množinami • Lze vynechat

  30. Nahrazení ALL • Místo ALL použít NULL • Neimplementovat ALL() • Implementovat GROUPING() pro rozlišení mezi NULL a ALL

  31. Nahrazení ALL - příklad • SELECT Model,Rok,Barva,SUM(prodeje), GROUPING(Model), GROUPING(Rok), GROUPING(Barva) FROM Prodeje GROUP BY CUBE Model, Rok, Barva; Dostaneme: (NULL, NULL, NULL, 941, TRUE, TRUE, TRUE) Místo (ALL, ALL, ALL, 941)

  32. Dekorace • Sloupce, které nejsou v GROUP BY, ale jsou na nich funkčně závislé • SELECT oddeleni.jmeno, sum(prodeje) FROMprodeje JOIN oddeleni USING (oddeleni_cislo) GROUP BY prodeje.oddeleni_cislo; • oddeleni.jmeno není v SQL92 povoleno

  33. Dekorace – nový přístup • Je-li dekorace funkčně závislá na agregaci, pak je v SELECT listu povolena • Dekorace interagují s agregačními sloupci: SELECT Den,stat,MAX(Tepl), kontitnent(stat) AS kontitnent FROM Pocasi GROUP BY CUBE Den(Cas) AS Den, Zeme(Sirka, Delka) AS stat

  34. Schémata dimenzí • Ukládáno mnoho informacích o akci => dimenze • Schéma sněhové vločky (snowflake schema) • Hvězdicové schéma (star schema)

  35. Snowflake - příklad • Tabulky dimenzí mohou obsahovat i dekorace (např. další informace o kanceláři…) • Dimenze se mohou dále štěpit (např. týdny nezapadají do měsíců)

  36. Počítání CUBE a ROLLUP • Zobecnění GROUP BY => stejné techniky výpočtu • Počítaní agregátů na co nejnižší systémové úrovni • Omezit přesuny dat • Používat pole nebo hashování pro reprezentaci agregačních sloupců v paměti • Pro velké agregáty (řetězce) používat hashování

  37. Definice a implementace agregačních funkcí • 1. Inicializace agregační funkce • 2. Volání agregace pro každou novou hodnotu • 3. Získání výsledné hodnoty • Možnost definovat cenu funkce => • Prostor pro optimalizátor

  38. 2N - algoritmus • Alokování prostoru pro každou buňku kostky • Pro každý nový (x1, …, xN, v) Iter(ukazatel, v) • tzn. 2N krát (xi nebo ALL) • Final(&ukazatel) pro každý z Π(Ci+1) uzlů kostky • Kardinalita základní tabulky T => T*2N volání Iter() • Lze zrychlit podle typu agregační funkce

  39. Typy agregačních funkcí • Mějme 2-dimenzionální množinu hodnot {Xij|I=1,…I;j=1,…J} • Typy funkcí: • Distributivní • Algebraické • Holistické

  40. Distributivní funkce • Agregační funkce F() je distributivní, existuje-li funkce G() tž.: F({Xi,j}) = G({F({Xi,j|I=1,…,I})|j=1,…,J}) • Př.: • MIN(), MAX(), SUM() ….. F=G • COUNT() … G=SUM()

  41. Algebraické funkce • Agregační funkce F() je algebraická, existuje-li funkce G() vracející n-tici a funkce H() tž.: F({Xi,j}) = H({G({Xi,j|I=1,…,I})|j=1,…,J}) • Př.: Average(), MaxN(), MinN()

  42. Holistické funkce • Agregační funkce F() je holistická, jestliže neexistuje konstanta omezující velikost subagregátu tj.: Neexistuje konstanta M charakterizující F({Xi,j|I=1,…,I}) • Př.: Median(), MostFrequent()

  43. Počítání super-agregátů holistické funkce • Není znám efektivnější postup než 2N-algoritmus používající standardní techniky GROUP BY

  44. Počítání super-agregátů distributivní funkce • Z N-té dimenze spočítáme (N-1)-tou dimenzi projekcí (agregací) jedné dimenze • Př.: CUBE(ALL,x2,…,xN) = F({CUBE(u,x2,…,xN)}) • Distributivnost umožňuje agregaci agregátů

  45. Počítání super-agregátů algebraické funkce • Spíše než subagregáty je nutno si pamatovat n-tice z vyšších dimenzí!

  46. Shrnutí • Operátor CUBE generalizuje a sjednocuje: Agregáty GROUP BY Histogramy Roll-upy Křížové tabulky • CUBE je založen na ALL (označení sloupce přes který se agreguje) • Někdy se vyplatí pouze ROLLUP • Jednoduše spočitatelné pro distributivní a algebraické funkce

More Related