SQL ROLAP aggregation (Data Mining)


Example

Description

The SQL standard provides two additional aggregate operators. These use the polymorphic value "ALL" to denote the set of all values ​​that an attribute can take. The two operators are:

  • with data cube that it provides all possible combinations than the argument attributes of the clause.
  • with roll up that it provides the aggregates obtained by considering the attributes in order from left to right compared how they are listed in the argument of the clause.

SQL standard versions that support these features: 1999,2003,2006,2008,2011.

Examples

Consider this table:

FoodBrandTotal_amount
PastaBrand1100
PastaBrand2250
PizzaBrand2300

With cube

select Food,Brand,Total_amount
from Table
group by Food,Brand,Total_amount with cube
FoodBrandTotal_amount
PastaBrand1100
PastaBrand2250
PastaALL350
PizzaBrand2300
PizzaALL300
ALLBrand1100
ALLBrand2550
ALLALL650

With roll up

select Food,Brand,Total_amount
from Table
group by Food,Brand,Total_amount with roll up
FoodBrandTotal_amount
PastaBrand1100
PastaBrand2250
PizzaBrand2300
PastaALL350
PizzaALL300
ALLALL650