oracle中rollup和cube的用法

文档地址：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 theGROUPBY 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 theGROUPBY 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.

 

 

 For instance, if a query specifiesROLLUP on grouping columns oftime,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 specifiedCUBE(time, region,department), the result set will include all thevalues that would be included in an equivalentROLLUPstatement plus additional combinations. For instance, in Figure 20-1, the departmental totals across regions (279,000and 319,000) would not be calculated by aROLLUP(time,region, department) clause, but theywould be calculated by aCUBE(time,region, department) clause. Ifn columns are specified for aCUBE, 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 asingleSELECT using CUBE. LikeROLLUP, CUBE can be helpful in generatingsummary tables. Note that population of summary tables is evenfaster if theCUBE 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 aSELECT statement. Its formis:

SELECT …  GROUP BY CUBE (grouping_column_reference_list)

Example 20-4 CUBE

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, countriesWHERE 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,351Internet                                292,387Internet                      GB         31,109Internet                      US        261,278Internet             2000-09            140,793Internet             2000-09  GB         16,569Internet             2000-09  US        124,224Internet             2000-10            151,593Internet             2000-10  GB         14,539Internet             2000-10  US        137,054Direct Sales                          1,497,646Direct Sales                  GB        177,148Direct Sales                  US      1,320,497Direct Sales         2000-09            723,424Direct Sales         2000-09  GB         85,223Direct Sales         2000-09  US        638,201Direct Sales         2000-10            774,222Direct Sales         2000-10  GB         91,925Direct 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 theCUBE 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 theGROUP 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)

Example 20-5 PartialCUBE

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, countriesWHERE 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,387Internet                      GB         31,109Internet                      US        261,278Internet             2000-09            140,793Internet             2000-09  GB         16,569Internet             2000-09  US        124,224Internet             2000-10            151,593Internet             2000-10  GB         14,539Internet             2000-10  US        137,054Direct Sales                          1,497,646Direct Sales                  GB        177,148Direct Sales                  US      1,320,497Direct Sales         2000-09            723,424Direct Sales         2000-09  GB         85,223Direct Sales         2000-09  US        638,201Direct Sales         2000-10            774,222Direct Sales         2000-10  GB         91,925Direct Sales         2000-10  US        682,297

 

 

文档地址： http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2182483

 

group_by_clause

 

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 intheFROM 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 theORDER 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

• "Using the GROUP BY Clause: Examples"

 

ROLLUP TheROLLUP operation in thesimple_grouping_clause groupsthe selected rows based on the values of the first n, n-1, n-2, ...0 expressions in theGROUP BYspecification, and returns a single row of summary for each group.You can use theROLLUP operation to producesubtotal values by using it with theSUMfunction. When used with SUM, ROLLUPgenerates subtotals from the most detailed level to the grandtotal. Aggregate functions such asCOUNT can be usedto produce other kinds of superaggregates.

 

For example, given three expressions (n=3) in theROLLUP clause of thesimple_grouping_clause, theoperation results in n+1 = 3+1 = 4 groupings.

 

Rows grouped on the values of the firstnexpressions are calledregular rows, and the othersare called superaggregaterows.--在起初的N个表达式的值被分组的行当中被称为常规行，剩下其它的行称为超聚集行。

 

 

See Also:

OracleData Warehousing Guide for information on usingROLLUP with materialized views

 

CUBE TheCUBE 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 producecross-tabulationvalues.

 

For example, given three expressions (n=3) in theCUBE clause of thesimple_grouping_clause, theoperation results in 2ⁿ = 2³ = 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);

 

 

二、OracleROLLUP和CUBE用法

Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。

如果是Group by ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUPBY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUPBY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。grouping_id()可以美化效果。除了使用GROUPING函数，还可以使用GROUPING_ID来标识GROUPBY的结果。

也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，……这样任意按自己想要的形式结合统计数据，非常方便。