文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1616
ROLLUP Extension to GROUP BY
ROLLUP enables a SELECTstatement to calculate multiple levels of subtotals across aspecified group of dimensions. It also calculates a grand total.ROLLUP is a simple extension to the GROUPBY clause, so its syntax is extremely easy to use. TheROLLUP extension is highly efficient, adding minimaloverhead to a query.
The action of ROLLUP is straightforward: it createssubtotals that roll up from the most detailed level to a grandtotal, following a grouping list specified in theROLLUP clause. ROLLUP takes as itsargument an ordered list of grouping columns. First, itcalculates the standard aggregate values specified in theGROUP BY clause. Then, it createsprogressively higher-level subtotals, moving from right to leftthrough the list of grouping columns. Finally, it creates a grandtotal.
---首先他会计算出指定在groupby字句中标准合计值，然后他会渐进地创建更高级别的合计值，是通过在group by字句列表中多列的右边第一个列开始往左边移动。最纵，它会计算总和。
ROLLUP creates subtotals at n+1 levels,where n is the number of grouping columns.
ROLLUP on grouping columns of time,region, and department(n=3), the resultset will include rows at four aggregation levels.
You might want to compress your data when usingROLLUP. This is particularly useful when there are fewupdates to older partitions.
CUBE Extension to GROUP BY
CUBE takesa specified set of grouping columns and creates subtotals for allof their possible combinations. In terms of multidimensionalanalysis, CUBE generates all the subtotals that couldbe calculated for a data cube with the specified dimensions. If youhave specified CUBE(time, region,department), the result set will include all thevalues that would be included in an equivalent ROLLUPstatement plus additional combinations. For instance, in Figure 20-1, the departmental totals across regions (279,000and 319,000) would not be calculated by a ROLLUP(time,region, department) clause, but theywould be calculated by a CUBE(time,region, department) clause. Ifn columns are specified for a CUBE, therewill be 2 to the n combinations of subtotals returned.Example 20-4 gives an example of a three-dimension cube. SeeOracleDatabase SQL Reference for syntax and restrictions.
When to Use CUBE
Consider UsingCUBE in any situation requiring cross-tabular reports.The data needed for cross-tabular reports can be generated with asingle SELECT using CUBE. LikeROLLUP, CUBE can be helpful in generatingsummary tables. Note that population of summary tables is evenfaster if the CUBE query executes in parallel.
CUBE is typically most suitable in queries that usecolumns from multiple dimensions rather than columns representingdifferent levels of a single dimension. For instance, a commonlyrequested cross-tabulation might need subtotals for all thecombinations of month, state, and product. These are threeindependent dimensions, and analysis of all possible subtotalcombinations is commonplace. In contrast, a cross-tabulationshowing all possible combinations of year, month, and day wouldhave several values of limited interest, because there is a naturalhierarchy in the time dimension. Subtotals such as profit by day ofmonth summed across year would be unnecessary in most analyses.Relatively few users need to ask "What were the total sales for the16th of each month across the year?" See "Hierarchy Handling in ROLLUP and CUBE" for an example ofhandling rollup calculations efficiently.
CUBE Syntax
CUBE appears in the GROUPBY clause in a SELECT statement. Its formis:
SELECT … GROUP BY CUBE (grouping_column_reference_list)
SELECT channel_desc, calendar_month_desc, countries.country_iso_code,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries
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 = countries.country_id
AND channels.channel_desc IN
('Direct Sales', 'Internet') AND times.calendar_month_desc IN
('2000-09', '2000-10') AND countries.country_iso_code IN ('GB', 'US')
GROUP BY CUBE(channel_desc, calendar_month_desc, countries.country_iso_code);
CHANNEL_DESC CALENDAR CO SALES$
-------------------- -------- -- --------------
1,790,032
GB 208,257
US 1,581,775
2000-09 864,217
2000-09 GB 101,792
2000-09 US 762,425
2000-10 925,815
2000-10 GB 106,465
2000-10 US 819,351
Internet 292,387
Internet GB 31,109
Internet US 261,278
Internet 2000-09 140,793
Internet 2000-09 GB 16,569
Internet 2000-09 US 124,224
Internet 2000-10 151,593
Internet 2000-10 GB 14,539
Internet 2000-10 US 137,054
Direct Sales 1,497,646
Direct Sales GB 177,148
Direct Sales US 1,320,497
Direct Sales 2000-09 723,424
Direct Sales 2000-09 GB 85,223
Direct Sales 2000-09 US 638,201
Direct Sales 2000-10 774,222
Direct Sales 2000-10 GB 91,925
Direct Sales 2000-10 US 682,297
This query illustrates CUBE aggregation acrossthree dimensions.
Partial CUBE
PartialCUBE resembles partial ROLLUP in that youcan limit it to certain dimensions and precede it with columnsoutside the CUBE operator. In this case, subtotals ofall possible combinations are limited to the dimensions within thecube list (in parentheses), and they are combined with thepreceding items in the GROUP BY list.
The syntax for partial CUBE is as follows:
GROUP BY expr1, CUBE(expr2, expr3)
This syntax example calculates 2*2, or 4, subtotals. Thatis:
-
(
expr1,expr2,expr3) -
(
expr1,expr2) -
(
expr1,expr3) -
(
expr1)
Using the sales database, you can issue thefollowing statement:
SELECT channel_desc, calendar_month_desc, countries.country_iso_code,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries
WHERE sales.time_id = times.time_id
AND sales.cust_id = customers.cust_id
AND customers.country_id=countries.country_id
AND sales.channel_id = channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND countries.country_iso_code IN ('GB', 'US')
GROUP BY channel_desc, CUBE(calendar_month_desc, countries.country_iso_code);
CHANNEL_DESC CALENDAR CO SALES$
-------------------- -------- -- --------------
Internet 292,387
Internet GB 31,109
Internet US 261,278
Internet 2000-09 140,793
Internet 2000-09 GB 16,569
Internet 2000-09 US 124,224
Internet 2000-10 151,593
Internet 2000-10 GB 14,539
Internet 2000-10 US 137,054
Direct Sales 1,497,646
Direct Sales GB 177,148
Direct Sales US 1,320,497
Direct Sales 2000-09 723,424
Direct Sales 2000-09 GB 85,223
Direct Sales 2000-09 US 638,201
Direct Sales 2000-10 774,222
Direct Sales 2000-10 GB 91,925
Direct Sales 2000-10 US 682,297
文档地址：
Specify the GROUP BY clause if youwant the database to group the selected rows based on the value ofexpr(s)for each row and return a single row of summary information foreach group. If this clause contains CUBE orROLLUP extensions, then the database producessuperaggregate groupings in addition to the regular groupings.
Expressions in the GROUP BY clause cancontain any columns of the tables, views, or materialized views inthe FROM clause, regardless of whether the columnsappear in the select list.
The GROUP BY clause groups rows butdoes not guarantee the order of the result set. To order thegroupings, use the ORDER BY clause.
See Also:
-
OracleData Warehousing Guide for an expanded discussion andexamples of using SQL grouping syntax for data aggregation
-
the GROUP_ID, GROUPING, and GROUPING_ID functions for examples
ROLLUP The ROLLUP operation in thesimple_grouping_clause groupsthe selected rows based on the values of the first n, n-1, n-2, ...0 expressions in the GROUP BYspecification, and returns a single row of summary for each group.You can use the ROLLUP operation to producesubtotal values by using it with the SUMfunction. When used with SUM, ROLLUPgenerates subtotals from the most detailed level to the grandtotal. Aggregate functions such as COUNT can be usedto produce other kinds of superaggregates.
For example, given three expressions (n=3) in theROLLUP clause of the simple_grouping_clause, theoperation results in n+1 = 3+1 = 4 groupings.
Rows grouped on the values of the firstnexpressions are called regular rows, and the othersare called superaggregaterows.--在起初的N个表达式的值被分组的行当中被称为常规行，剩下其它的行称为超聚集行。
CUBE The CUBE operation in thesimple_grouping_clause groupsthe selected rows based on the values of all possible combinationsof expressions in the specification. It returns a single row ofsummary information for each group. You can use theCUBE operation to produce cross-tabulationvalues.
For example, given three expressions (n=3) in theCUBE clause of the simple_grouping_clause, theoperation results in 2n = 23 = 8groupings.
Rows grouped on the values of n expressions are calledregular rows, and the rest are calledsuperaggregaterows..--在起初的N个表达式的值被分组的行当中被称为常规行，剩下其它的行称为超聚集行。
参考别人的文章
一、Oracle分组函数rollup,cube
rollup(字段1,字段2):
cube(字段1,字段2):
rollup(col1, col2,...) 和 cube(col1, col2,...) 用法区别在 cube 在rollup 汇总的记录集上，还会增加对 col2 等字段的汇总；
ROLLUP只对第一个参数(字段)进行汇总，CUBE可以对参数(字段)依次汇总，所以ROLLUP中参数个数只有一个会起作用(且排名在前的参数)。
Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。
1、ROLLUP(A, B, C):
首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUPBY，最后对全表进行GROUP BY操作。 --有N+1种组合方式。见上英文
2、CUBE(A, B, C)，则首先会对(A、B、C)进行GROUPBY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUPBY操作。--有2的N次方种组合方式。
3、CUBE 和 ROLLUP 之间的区别在于：
CUBE 生成的结果集显示了所选列中值的所有组合的聚合(注意:这是我们数学当中讲的组合，而不是排列。)。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
例子：
create table student(
cgrade varchar2(64),
cclass varchar2(64),
cgroup varchar2(64),
stu int
)
insert into student(cgrade,cclass,cgroup,stu)values('1','1','1',10);
insert into student(cgrade,cclass,cgroup,stu)values('1','2','1',10);
insert into student(cgrade,cclass,cgroup,stu)values('1','2','2',20);
insert into student(cgrade,cclass,cgroup,stu)values('2','1','1',30);
insert into student(cgrade,cclass,cgroup,stu)values('2','2','2',40);
select * from student;
select cgrade,cclass,sum(stu) from student group bycgrade,cclass;
select cgrade,cclass,sum(stu) from student group bycube(cgrade,cclass);
selectdecode(grouping(cgrade),1,'学校人数',0,cgrade),decode(grouping(cclass)+grouping(cgrade),1,'年级人数',0,cclass),sum(stu)from student group by rollup(cgrade,cclass);
扫一扫
603
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
