DATA CUBE Mgr. Jiří Helmich
Analytické kroky formulace dotazu
extrakce dat
analýza
vizualizace
Motivace
n-sloupcová tabulka v Excelu vs. sloupcový graf
Dimensionality reduction
n dimenzí
data obecně uspořádána do n dimenzí vizualizace = projekce do 2D nebo 3D příklad: mapa na mapě = 2D (lat, lng) + hodnota v daném místě = 3D + časový údaj (timeline) = 4D + barva = 5D poloha
Dimensionality reduction
redukce dat z n dimenzí na počet dimenzí vizualizace
SUMARIZACE toho, co je důležité
Příklad sumarizace
prodej automobilů model rok
prodeje barva značka distributor datum prodeje
Klasické SQL konstrukty
Jak dosáhnout redukce počtu dimenzí? relační datbáze: relace s n atributy
Počasí čas
lat
lng
výška [m]
1.6.1996 15:00
37:58:33N
122:45:28W
102
21
1009
…
…
…
…
…
…
7.6.1996 15:00
34:16:18N
27:05:55W
10
23
1024
dimenze
teplota [C] tlak [HPa]
hodnoty
Klasické SQL konstrukty
redukce dimenzí ~ sumarizace ~ agregace agregační funkce COUNT
SUM MIN MAX AVG
operátor GROUP BY
Agregace SELECT AVG(teplota) FROM pocasi;
SELECT COUNT(DISTINCT teplota) FROM pocasi;
Redukce všech vybraných hodnot na jedinou hodnotu.
GROUP BY 1. 2.
rozdělení tabulky na skupiny agregace skupin pomocí agregační funkce
GROUP BY SELECT cas, vyska, AVG(teplota) FROM pocasi GROUP BY cas, vyska; čas
lat
lng
výška [m]
1.6.1996 15:00
37:58:33N
122:45:28W
102
21
21
1.6.1996 15:00
34:16:18N
27:05:55W
10
23
23
1.6.1996 16:00
34:16:18N
27:05:55W
10
27
27
1.6.1996 16:00
34:16:18N
27:05:55W
102
30
1.6.1996 16:00
37:58:33N
122:45:28W
102
24
teplota [C] AVG(teplota)
27
Další agregace
median standardní odchylka variace střed úhlová rychlost … a další doménově specifické funkce
Implementace GROUP BY Init(handle) { sum = 0 } Iter(handle, value) { sum += value } value = Final(handle) { return sum } Init(handle) { sum = 0; count = 0 } Iter(handle, value) { sum += value; ++count } value = Final(handle) { return (sum/count) }
Agregace /Red Brick/
Rank(expr) pro N hodnot v tabulce expr
je minimum z hodnot ve sloupci: 1 expr je maximum z hodnot ve sloupci: N
N_tile(expr, n) rozdělí
tabulku na n hodnotových (zhruba stejně velkých) rozsahů vrátí číslo (index) rozsahu, do kterého patří expr
Ratio_To_Total(expr) sečte
všechny výrazy vrátí podíl expr oproti získané sumě
N_tile - příklad SELECT percentil, MIN(teplota), MAX(teplota) FROM pocasi GROUP BY N_tile(teplota, 10) as percentil HAVING percentil = 5; 1.
2. 3.
rozdělí tabulku na 10 podobně velkých skupin v každé skupině spočte MIN a MAX vybere řádek s 5. skupinou (prostředních 10 % všech teplot)
Kumulativní agregace
Cumulative(expr) součet
dosavadních hodnot v uspořádaném seznamu
Running_sum(expr, n) součet
n posledních hodnot v uspořádaném seznamu prvních n-1 hodnot je NULL
Running_average(expr, n) průměr
Nedostatky GROUP BY
GROUP BY neumožňuje přímou tvorbu histogramů některé
systémy ano, ale mimo standard
SELECT den, stat, MAX(teplota) FROM pocasi GROUP BY DAY(cas) AS den, NATION(lat, lng) AS stat; Denní maximum v dané zemi.
Histogramy dle SQL92 SELECT FROM
den, stat, MAX(teplota) (SELECT DAY(cas) AS den, NATION (lat, lng) AS
stat, teplota FROM pocasi ) AS tmp GROUP BY den, stat;
Nejprve transformace, poté agregace.
roll-up & drill-down
reporty pomocí hrubých agregací postupně odkrýváme detaily nejprve
roll-up & drill-down Model Chevy
Rok 1994
Barva
Prodej - model - rok - barva
černá
50
bílá
40
Prodej - model - rok
Prodej - model
90 1995
černá
85
bílá
115 200 290
roll-up podle modelu, roku a barvy
2N agregačních sloupců! (6D = 64)
roll-up & drill-down Model
Rok
Barva
Prodej - model - rok - barva
Prodej - model - rok
Prodej - model
Chevy
1994
černá
50
90
290
Chevy
1994
bílá
40
90
290
Chevy
1995
černá
85
200
290
Chevy
1995
bílá
115
200
290
roll-up podle modelu, roku a barvy [Date]
roll-up & drill-down rok/barva Model
1994
součet
bílá
mezisoučet 1995
černá
bílá
mezisoučet 1994
1995
Chevy
50
40
90
85
115
200
290
Ford
50
10
60
85
75
160
220
Součet
100
50
150
170
190
360
510
černá
roll-up podle modelu, roku a barvy [Excel Pivot]
Hodnota ALL – méně sloupců SELECT ALL, ALL, ALL, SUM(prodeje) FROM prodeje WHERE model = ‘Chevy’ UNION SELECT model, ALL, ALL, SUM(prodeje) FROM prodeje WHERE model = ‘Chevy’ GROUP BY model UNION SELECT model, rok, ALL, SUM(prodeje) FROM prodeje WHERE model = ‘Chevy’ GROUP BY model, rok UNION SELECT model, rok, barva, SUM(prodeje) FROM prodeje WHERE model = ‘Chevy’ GROUP BY model, rok, barva;
Model
Rok
Barva
Kusů
Chevy
1994
černá
50
Chevy
1994
bílá
40
Chevy
1994
ALL
90
Chevy
1995
černá
85
Chevy
1995
bílá
115
Chevy
1995
ALL
200
Chevy
ALL
ALL
290
ALL
ALL
ALL
290
Roll-up
Není symetrický! počítá meziagregace podle všech agregovaných sloupců (všechny kombinace) Roll-up dle GROUP-BY Pivot
Model
Rok
Barva
Kusů
Chevy
ALL
černá
135
Chevy
ALL
bílá
155
UNION SELECT model, ALL, barva, SUM(prodeje) …
Kontingenční tabulky
Kontingenční tabulka je výsledkem symetrické agregace Chevy
1994
1995
Součet (ALL)
černá
50
85
135
bílá
40
115
155
Součet (ALL) 90
200
290
Ford
1994
1995
Součet (ALL)
černá
50
85
135
bílá
10
75
85
160
220
Součet (ALL) 60
Problémy GROUP BY
Velikost dotazů je závislá na počtu dimenzí 6D
= 64-way UNION
průchodů daty 64 třídění/hashování 64
Operátory CUBE a ROLLUP
Myšlenka: zavést operátor, který provede agregace postupně na všech úrovních
0D
2D
1D
3D
ALL, ALL, ALL, …, ALL, f(*) = SUM (0D)
Operátor CUBE
Operátor CUBE
nagenerovat množinu všech podmnožin agregací rozšíření GROUP BY GROUP
BY je speciální případ CUBE
SELECT den, stat, MAX(teplota) FROM pocasi GROUP BY CUBE DAY(cas) AS den, NATION(lat, lng) AS stat;
Operátor CUBE 1.
2.
klasická agregace přes <select list> atributy z GROUP BY UNION “super-agregací” globální kostky
substituce hodnot za ALL
N atributů v <select list> ~ 2N-1 superagregací
Operátor CUBE
C1,C2, …, CN – kardinality N atributů ze <sel. list> kardinalita výsledné kostky: ∏(Ci+1) +1 pro každý atribut za hodnotu ALL
ROLLUP
pokud chceme data pro roll-up nebo drill-down report, je CUBE “kanón na vrabce”
ROLLUP(v1, …, vn) (v1 , v2 , …, vn , f()), (v1 , v2 , …, ALL, f()), …………………………. (v1 , ALL , …, ALL, f()), (ALL , ALL , …, ALL, f()).
ROLLUP vs. CUBE ROLLUP (YEAR, MONTH, DAY) YEAR, MONTH, DAY YEAR, MONTH YEAR ()
CUBE YEAR, MONTH, DAY YEAR, MONTH YEAR, DAY YEAR MONTH, DAY MONTH DAY ()
Algebra => syntaxe
CUBE(ROLLUP) = CUBE ROLLUP(GROUP BY) = ROLLUP
GROUP BY <select list> ROLLUP <select list> CUBE <select list>
Příklad SELECT
vyrobce, rok, mesic, den, barva, model, SUM(cena) AS
zisk FROM prodej GROUP BY vyrobce, ROLLUP YEAR(cas) AS rok, MONTH(cas) AS mesic, DAY(cas) AS den, CUBE barva, model;
Syntaxe GROUP BY
::= { ( | <expression> ) [ AS ] [ ,…}
]
Rozšíření GROUP BY nezávislé na CUBE
Syntaxe GROUP BY [ ] [ ROLLUP ] [CUBE ]
ALL
nové klíčové slovo
model.ALL = ALL(model) rok.ALL = ALL(rok) barva.ALL = ALL(barva)
= {Chevy, Ford} = {1994,1995} = {černá, bílá}
definice sloupce: ALL [NOT] ALLOWED podpora operátorů = a IN operátor GROUPING(): ALL() IS NOT NULL Jak je to náročné?
ALL – jde to i bez něj?
ALL je jako NULL – mnoho speciálních případů vyžaduje
vnímání množiny jako hodnoty
návrh místo ALL neimplementovat ALL implementovat GROUPING pro rozlišení ALL a NULL NULL
ALL jako NULL SELECT
model, rok, barva, SUM(prodej), GROUPING(model), GROUPING(rok), GROUPING(barva) FROM prodej GROUP BY CUBE model, rok, barva; ALL
(NULL, NULL, NULL, 941, TRUE, TRUE, TRUE) ALL
(ALL, ALL, ALL, 941
Decorations
sloupce, které nejsou v GROUP BY ale
jsou funkčně závislé na GROUPING()
SELECT oddeleni.jmeno, SUM(prodej) FROM prodej JOIN oddeleni USING (cislo_oddeleni) GROU BY prodej.cislo_oddeleni; jméno
oddělení není agregační sloupec jméno oddělení závislé na agregované hodnotě
Decorations SELECT
den, stat, MAX(teplota), continent(stat) AS kontinent FROM pocasi GROUP BY CUBE DAY(cas) AS den, country(lat, lng) AS stat; den
národ
MAX(teplota)
kontinent
25.1.1995
USA
28
Severní Amerika
ALL
USA
37
Severní Amerika
25.1.1995
ALL
41
NULL
ALL
ALL
48
NULL
Snowflake a star schémata
tabulka pozorování dekorační tabulky pro každou dimenzi granularita tvoří čistou hierarchii
Užitečné zkratky SELECT Model,Year,Color,SUM(Sales), SUM(Sales)/ (SELECT SUM(Sales) FROM Sales WHERE Model IN {‘Ford’,‘Chevy’} AND Year Between 1990 AND 1992 ) FROM Sales WHERE Model IN { ‘Ford’ , ‘Chevy’ } AND Year Between 1990 AND 1992 GROUP BY CUBE Model, Year, Color ;
SELECT Model, Year, Color SUM(Sales) AS total, SUM(Sales) / total(ALL,ALL,ALL) FROM Sales WHERE Model IN { ‘Ford’ , ‘Chevy’ } AND Year Between 1990 AND 1992 GROUP BY CUBE Model, Year, Color;
Výpočet ROLLUP a CUBE 1.
2.
seřadit řádky podle hodnot agregačních atributů spočítat agregační funkce CUBE je UNION několika ROLLUP
Výpočet
agregace algebraické
– MaxN, MinN, Avg distributivní – count, max, min, sum
alokace handle pro každou buňku n-tice xi
(x1,x2,....,xN, v): 2N x Iter()
nebo ALL pro N hodnot
tabulka kardinality T T
x 2N
Udržování kostky
Literatura Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and SubTotals Jim Gray Surajit Chaudhuri, Adam Bosworth, Andrew Layman, Don Reichart, Murali Venkatrao, Frank Pellow, Hamid Pirahesh
