Friday, August 2, 2019

What is the difference between CUBE and ROLLUP operators

CUBE is a Group By extension.
You can use with CUBE for generating summaries of all possible combinations of Group By columns, as well as a grand total.
the CUBE extension will generate subtotals for all combinations of the dimensions specified.

Ex:
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY CUBE (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

ROLLUP   is a Group By extension.
In addition to the regular aggregation results we expect from the GROUP BY clause, the ROLLUP extension produces group subtotals from right to left and a grand total.

Ex:
SELECT fact_1_id,
       fact_2_id,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY ROLLUP (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;


List out the difference between CUBE operator and ROLLUP operator

CUBE generates a result set that represents aggregates for all combinations of values in the selected columns. 
ROLLUP generates a result set that represents aggregates for a hierarchy of values in the selected columns.

No comments:

Post a Comment

AME (Approval Management Engine)

AME (Approval Management Engine) : AME Stands for Oracle Approval Management Engine. AME is a self service web application that enables...