全部博文（11）
2011年（11）
分类： Oracle
2011-05-03 23:33:54
oracle 11g 007 sql 第17章 Enhancements to the GROUP BY Clause
GROUPING函数可以接受一列，返回0或者1。
如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。
GROUPING只能在使用ROLLUP或CUBE的查询中使用。
当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
ROLLUP，是GROUP BY子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。
ROLLUP传递一列 例子：
SQL> create table t007_a( a number);
Table created
SQL> insert into t007_a values(100);
1 row inserted
SQL> insert into t007_a values(200);
1 row inserted
SQL> commit;
Commit complete
SQL> select a,sum(a),grouping(a) from t007_a group by rollup(a);
A SUM(A) GROUPING(A)
---------- ---------- -----------
100 100 0
200 200 0
300 1
CUBE，也是GROUP BY子句的一种扩展，可以返回每一个列组合的小计记录，同时在末尾加上总计记录。
CUBE传递一列 例子：
SQL> select a,sum(a),grouping(a) from t007_a group by cube(a);
A SUM(A) GROUPING(A)
---------- ---------- -----------
300 1
100 100 0
200 200 0
ROLLUP、CUBE多列的例子：
ROLLUP 多列：
SQL> create table t007_b(a number,b varchar(20),c varchar(30));
Table created
SQL> insert into t007_b values( 200,'10','30');
1 row inserted
SQL> insert into t007_b values( 300,'30','50');
1 row inserted
SQL> insert into t007_b values( 500,'50','70');
1 row inserted
SQL> commit;
Commit complete
以下有两个列子大家看看有什么区别：
SQL> select a,b,c,grouping(a),grouping(b),grouping(c) from t007_b
2 group by rollup(a,(b,c));
A B C GROUPING(A) GROUPING(B) GROUPING(C)
---------- ------------- -------------------- ----------- ----------- -----------
200 10 30 0 0 0
200 0 1 1
300 30 50 0 0 0
300 0 1 1
500 50 70 0 0 0
500 0 1 1
1 1 1
SQL> select a,b,c,grouping(a),grouping(b),grouping(c) from t007_b
2 group by rollup(a,b,c);
A B C GROUPING(A) GROUPING(B) GROUPING(C)
---------- ------------- -------------------- ----------- ----------- -----------
200 10 30 0 0 0
200 10 0 0 1
200 0 1 1
300 30 50 0 0 0
300 30 0 0 1
300 0 1 1
500 50 70 0 0 0
500 50 0 0 1
500 0 1 1
1 1 1
10 rows selected
以上的区别在rollup(a,(b,c))，rollup(a,b,c) 前一个例子是将a看成一个整体，b和c看成另一个整体，而第2个例子是将a、b、c分别看成一个整体。
0和1的意思我已经在前面讲到了，这里就不提了。
第2节GROUPING SETS
聚合是数据仓库的基础。为了提高聚合的性能。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可以并行进行：多进程同时运算所有的语句。这种功能使得聚合计算更加高效，
因此提高了数据库性能和可测性。
GROUPING SETS 简单例子：
SQL> select a,b,c,grouping(a),grouping(b),grouping(c) from t007_b group by
2 grouping sets(rollup((a,b),c));
A B C GROUPING(A) GROUPING(B) GROUPING(C)
---------- --------------- --------------- ----------- ----------- -----------
200 10 30 0 0 0
200 10 0 0 1
300 30 50 0 0 0
300 30 0 0 1
500 50 70 0 0 0
500 50 0 0 1
1 1 1
GROUPING SETS 复杂例子：
SQL> select a,b,c,grouping(a),grouping(b),grouping(c) from t007_b group by
2 grouping sets (rollup((a,b),c),cube((a,b),c));
A B C GROUPING(A) GROUPING(B) GROUPING(C)
---------- -------------------- ------------ ----------- ----------- -----------
200 10 30 0 0 0
300 30 50 0 0 0
500 50 70 0 0 0
200 10 30 0 0 0
300 30 50 0 0 0
500 50 70 0 0 0
200 10 0 0 1
300 30 0 0 1
500 50 0 0 1
200 10 0 0 1
300 30 0 0 1
500 50 0 0 1
1 1 1
1 1 1
50 1 1 0
70 1 1 0
30 1 1 0
17 rows selected
