数据库系统概念5英文原版课件ch18.pptVIP

Copyright: Silberschatz, Korth and Sudarshan Chapter 18: Data Analysis and Mining Chapter 18: Data Analysis and Mining Decision Support Systems Decision-Support Systems: Overview Data Analysis and OLAP Cross Tabulation of sales by item-name and color Relational Representation of Cross-tabs Data Cube Online Analytical Processing Hierarchies on Dimensions Cross Tabulation With Hierarchy OLAP Implementation OLAP Implementation (Cont.) Early OLAP systems precomputed all possible aggregates in order to provide online response Space and time requirements for doing so can be very high 2n combinations of group by It suffices to precompute some aggregates, and compute others on demand from one of the precomputed aggregates Can compute aggregate on (item-name, color) from an aggregate on (item-name, color, size) For all but a few “non-decomposable” aggregates such as median is cheaper than computing it from scratch Several optimizations available for computing multiple aggregates Can compute aggregate on (item-name, color) from an aggregate on (item-name, color, size) Can compute aggregates on (item-name, color, size), (item-name, color) and (item-name) using a single sorting of the base data Extended Aggregation in SQL:1999 Extended Aggregation (Cont.) Relational representation of cross-tab that we saw earlier, but with null in place of all, can be computed by select item-name, color, sum(number) from sales group by cube(item-name, color) The function grouping() can be applied on an attribute Returns 1 if the value is a null value representing all, and returns 0 in all other cases. select item-name, color, size, sum(number), grouping(item-name) as item-name-flag, grouping(color) as color-flag, grouping(size) as size-flag,from salesgroup by cube(item-name, color, size) Can use the function decode() in the select clause to replace such nulls by a value such as all E.g. replace item-name in first query by  decode( grouping(item-name), 1, ‘all’, item-name)