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
|