12.2 CUBE
The CUBE extension
of the GROUP BY clause takes
aggregation one step further than ROLLUP. The CUBE operation
generates subtotals for all possible
combinations of the grouping columns. Therefore, output of a CUBE
operation will contain all subtotals produced by an equivalent ROLLUP
operation and also some additional subtotals. For example, if you are
performing ROLLUP on columns region and month, you will get subtotals
for all months for each region, and a grand total. However, if you
perform the corresponding CUBE, you will get:
The regular rows produced by the GROUP BY clause Subtotals for all months on each region A subtotal for all regions on each month A grand total
Like ROLLUP, CUBE is an extension of the GROUP BY clause, and can
appear in a query only along with a GROUP BY clause. The syntax of
CUBE is:
SELECT ...
FROM ...
GROUP BY CUBE (list of grouping columns)
For example, the following query returns subtotals for all
combinations of regions and months in the ORDER table:
SELECT R.NAME REGION, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
GROUP BY CUBE(R.NAME, O.MONTH);
REGION MONTH SUM(O.TOT_SALES)
-------------------- --------- ----------------
Mid-Atlantic January 1832091
Mid-Atlantic February 1286028
Mid-Atlantic March 1911093
Mid-Atlantic April 1623438
Mid-Atlantic May 1778805
Mid-Atlantic June 1504455
Mid-Atlantic July 1820742
Mid-Atlantic August 1381560
Mid-Atlantic September 1178694
Mid-Atlantic October 1530351
Mid-Atlantic November 1598667
Mid-Atlantic December 1477374
Mid-Atlantic 18923298
New England January 1527645
New England February 1847238
New England March 1699449
New England April 1792866
New England May 1698855
New England June 1510062
New England July 1678002
New England August 1642968
New England September 1726767
New England October 1648944
New England November 1384185
New England December 1599942
New England 19756923
SouthEast US January 1137063
SouthEast US February 1855269
SouthEast US March 1967979
SouthEast US April 1830051
SouthEast US May 1983282
SouthEast US June 1705716
SouthEast US July 1670976
SouthEast US August 1436295
SouthEast US September 1905633
SouthEast US October 1610523
SouthEast US November 1661598
SouthEast US December 1841100
SouthEast US 20605485
January 4496799
February 4988535
March 5578521
April 5246355
May 5460942
June 4720233
July 5169720
August 4460823
September 4811094
October 4789818
November 4644450
December 4918416
59285706
52 rows selected.
Note that the output contains not only the subtotals for each region,
but also the subtotals for each month. You can get the same result
from a query without the CUBE operation. However, that query would be
lengthy and complex and, of course, very inefficient. Such a query
would look as follows:
SELECT R.NAME REGION, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
GROUP BY R.NAME, O.MONTH
UNION ALL
SELECT R.NAME REGION, NULL, SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
GROUP BY R.NAME
UNION ALL
SELECT NULL, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH, SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
GROUP BY O.MONTH
UNION ALL
SELECT NULL, NULL, SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID;
REGION MONTH SUM(O.TOT_SALES)
-------------------- --------- ----------------
Mid-Atlantic January 1832091
Mid-Atlantic February 1286028
Mid-Atlantic March 1911093
Mid-Atlantic April 1623438
Mid-Atlantic May 1778805
Mid-Atlantic June 1504455
Mid-Atlantic July 1820742
Mid-Atlantic August 1381560
Mid-Atlantic September 1178694
Mid-Atlantic October 1530351
Mid-Atlantic November 1598667
Mid-Atlantic December 1477374
New England January 1527645
New England February 1847238
New England March 1699449
New England April 1792866
New England May 1698855
New England June 1510062
New England July 1678002
New England August 1642968
New England September 1726767
New England October 1648944
New England November 1384185
New England December 1599942
SouthEast US January 1137063
SouthEast US February 1855269
SouthEast US March 1967979
SouthEast US April 1830051
SouthEast US May 1983282
SouthEast US June 1705716
SouthEast US July 1670976
SouthEast US August 1436295
SouthEast US September 1905633
SouthEast US October 1610523
SouthEast US November 1661598
SouthEast US December 1841100
Mid-Atlantic 18923298
New England 19756923
SouthEast US 20605485
January 4496799
February 4988535
March 5578521
April 5246355
May 5460942
June 4720233
July 5169720
August 4460823
September 4811094
October 4789818
November 4644450
December 4918416
59285706
52 rows selected.
Since a CUBE produces aggregate results for all possible combinations
of the grouping columns, the output of a query using CUBE is
independent of the order of columns in the CUBE operation, if
everything else remains the same. This is not the case with ROLLUP.
If everything else in the query remains the same, ROLLUP(a,b) will
produce a slightly different result set than ROLLUP(b,a). However,
the result set of CUBE(a,b) will be the same as that of CUBE(b,a).
The following example illustrates this by taking the example of the
beginning of this section and reversing the order of columns in the
CUBE operation.
SELECT R.NAME REGION, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
GROUP BY CUBE(O.MONTH, R.NAME);
REGION MONTH SUM(O.TOT_SALES)
-------------------- --------- ----------------
Mid-Atlantic January 1832091
New England January 1527645
SouthEast US January 1137063
January 4496799
Mid-Atlantic February 1286028
New England February 1847238
SouthEast US February 1855269
February 4988535
Mid-Atlantic March 1911093
New England March 1699449
SouthEast US March 1967979
March 5578521
Mid-Atlantic April 1623438
New England April 1792866
SouthEast US April 1830051
April 5246355
Mid-Atlantic May 1778805
New England May 1698855
SouthEast US May 1983282
May 5460942
Mid-Atlantic June 1504455
New England June 1510062
SouthEast US June 1705716
June 4720233
Mid-Atlantic July 1820742
New England July 1678002
SouthEast US July 1670976
July 5169720
Mid-Atlantic August 1381560
New England August 1642968
SouthEast US August 1436295
August 4460823
Mid-Atlantic September 1178694
New England September 1726767
SouthEast US September 1905633
September 4811094
Mid-Atlantic October 1530351
New England October 1648944
SouthEast US October 1610523
October 4789818
Mid-Atlantic November 1598667
New England November 1384185
SouthEast US November 1661598
November 4644450
Mid-Atlantic December 1477374
New England December 1599942
SouthEast US December 1841100
December 4918416
Mid-Atlantic 18923298
New England 19756923
SouthEast US 20605485
59285706
52 rows selected.
This query produced the same results as the earlier query; only the
order of the rows is different.
To exclude some subtotals from the output, you can do
a
partial CUBE, (similar to a partial ROLLUP) by
taking out column(s) from the CUBE operation and putting them into
the GROUP BY clause. Here's an example:
SELECT R.NAME REGION, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
GROUP BY R.NAME CUBE(O.MONTH);
REGION MONTH SUM(O.TOT_SALES)
-------------------- --------- ----------------
Mid-Atlantic January 1832091
Mid-Atlantic February 1286028
Mid-Atlantic March 1911093
Mid-Atlantic April 1623438
Mid-Atlantic May 1778805
Mid-Atlantic June 1504455
Mid-Atlantic July 1820742
Mid-Atlantic August 1381560
Mid-Atlantic September 1178694
Mid-Atlantic October 1530351
Mid-Atlantic November 1598667
Mid-Atlantic December 1477374
Mid-Atlantic 18923298
New England January 1527645
New England February 1847238
New England March 1699449
New England April 1792866
New England May 1698855
New England June 1510062
New England July 1678002
New England August 1642968
New England September 1726767
New England October 1648944
New England November 1384185
New England December 1599942
New England 19756923
SouthEast US January 1137063
SouthEast US February 1855269
SouthEast US March 1967979
SouthEast US April 1830051
SouthEast US May 1983282
SouthEast US June 1705716
SouthEast US July 1670976
SouthEast US August 1436295
SouthEast US September 1905633
SouthEast US October 1610523
SouthEast US November 1661598
SouthEast US December 1841100
SouthEast US 20605485
39 rows selected.
If you compare the results of the partial CUBE operation with that of
the full CUBE operation, discussed at the beginning of this section,
you will notice that the partial CUBE has excluded the subtotals for
each month and the grand total from the output. If you want to retain
the subtotals for each month, but want to exclude the subtotals for
each region, you can swap the position of R.NAME and O.MONTH in the
GROUP BY...CUBE clause, as shown here:
SELECT R.NAME REGION, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
GROUP BY O.MONTH CUBE(R.NAME);
One interesting thing to note is that if you have one column in the
CUBE operation, it produces the same result as the ROLLUP operation.
Therefore, the following two queries produce identical results:
SELECT R.NAME REGION, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
GROUP BY R.NAME CUBE(O.MONTH);
SELECT R.NAME REGION, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,
SUM(O.TOT_SALES)
FROM ORDERS O, REGION R
WHERE R.REGION_ID = O.REGION_ID
GROUP BY R.NAME ROLLUP(O.MONTH);
|
