| 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 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 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); | |