"Some cause
happiness wherever they go; others, whenever they go." -
Oscar Wilde (1854-1900) |
Read
first then play the video:
SQL-VIDEO -Data
Warehousing and use of ROLLUP and CUBE functions
The ROLLUP and CUBE
function
Hands-On
Data warehousing is a focus
in your organization. Management needs more reports in order to truly
understand their data. You have been assigned to write simple SQL
statement using the ROLLUP operator to create salary subtotals
for each type of job per year. Also, you should use the CUBE operator
to enable a simple statement to calculate all possible combinations of
the types of jobs and year for the research department only.
Notice: A select statement
using the ROLLUP operator returns both "regular row" and "super-aggregate
rows." Super-aggregate rows are rows that contain a sub-total value.
A select statement using
the CUBE operator returns cross-tabulation values, thus produces
totals in all possible dimensions, and is used for warehousing
aggregated data reports.
The report layout printout:
The ROLLUP layout output:
Year
JOB SUM(SAL) COUNT(*)
----
--------- ---------- ----------
80
CLERK
1050
1
80
1050 1
81 ANALYST 3150 1
81
CLERK 997.5 1
81
MANAGER 9160.45 3
81
PRESIDENT 5250 1
81
SALESMAN 11609.82 4
81
30167.77 10
82
CLERK 1365 1
82
1365 1
87
ANALYST 2000 1
87
CLERK 1337.06 1
87
3337.06 2
35919.83
14
The CUBE layout output:
Year JOB SUM(SAL) COUNT(*)
---- --------- ----------
----------
80 CLERK 1050 1
80 1050 1
81 ANALYST 3150 1
81 MANAGER 3123.75 1
81 6273.75 2
87 ANALYST 2000 1
87
CLERK 1337.06 1
87 3337.06 2
ANALYST 5150 2
CLERK 2387.06 2
MANAGER 3123.75 1
10660.81 5
Introduction
Oracle added the ROLLUP and
CUBE functions in Oracle9i to the GROUP BY clause of the SELECT
statement. The ROLLUP function creates subtotals at any level of
aggregation from the most detailed up to a grand total. The CUBE
function is similar to the ROLLUP function, but it calculates all
possible combinations of subtotals. Look at the following example to
have a better understanding of these two powerful functions. We use
these two functions a lot when we have a warehousing application or
needs.
Connect to SQL*PLUS as the
iself user.
SQL> CONNECT iself/schooling
Set the pagesize to 55 and
the linesize to 100.
SQL> SET PAGESIZE 55
SQL> SET LINESIZE 100
Alter the EMP table, and
add a column to store the year that employees were hired.
SQL> ALTER TABLE emp ADD (year
VARCHAR2(2))
SQL> /
Set the column year
heading.
SQL> COLUMN year FORMAT a4 HEADING
"Year"
Update the EMP table to set
the year each employee was hired, into the newly created YEAR column.
SQL> UPDATE emp
SET year = TO_CHAR(hiredate,'YY')
SQL> /
SQL> commit;
ROLLUP grouping option
Query an aggregation sum
report for each job, in each year, using the ROLLUP grouping option.
SQL> SELECT year, job, SUM(sal), COUNT(*)
FROM emp
GROUP BY ROLLUP (year, job)
SQL> /
This is an example of a GROUP BY ROLLUP option.
CUBE grouping option
Query an aggregation sum
report for each job, in each year using the CUBE option group for the
research department only.
SQL> SELECT year, job, SUM(sal), COUNT(*)
FROM emp
WHERE deptno = 20
GROUP BY CUBE (year, job)
SQL> /
This is an example of a GROUP BY CUBE option.
DROP column
Drop the year column
SQL> ALTER TABLE emp DROP (year)
SQL> /
You have dropped the year column so that you can perform
this exercise over and over again.
"I am ready to
meet my Maker. Whether my Maker is prepared for the great ordeal
of meeting me is another matter." - Sir Winston Churchill
(1874-1965) |
Questions:
Q: What does the ROLLUP
operator?
Q: What does the CUBE
operator?
Q: What are the differences
between the CUBE and ROLLUP functions?
Q: What environments may
use the CUBE and ROLLUP functions most?
Q: Write a query to list an
aggregation sum report for each job, in each year, using the ROLLUP
grouping option.
Q: Write a query to list an
aggregation sum report for each job, in each year, using the CUBE
grouping option.
|