|
|
|
Oracle:
Analytical SQL functions - rollup
- cube
Oracle has
introduced some exciting extensions to ANSI
SQL to allow us to quickly compute
aggregations and rollups. These new
statements include:
These
simple SQL operators allow us to create easy
aggregations directly inside the SQL without
having to employ SQL*Plus break and compute
statements. Let’s start by examining the
ROLLUP syntax.
·
Creating tabular aggregates
with ROLLUP
ROLLUP
enables an SQL statement to calculate
multiple levels of subtotals across a
specified group of dimensions. It also
calculates a grand total. ROLLUP is a simple
extension to the GROUP BY clause, so its
syntax is extremely easy to use.
SELECT
deptno,
job,
count(*),
sum(sal)
FROM
emp
GROUP BY
ROLLUP(deptno,job);
DEPTNO JOB COUNT(*)
SUM(SAL)
--------- --------- ---------
---------
10 CLERK 1
1300
10 MANAGER 1
2450
10 PRESIDENT 1
5000
10 3
8750
20 ANALYST 2
6000
20 CLERK 2
1900
20 MANAGER 1
2975
20 5
10875
·
Create cross-tabular reports
with CUBE
In
multidimensional jargon, a “cube” is a
cross-tabulated summary of detail rows.
CUBE enables a SELECT statement to calculate
subtotals for all possible combinations of a
group of dimensions. It also calculates a
grand total. This is the set of information
typically needed for all cross-tabular
reports, so CUBE can calculate a
cross-tabular report with a single select
statement.
Note in the
example below that totals are calculated for
each department, and also for each job
category.
SELECT
deptno,
job,
count(*),
sum(sal)
FROM
emp
GROUP BY
CUBE(deptno,job);
DEPTNO JOB COUNT(*) SUM(SAL)
--------- --------- ---------
---------
10 CLERK 1
1300
10 MANAGER 1
2450
10 PRESIDENT 1
5000
10 3
8750
20 ANALYST 2
6000
20 CLERK 2
1900
20 MANAGER 1
2975
20 5
10875
30 CLERK 1
950
30 MANAGER 1
2850
30 SALESMAN 4
5600
30 6
9400
ANALYST 2
6000
CLERK 4
4150
MANAGER 3
8275
PRESIDENT 1
5000
SALESMAN 4
5600
14
29025
|
|
|
|
|
|
