事关CUBE ROLLUP GROUPING SETS

            事关CUBE ROLLUP GROUPING SETS（1）

　　聚合是数据仓库的基础。为了提高聚合的性能。Oracle提供了Group By 条款的扩展。
　　1． CUBE, ROLLUP扩展
　　2． 3个grouping函数
　　3． Grouping set扩展
　　CUBE ROLLUP GROUPING SETS对SQL的扩展使得查询和报告都变得简单和迅速。Rollup计算诸如sum count max min avg的函数，增加了聚合的级别.CUBE是一个类似ROLLUP的扩展，使得可以用一个语句计算所有可能的聚合。CUBE可以通过单条生成Cross-tabulation（交叉报表）报告的信息。CUBE，ROLLUP，GROUPING SETS扩展令你可以精确的对你感兴趣的group by 条款进行grouping。不运行cube操作也可以高效的从多个维度进行分析。计算一个full cube将会带来很高的负载，所以将cube替换为grouping sets可以明显地提高性能。CUBE ROLLUP GROUPING SETS可以生成单个结果集，等效于UNION ALL。为了提高性能，CUBE, ROLLUP, and GROUPING SETS可以并行进行：多进程同时运算所有的语句。这种功能使得聚合计算更加高效，因此提高了数据库性能和可测性。
　　Analyzing Across Multiple Dimensions
　　多维分析
　　在DSS系统中一个很重要的概念是多维分析：
　　从多个角度对企业的状况进行查勘。Demension，众多的维一般是时间，空间，产品，部门，渠道，企业的维度多种多样。事件和实体关联特定的维度就是所谓的facts.这些所谓的facts可以是销售，现金，利润，客户账户，或是其他一些值得记录的东东。
　　下面是一些多维需求的例子
　　1． 展示1999和2000年，所有产品的总销量，聚合级别为地域维度，从州到国家到地区。
　　2． 创建交叉报表分析1999和2000年南美洲范围的费用，包括所有的小记
　　
　　所有的需求都包含了多维。很多多维的问题需要数据的聚合，数据集的比较，通常跨越时间，地域。
　　
　　Optimized Performance
　　不仅仅是多维的问题，还有所有类型的处理可以从增强聚合功能中得到好处。事务的处理，金融制造系统-所有的这些都产生大量的生产报告需要大量的系统资源。创建这些报告减少系统负载，提升了系统性能。
　　事实上，计算机处理丛细节到高层的聚合数据可以通过优化聚合性能得到好处。
　　Oracle9i提供了扩展聚合功能，带来了很多好处。包括：
　　1． 简化代码编程
　　2． 快而高效的查询处理
　　3． 减少客户端负载和网络负载。
　　4． 由于简单的查询可以缓冲聚合
　　
　　
　　
　　
　　参考：oracle文档a96520.pdf

事关ROLLUP CUBE GROUPING SETS（2）

　　ROLLUP Extension to GROUP BY
　　Rollup可以令select 语句根据group的维计算多层小计。他也可以计算总计。Rollup是对group by的简单扩展，所以他的语法非常简单。Rollup扩展非常高效，对一个查询增加的非常少的开销。
　　Rollup的行为非常直接，它根据grouping list的Rollup条款创建小计。它从右向左的进行再聚合。
　　首先，它计算grouping条款的标准聚合。
　　然后，它按照grouping list列从右向左的顺序进行更高层的聚合。
　　最后，创建n+1层的总计。N = grouping列的数量。
　　例如，一个查询用Rollup在grouping list列出time,region,department，结果集包括4个聚合层次。使用Rollup可以压缩数据。这对于对旧有分区较少更新的情况十分有用。
　　
　　When to Use ROLLUP
　　一般在任务中包含小计的情况可以使用Rollup扩展。
　　ROLLUP Syntax
　　Rollup出现在select查询的group by条款。他的形式是：
　　Select … Group by Rollup(grouping_column_reference_list)
　　Select channel_desc,
　　calendar_month_desc,
　　contry_id,
　　sum(amount_sold) SALES$
　　From sales,customers,times,channels
　　Where sales.time_id=times.time_id
　　 And sales.cust_id = customer.cust_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 country_id IN (‘UK’,’US’)
　　Group by rollup (channel_desc,calendar_month_desc,contry_id);
　　CHANNEL_DESC CALENDAR CO SALES$
　　-------------------- -------- -- --------------
　　Direct Sales 2000-09 UK 1,378,126
　　Direct Sales 2000-09 US 2,835,557
　　Direct Sales 2000-09 4,213,683
　　Direct Sales 2000-10 UK 1,388,051
　　Direct Sales 2000-10 US 2,908,706
　　Direct Sales 2000-10 4,296,757
　　Direct Sales 8,510,440
　　Internet 2000-09 UK 911,739
　　Internet 2000-09 US 1,732,240
　　Internet 2000-09 2,643,979
　　Internet 2000-10 UK 876,571
　　Internet 2000-10 US 1,893,753
　　Internet 2000-10 2,770,324
　　Internet 5,414,303
　　13,924,743
　　
　　This query returns the following sets of rows:这个查询返回如下的行：
　　1． Group by条款规定的标准聚合
　　2． 基于country_id层的小计
　　3． 基于calendar_month_desc和country_id的2层聚合
　　4． 总计
　　Partial Rollup 部分rollup
　　你也可以使用Rollup包含有限的几个小计。部分rollup的语法是
　　Group by expr1,Rollup(expr2,expr3)
　　这种情况下，group by 条款创建2+1层的小记。
　　层次为(expr1,expr2,expr3)(expr1,expr2)(expr1)
　　
　　select channel_desc,
　　calendar_month_desc,
　　country_id,
　　sum(amount_sold) sales$
　　from sales,customers,times,channels
　　Where sales.time_id=times.time_id
　　 And sales.cust_id = customer.cust_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 country_id IN (‘UK’,’US’)
　　Group by channel_desc,rollup(calendar_month_desc,country_id);
　　CHANNEL_DESC CALENDAR CO SALES$
　　-------------------- -------- -- --------------
　　Direct Sales 2000-09 UK 1,378,126
　　Direct Sales 2000-09 US 2,835,557
　　Direct Sales 2000-09 4,213,683
　　Direct Sales 2000-10 UK 1,388,051
　　Direct Sales 2000-10 US 2,908,706
　　Direct Sales 2000-10 4,296,757
　　Direct Sales 8,510,440
　　Internet 2000-09 UK 911,739
　　Internet 2000-09 US 1,732,240
　　Internet 2000-09 2,643,979
　　Internet 2000-10 UK 876,571
　　Internet 2000-10 US 1,893,753
　　Internet 2000-10 2,770,324
　　Internet 5,414,303
　　这个查询返回下面的行
　　1． Group by条款规定的标准聚合
　　2． 基于country_id层的小计
　　3． 基于calendar_month_desc和country_id的2层聚合
　　4． 不计算总计
　　参考文档编号：a96520.pdf

事关ROLLUP CUBE GROUPINGSETS （3）

　　CUBE Extension to GROUP BY
　　
　　
　　CUBE进行grouping列规定的grouping，创建所有可能的小计。在多维分析的术语中，CUBE生成CUBE数据规定维度的所有能计算的小记。
　　如果规定了CUBE(time,region,department),那么结果集将会包括相应rollup的所有值加上额外的合并。如果有n类在CUBE中规定，那么将会有2的n次方的小记返回。
　　
　　When to Use CUBE
　　需要交叉报表的地方可以用Cube。交叉报表的数据生成可以通过简单的带有Cube的select查询语句实现。像Rollup,Cube在总结性报表中非常有用。Cube相比较一维多层，在多维的情况下更有用。
　　CUBE Syntax
　　CUBE出现在select语句的group by条款中。它的形式是：
　　Select … group by CUBE (group_column_reference_list)
　　例：
　　select channel_desc,
　　calendar_month_desc,
　　country_id,
　　sum(amount_sold) sales$
　　from sales,customers,times,channels
　　Where sales.time_id=times.time_id
　　 And sales.cust_id = customer.cust_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 country_id IN (‘UK’,’US’)
　　Group by CUBE (channel_desc,calendar_month_desc,country_id);
　　CHANNEL_DESC CALENDAR CO SALES$
　　-------------------- -------- -- --------------
　　Direct Sales 2000-09 UK 1,378,126
　　Direct Sales 2000-09 US 2,835,557
　　Direct Sales 2000-09 4,213,683
　　Direct Sales 2000-10 UK 1,388,051
　　Direct Sales 2000-10 US 2,908,706
　　Direct Sales 2000-10 4,296,757
　　Direct Sales UK 2,766,177
　　Direct Sales US 5,744,263
　　Direct Sales 8,510,440
　　Internet 2000-09 UK 911,739
　　Internet 2000-09 US 1,732,240
　　Internet 2000-09 2,643,979
　　Internet 2000-10 UK 876,571
　　Internet 2000-10 US 1,893,753
　　Internet 2000-10 2,770,324
　　Internet UK 1,788,310
　　Internet US 3,625,993
　　Internet 5,414,303
　　2000-09 UK 2,289,865
　　2000-09 US 4,567,797
　　2000-09 6,857,662
　　2000-10 UK 2,264,622
　　2000-10 US 4,802,459
　　2000-10 7,067,081
　　UK 4,554,487
　　US 9,370,256
　　13,924,743
　　
　　Partial CUBE部分CUBE
　　部分CUBE类似部分ROLLUP，可以限制某些维度，还可以把一些列放在CUBE之外。这种情况下，所有可能的小节限制在CUBE列表之中。
　　部分CUBE 语法
　　GROUP BY expr1, CUBE(expr2, expr3)
　　计算4次小计:
　　n (expr1, expr2, expr3)
　　n (expr1, expr2)
　　n (expr1, expr3)
　　n (expr1)
　　Calculating Subtotals Without CUBE不使用CUBE计算小计
　　像ROLLUP一样，多个select语句使用UNION ALL连接可以提供跟CUBE相同的信息。但是，却需要多个select语句。对于n维CUBE，需要2的n次方个select查询。
　　在3维的例子中，这意味着需要把这些select语句都通过UNION ALL联接起来。
　　这么多的select语句将会使得处理不够高效，并且加长sql语句。
　　GROUPING Functions
　　ROLLUP和CUBE带来了两个问题。
　　第一， 你怎么让程序知道结果集哪一行代表小节，并且怎样知道对于一个给定的小节找到确切的聚合层次？
　　如果查询结果出现NULL还有CUBE ROLLUP产生的NULL，你如何来区分呢
　　GROUPING Function
　　Grouping 解决上面的问题。使用一个列作为参数。Grouping当rollup 和 cube生成的NULL值时返回1。就是说如果这一列是个小节，则grouping返回1；否则返回0
　　Grouping 的语法
　　Grouping出现在select条款中，形如
　　Select … [grouping(dimension_column) … ] …
　　Group by … {CUBE | ROLLUP | GROUPING SETS} (dimension_column)
　　
　　举例
　　SELECT
　　DECODE(GROUPING(channel_desc), 1, 'All Channels', hannel_desc)
　　AS Channel,
　　DECODE(GROUPING(country_id), 1, 'All Countries', country_id)
　　AS Country,
　　TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
　　FROM sales, customers, times, channels
　　WHERE sales.time_id=times.time_id AND
　　sales.cust_id=customers.cust_id AND
　　sales.channel_id= channels.channel_id AND
　　channels.channel_desc IN ('Direct Sales', 'Internet') AND
　　times.calendar_month_desc= '2000-09'
　　AND country_id IN ('UK', 'US')
　　GROUP BY CUBE(channel_desc, country_id)
　　
　　CHANNEL COUNTRY SALES$
　　-------------------- ------------- --------------
　　Direct Sales UK 1,378,126
　　Direct Sales US 2,835,557
　　Direct Sales All Countries 4,213,683
　　Internet UK 911,739
　　Internet US 1,732,240
　　Internet All Countries 2,643,979
　　All Channels UK 2,289,865
　　All Channels US 4,567,797
　　All Channels All Countries 6,857,662
　　
　　使用having子句来限制
　　HAVING
　　(GROUPING(channel_desc)=1 AND GROUPING(calendar_month_desc)= 1 AND
　　GROUPING(country_id)=1) OR
　　(GROUPING(channel_desc)=1 AND GROUPING(calendar_month_desc)= 1) OR
　　(GROUPING(country_id)=1 AND GROUPING(calendar_month_desc)= 1);
　　这样得到的全是聚合数据.
　　
　　GROUPING SETS Expression
　　通过group sets表达式，你可以选择性的设置你想要的grouping。
　　例如：
　　
　　SELECT channel_desc, calendar_month_desc, country_id,
　　TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
　　FROM sales, customers, times, channels
　　WHERE sales.time_id=times.time_id AND
　　sales.cust_id=customers.cust_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 country_id IN ('UK', 'US')
　　GROUP BY GROUPING SETS((channel_desc, calendar_month_desc, country_id),
　　(channel_desc, country_id), (calendar_month_desc, country_id));
　　这个语句计算3个grouping
　　n (channel_desc, calendar_month_desc, country_id)
　　n (channel_desc, country_id)
　　n (calendar_month_desc, country_id)
　　
　　CUBE( a , b , c) 等效于
　　Grouping sets ( (a,b,c), (a,b),(a,c),(b,c),(a),(b),(c),() ) 和 rollup(a,b,c)
　　Composite Columns 组合列
　　组合列示列的集合在计算grouping的时候可以看作一个单元。
　　
　　ROLLUP (year, (quarter, month), day)意思是
　　n (year, quarter, month, day),
　　n (year, quarter, month),
　　n (year)
THE END
参考文档编号 a96520.pdf
参考文档名称 Oracle9i Data Warehousing Guide 第18章
部分词语不做翻译，例如group grouping CUBE rollup等等。