Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals

1. Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals 데이터베이스 연구실 김호숙991COG04@mm.ewha.ac.kr 2000. 3. 23

2. 목차 • Introduction • Problems With GROUP BY • The Data CUBE Operator • Addressing The Data Cube • Computing the Data Cube • Summary 데이타베이스연구실 김호숙

3. 1. Introduction • Data analysis • Extraction : database로부터 file 이나 table 로 aggregated data를 추출. • Visualizing : 그 결과를 graphical하게 가시화. • Visualization Tool • Space, Color, Time(motion) 등을 이용하여 dataset을 N-dimensional space로 표현. 데이타베이스연구실 김호숙

4. Table 1: Weather Time (UCT) Latitude Longitude Altitude (m) Temp (c) Pres (mb) 27/11/94:1500 37:58:33N 122:45:28W 102 21 1009 27/11/94:1500 34:16:18N 27:05:55W 10 23 1024 • Relational system에서는 N-attribute domain을 이용하여 N차원 데이터를 표현. Dimension Measurement 데이타베이스연구실 김호숙

5. SQL표준 aggregate function • COUNT(), SUM(), MIN(), MAX(), AVG() • 많은 SQL system의 추가 제공 함수들 • Statistical function (median,standard deviation, variance) • Physical function (center of mass) • 그밖에 domain specific function. • 사용자 정의 aggregation function • Illustra system 데이타베이스연구실 김호숙

6. GROUP BY operation SELECT Time, Altitude, AVG(Temp) FROM Weather GROUP BY Time, Altitude; 데이타베이스연구실 김호숙

7. Red Brick system에서 추가적으로 지원되는 aggregation functions. • Rank(expression) • N_tile(expression, n) • Ratio_To_Total(expression) • Cumulative(expression) • Running_Sum(expression,n) • Running_Average(expression,n) 데이타베이스연구실 김호숙

8. SQL Aggregates in Standard Benchmarks Benchmark Queries Aggregates GROUP BYs TPC-A, B 1 0 0 TPC-C 18 4 0 TPC-D 16 27 15 Wisconsin 18 3 2 AS3AP 23 20 2 SetQuery 7 5 1 2. Problems With GROUP BY: 데이타베이스연구실 김호숙

9. SQL standard GROUP BY operation으로 지원하기 어려운 data analysis 형태 • Histograms • Roll-up Totals and Sub-Totals for drill-downs • Cross Tabulations 데이타베이스연구실 김호숙

10. Histogram : aggregation over computed categories SELECT day, nation, MAX(Temp) FROM Weather GROUP BY Day(Time) AS day, Country(Latitude,Longitude) AS nation; SELECT day, nation, MAX(Temp) FROM ( SELECT Day(Time) AS day, Country(Latitude, Longitude) AS nation, Temp FROM Weather ) AS foo GROUP BY day, nation; SQL92 데이타베이스연구실 김호숙

11. Chevy 1994 black 50 Sales Roll Up by Model by Year by Color Model Year Color Sales by Model by Year by Color Sales by Model by Year Sales by Model white 40 90 1995 black 85 white 115 200 290 • Roll-up Totals and Sub-Totals for drill-downs Roll up Drill down 데이타베이스연구실 김호숙

12. Year Color Units 1994 black 50 Table 4: Sales Summary Model 1994 white 40 Chevy Chevy 1994 ALL 90 Chevy Chevy 1995 black 85 Chevy Chevy 1995 white 115 Chevy 1995 ALL 200 ALL ALL 290 SELECT Model, ALL, ALL, SUM(Sales) FROM Sales WHERE Model = 'Chevy' GROUP BY Model UNION SELECT Model, Year, ALL, SUM(Sales) FROM Sales WHERE Model = 'Chevy' GROUP BY Model, Year UNION SELECT Model, Year, Color, SUM(Sales) FROM Sales WHERE Model = 'Chevy' GROUP BY Model, Year, Color; • Supper aggregation item을 표현하기 위해 dummy value인 “ALL”을 추가한 표현 데이타베이스연구실 김호숙

13. Chevy Sales Cross Tab Chevy 1994 1995 total (ALL) black 50 85 135 white 40 115 155 total (ALL) 90 200 290 • Cross Tabulation or Cross Tab • 6차원의 cross tab을 위해서는 64번의 서로 다른 group by를 통한 결과를 64번 union해야 하며 이를 위해 대부분의SQL system은 64번의 data 의 scan이 발생한다. 데이타베이스연구실 김호숙

14. 3. The Data CUBE Operator • 3차원 aggregate를 위한 cube • 0차원 cube – point • 1차원 cube–line와 point • 2차원 cube –cross tab과 2개의 line와 한 point • 3차원 cube – 3개의 2차원 cross tab의 intersect 를 통한 cube 데이타베이스연구실 김호숙

15. 데이타베이스연구실 김호숙

16. CUBE를 지원하기 위해 확장된 syntax GROUP BY CUBE ( { ( <column name> | <expression>) [ AS <correlation name> ] [ <collate clause> ] ,...} ) SELECT day, nation, MAX(Temp) FROM Weather GROUP BY CUBE ( Day(Time) AS day, Country (Latitude,Longitude)AS nation ) ; 데이타베이스연구실 김호숙

17. DATA CUBE Model Year Color Sales Chevy 1990 blue 62 Chevy 1990 red 5 Chevy 1990 white 95 Chevy 1990 ALL 154 Chevy 1991 blue 49 Chevy 1991 red 54 Chevy 1991 white 95 Chevy 1991 ALL 198 Chevy 1992 blue 71 Chevy 1992 red 31 Chevy 1992 white 54 Chevy 1992 ALL 156 Chevy ALL blue 182 SALES Chevy ALL red 90 Model Year Color Sales Chevy ALL white 236 Chevy 5 1990 red Chevy ALL ALL 508 Chevy 87 1990 white Ford 1990 blue 63 Chevy 62 Ford 1990 red 64 1990 blue Ford 1990 white 62 Chevy 54 1991 red Ford 1990 ALL 189 Chevy 95 1991 white Ford 1991 blue 55 Chevy 49 1991 blue Ford 1991 red 52 Chevy 31 CUBE 1992 red Ford 1991 white 9 Chevy 54 1992 white Ford 1991 ALL 116 Chevy 71 1992 blue Ford 1992 blue 39 Ford 64 1990 red Ford 1992 red 27 Ford 62 Ford 1992 white 62 1990 white Ford 1992 ALL 128 Ford 63 1990 blue Ford ALL blue 157 Ford 52 1991 red Ford ALL red 143 Ford 9 1991 white Ford ALL white 133 Ford 55 1991 blue Ford ALL ALL 433 Ford 27 1992 red ALL 1990 blue 125 Ford 62 1992 white ALL 1990 red 69 Ford 39 1992 blue ALL 1990 white 149 ALL 1990 ALL 343 ALL 1991 blue 106 ALL 1991 red 104 ALL 1991 white 110 ALL 1991 ALL 314 ALL 1992 blue 110 ALL 1992 red 58 ALL 1992 white 116 ALL 1992 ALL 284 ALL ALL blue 339 ALL ALL red 233 ALL ALL white 369 ALL ALL ALL 941 SELECT Model, Year, Color, SUM(sales) as Sales FROM Sales WHERE Model in { ‘Ford’, ‘Chevy’ } AND Year BETWEEN 1990 and 1992 GROUP BY CUBE {Model,Year,Color}; Cube Relation (Ci + 1) 3 * 4 * 4 = 48 2 * 3 * 3 = 18 데이타베이스연구실 김호숙

18. ALL을 추가하면서 SQL에서 고려할 사항들 • 모든 ALL value는 그것이 의미하는 set of aggregates 값으로 해석되어야 한다. Model.ALL = ALL(Model) = {Chevy, Ford } Year.ALL = ALL(Year) = {1990,1991,1992} Color.ALL = ALL(Color) = {red,white,blue} • ALL 은 새로운 keyword가 된다. • Column을 정의할 때 ALL의 허용(불가)여부가 추가 된다. • NULL 값과 같이 다른 aggregate 에 일부가 되지 못한다. 데이타베이스연구실 김호숙

19. 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); 4. Addressing The Data Cube • Percent-of-total : global aggregate 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); 데이타베이스연구실 김호숙

20. 5. Computing the Data Cube • Group by로부터 cube의 “ALL” tuple을 계산하기 위해서 각각의 차원에 ALL value를 추가한다. • N차원 cube에서 각각의 attributecardinality가 C1, C2, C3… Cn인 경우 cube relation의 결과는 (Ci + 1)개 이다. 데이타베이스연구실 김호숙

21. 2차원 value의 집합에 대한 aggregation functions. 즉 {Xij | i = 1,...,I; j=1,...,J} 일 때 • Distributive F({Xi,j}) = G({F({Xi,j|i=1,...,I}) | j=1,...J}). Count(), Min(), Max(), Sum() • Algebraic F({Xi,j}) = H({G({X i,j |i=1,.., I}) | j=1,..., J }). Average(), standard deviation, MaxN(), MinN() • Holistic F({Xi,j |i=1,...,I}). Median(), MostFrequent() , Rank() 데이타베이스연구실 김호숙

22. 6. Summary • SQL’의 기본적인 5가지 aggregate functions은 전형적인 data mining operations을 위해서 rank, N_tile, cumulative, percent of total 과 같은 함수를 포함하는 방향으로 확장되어야 한다. • Cube operator generalizes and unifies • aggregates, • group by, • histograms, • roll-ups and drill-downs and, • cross tabs. • Cube는 distributive 와 algebraic functions class 들에 대해 쉽게 계산이 가능하다. 데이타베이스연구실 김호숙

23. Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals Jim Gray … Microsoft Research Adam Bosworth … Microsoft Research Andrew Layman … Microsoft Research Hamid Pirahesh… IBM Research 5 February 1995, Revised 18 October 1995 Technical Report MSR-TR-95-22 데이타베이스연구실 김호숙