In addition to the subtotals generated by the ROLLUP
extension, the CUBE
extension will generate subtotals for all combinations of the dimensions specified. If "n" is the number of columns listed in the CUBE
, there will be 2n subtotal combinations.
[@more@]
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; FACT_1_ID FACT_2_ID SALES_VALUE ---------- ---------- ----------- 1 1 4363.55 1 2 4794.76 1 3 4718.25 1 4 5387.45 1 5 5027.341 24291.352 1 5652.84 2 2 4583.02 2 3 5555.77 2 4 5936.67 2 5 4508.742 26237.04 50528.39
SELECT fact_1_id, fact_2_id, fact_3_id, SUM(sales_value) AS sales_value FROM dimension_tab GROUP BY ROLLUP (fact_1_id, fact_2_id, fact_3_id) ORDER BY fact_1_id, fact_2_id, fact_3_id;
SELECT fact_1_id, fact_2_id, fact_3_id, SUM(sales_value) AS sales_value FROM dimension_tab GROUP BY fact_1_id, ROLLUP (fact_2_id, fact_3_id) ORDER BY fact_1_id, fact_2_id, fact_3_id;
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; FACT_1_ID FACT_2_ID SALES_VALUE ---------- ---------- ----------- 1 1 4363.55 1 2 4794.76 1 3 4718.25 1 4 5387.45 1 5 5027.341 24291.352 1 5652.84 2 2 4583.02 2 3 5555.77 2 4 5936.67 2 5 4508.742 26237.04 1 10016.39 2 9377.78 3 10274.02 4 11324.12 5 9536.08 50528.39
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25556693/viewspace-1049100/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25556693/viewspace-1049100/