oracle CUBE ROLLUP GROUPING SETS实例讲解

现在的位置: 首页 > 综合 > 正文

oracle CUBE ROLLUP GROUPING SETS实例讲解

2012年10月10日 ⁄ 综合 ⁄ 共 14548字 ⁄ 字号小中大 ⁄ 评论关闭

聚合是数据仓库的基础。为了提高聚合的性能。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等等。

事关ROLLUP CUBE GROUPINGSETS （3）
CUBE Extension to GROUP BY

GROUPING SETS Expression
通过group sets表达式，你可以选择性的设置你想要的grouping。
例如：

ROLLUP (year, (quarter, month), day)意思是
n (year, quarter, month, day),
n (year, quarter, month),
n (year)
THE END

【上篇】水晶报表学习资料
【下篇】简单的购物车例子

作者: vrouw

该日志由 vrouw 于12年前发表在综合分类下，最后更新于 2012年10月10日.
转载请注明: oracle CUBE ROLLUP GROUPING SETS实例讲解 | 学步园 +复制链接

抱歉!评论已关闭.

学步园

oracle CUBE ROLLUP GROUPING SETS实例讲解

作者: vrouw

书签

最新文章New

本站推荐

返回首页