|
|
|
|
|
|
| Search the Reference Library pages: |
|
|
|
|
| Oracle Rollup, Cube, and Grouping Sets |
| Version 10.2 |
| |
| General
|
Rollup Note: ROLLUP
enables a SELECT 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. The ROLLUP extension is highly
efficient, adding minimal overhead to a query.
The action of ROLLUP is straight forward: it creates subtotals that roll up from the most
detailed level to a grand total, following a grouping list specified in the ROLLUP clause.
ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the
standard aggregate values specified in the GROUP BY clause. Then, it creates progressively
higher-level subtotals, moving from right to left through the list of grouping columns.
Finally, it creates a grand total.
ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns. For
instance, if a query specifies ROLLUP on grouping columns of time, region, and department
(n=3), the result set will include rows at four aggregation levels. |
Cube Note: CUBE takes a
specified set of grouping columns and creates subtotals for all of their possible
combinations. In terms of multidimensional analysis, CUBE generates all the subtotals that
could be calculated for a data cube with the specified dimensions. If you have specified
CUBE(time, region, department), the result set will include all the values that would be
included in an equivalent ROLLUP statement plus additional combinations.
Consider Using CUBE in any situation requiring cross-tabular reports. The data needed for
cross-tabular reports can be generated with a single SELECT using CUBE. Like ROLLUP, CUBE
can be helpful in generating summary tables. Note that population of summary tables is
even faster if the CUBE query executes in parallel.
CUBE is typically most suitable in queries that use columns from multiple dimensions
rather than columns representing different levels of a single dimension. For instance, a
commonly requested cross-tabulation might need subtotals for all the combinations of
month, state, and product. These are three independent dimensions, and analysis of all
possible subtotal combinations is commonplace. In contrast, a cross-tabulation showing all
possible combinations of year, month, and day would have several values of limited
interest, because there is a natural hierarchy in the time dimension. Subtotals such as
profit by day of month summed across year would be unnecessary in most analyses. |
| |
| Create demo schema |
@$ORACLE_HOME\demo\schema\sales_history\sh_main.sql |
| |
| ROLLUP |
| Full Rollup Demo SQL Statement |
col country_name format a25
SELECT ch.channel_desc, t.calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND cu.country_id = co.country_id
AND ch.channel_desc IN ('Direct Sales','Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_name LIKE 'U%'
GROUP BY ROLLUP(ch.channel_desc, t.calendar_month_desc, co.country_name); |
| Partial Rollup Demo SQL Statement |
SELECT ch.channel_desc,
t.calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc IN ('Direct Sales','Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_name LIKE 'U%'
GROUP BY ch.channel_desc, ROLLUP(t.calendar_month_desc, co.country_name); |
| |
| GROUP_ID |
The following demonstrates a GROUP BY with repeating values and their identification with the
GROUP_ID() function |
GROUP_ID() |
CREATE TABLE grp_rep (
person_id NUMBER(3),
division VARCHAR2(3),
commission NUMBER(5));
INSERT INTO grp_rep VALUES (1,'SAM',1000);
INSERT INTO grp_rep VALUES (2,'EUR',1200);
INSERT INTO grp_rep VALUES (1,'EUR',1450);
INSERT INTO grp_rep VALUES (1,'EUR',700);
INSERT INTO grp_rep VALUES (2,'SEA',1000);
INSERT INTO grp_rep VALUES (2,'SEA',2000);
INSERT INTO grp_rep VALUES (1,'EUR',800);
COMMIT;
SELECT person_id, division, SUM(commission)
FROM grp_rep
GROUP BY person_id, division;
SELECT person_id, division, SUM(commission)
FROM grp_rep
GROUP BY person_id, ROLLUP (person_id, division);
SELECT person_id, division, SUM(commission), GROUP_ID() g
FROM grp_rep
GROUP BY person_id, ROLLUP (person_id, division);
SELECT person_id, division, SUM(commission), GROUP_ID() g
FROM grp_rep
GROUP BY person_id, ROLLUP (person_id, division)
HAVING
GROUP_ID() = 0; |
| |
| GROUPING |
Distinguishes
superaggregate rows from regular grouped rows.
Distinguish a null representing the set of all values in a superaggregate row from a null in a regular row. |
GROUPING(<expression>) |
conn
hr/hr
set linesize 121
col job format a10
SELECT DECODE(department_name, '1', 'All Departments',
department_name) AS DEPARTMENT,
DECODE(job_id, '1', 'All Jobs', job_id) AS job,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY ROLLUP (department_name, job_id);
SELECT DECODE(GROUPING(department_name), '1', 'All Departments',
department_name) AS DEPARTMENT,
DECODE(GROUPING(job_id), '1', 'All Jobs',
job_id) AS job,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY ROLLUP (department_name, job_id); |
| |
| GROUPING
SETS |
| Demo from OTN |
conn sh/sh
SELECT channel_desc, calendar_month_desc, co.country_id,
TO_CHAR(SUM(amount_sold) , '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries co
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND sales.channel_id= channels.channel_id
AND customers.country_id = co.country_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_iso_code IN ('UK', 'US')
GROUP BY GROUPING SETS(
(channel_desc, calendar_month_desc, co.country_id),
(channel_desc, co.country_id),
(calendar_month_desc, co.country_id));
SELECT channel_desc, calendar_month_desc, co.country_id,
TO_CHAR(SUM(amount_sold) , '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries co
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND sales.channel_id= channels.channel_id
AND customers.country_id = co.country_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_iso_code IN ('UK', 'US')
GROUP BY CUBE(channel_desc, calendar_month_desc, co.country_id); |
| CUBE |
Full Cube Rollup |
GROUP BY CUBE() |
conn sh/sh
col sales$ format a20
SELECT ch.channel_desc,
calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc IN ('Direct Sales', 'Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND cu.country_id = co.country_id
AND co.country_name LIKE 'U%'
GROUP BY CUBE(channel_desc, t.calendar_month_desc, co.country_name); |
Partial Cube Rollup |
conn sh/sh
SELECT ch.channel_desc,
calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc IN ('Direct Sales', 'Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND cu.country_id = co.country_id
AND co.country_name LIKE 'U%'
GROUP BY channel_desc, CUBE(t.calendar_month_desc, co.country_name); |
| |
| GROUPING_ID |
Returns a
number corresponding to the GROUPING bit vector associated with a
row.
In queries with many GROUP BY expressions,
determining the GROUP BY level of a particular row requires many
GROUPING functions, which leads to cumbersome SQL. GROUPING_ID is
useful in these cases. |
GROUPING_ID(<expression>,
<expression>, ..) |
conn sh/sh
SELECT channel_id, promo_id, SUM(amount_sold) s_sales,
GROUPING(channel_id) gc,
GROUPING(promo_id) gp
FROM sales
WHERE promo_id > 496
GROUP BY CUBE(channel_id, promo_id);
SELECT channel_id, promo_id, SUM(amount_sold) s_sales,
GROUPING(channel_id) AS GC,
GROUPING(promo_id) AS GP,
GROUPING_ID(channel_id, promo_id) AS
GCP,
GROUPING_ID(promo_id, channel_id) AS
GPC
FROM sales
WHERE promo_id > 496
GROUP BY CUBE(channel_id, promo_id); |
|
| |
|
|
| |
|
-----
|