1 / 4

Advanced Aggregation Operations in SQL: Cube vs. Rollup

Understand SQL Cube and Rollup operations for advanced aggregation in databases. Learn how to compute unions on subsets of attributes easily. Examples and explanations provided!

kirk-underwood
Download Presentation

Advanced Aggregation Operations in SQL: Cube vs. Rollup

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. The cube operation computes union of group by’s on every subset of the specified attributes E.g. consider the query select item-name, color, size, sum(number)fromsalesgroup by cube(item-name, color, size) This computes the union of eight different groupings of the sales relation: { (item-name, color, size), (item-name, color), (item-name, size), (color, size), (item-name), (color), (size), ( ) } where ( ) denotes an empty group by list. For each grouping, the result contains the null value for attributes not present in the grouping. Extended Aggregation in SQL:1999

  2. SQL Cube Example SELECT Type, Store, SUM(Number) as NumberFROM Pets GROUP BY CUBE(type,store) Generates summaries on (type, store), (type), (store), and () From: http://databases.about.com/od/sql/l/aacuberollup.htm

  3. The rollup construct generates union on every prefix of specified list of attributes E.g. select item-name, color, size, sum(number)from salesgroup by rollup(item-name, color, size) Generates union of four groupings: { (item-name, color, size), (item-name, color), (item-name), ( ) } Rollup can be used to generate aggregates at multiple levels of ahierarchy. Extended Aggregation (Cont.)

  4. SQL Rollup Example SELECT Type, Store, SUM(Number) as NumberFROM Pets GROUP BY ROLLUP(type, store) Generates summaries on (type, store), (type), and () From: http://databases.about.com/od/sql/l/aacuberollup.htm

More Related