陕西师范大学计算机科学学院.pptVIP

Copyright: Silberschatz, Korth and Sudarshan Chapter 22: Advanced Querying and Information Retrieval 陕西师范大学计算机科学学院 数据库原理与应用课程组 Chapter 22: Advanced Querying and Information Retrieval Decision Support Systems Decision-Support Systems: Overview Data Analysis and OLAP Cross Tabulation of sales by item-name and color Relational Representation of Crosstabs Three-Dimensional 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 SQL-92 aggregation quite limited Many useful aggregates are either very hard or impossible to specify Data cube Complex aggregates (median, variance) binary aggregates (correlation, regression curves) ranking queries (“assign each student a rank based on the total marks” SQL:1999 OLAP extensions provide a variety of aggregation functions to address above limitations Supported by several databases, including Oracle and IBM DB2 Extended Aggregation in SQL:1999 Extended Aggregation (Cont.) Relational representation of crosstab 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 f