ROLLUP is an extension to the group_by_clause that groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions for each row, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values. For example, given three expressions in the ROLLUP clause of the group_by_clause, the operation results in n+1 = 3+1 = 4 groupings. Rows based on the values of the first ’n’ expressions are called regular rows, and the others are called superaggregate rows.
对于扩展统计查询ROLLUP的测试: 1.先执行一个普通的GROUP查询: SQL> select col1,col2,count(*) 2 from t_percent 3 group by col1, col2; COL1 COL2 COUNT(*) ---------- ---------- ---------- A 11 3 A 12 2 A 13 5 B 11 2 B 12 3 B 13 4 B 14 1 7 rows selected.2.加上ROLLUP关键字,从而能使用函数GROUPING(),该函数获得统计分组的层次 SQL> select grouping(col1),grouping(col2),col1,col2,count(*) 2 from t_percent 3 group by rollup(col1,col2); GROUPING(COL1) GROUPING(COL2) COL1 COL2 COUNT(*) -------------- -------------- ---------- ---------- ---------- 0 0 A 11 3 0 0 A 12 2 0 0 A 13 5 0 1 A 10 0 0 B 11 2 0 0 B 12 3 0 0 B 13 4 0 0 B 14 1 0 1 B 10 1 1 20 10 rows selected. 可以看到,在STEP1查询结构的基础上,新增加了3个输出结果,分别是COL1=A AND COL2 IS ALL/COL1=B AND COL2 IS ALL/COL1 IS ALL AND COL2 IS ALL.这三个结果是在STEP1结果上的扩展统计.3.通过使用GROUPING()并结合DECODE()函数,我们就能获得更容易阅读的统计结果: SQL> select decode(grouping(col1),1,'all col1', col1), 2 decode(grouping(col2),1,'all col2', col2),count(*) 3 from t_percent 4 group by rollup(col1,col2); DECODE(GRO DECODE(GRO COUNT(*) ---------- ---------- ---------- A 11 3 A 12 2 A 13 5 A all col2 10 B 11 2 B 12 3 B 13 4 B 14 1 B all col2 10 all col1 all col2 2010 rows selected.
selected rows based on the values of the first n, n-1, n-2, ... 0
expressions for each row, and returns a single row of summary for
each group. You can use the ROLLUP operation to produce subtotal values.
For example, given three expressions in the ROLLUP clause of the
group_by_clause, the operation results in n+1 = 3+1 = 4 groupings.
Rows based on the values of the first ’n’ expressions are called
regular rows, and the others are called superaggregate rows.
1.先执行一个普通的GROUP查询:
SQL> select col1,col2,count(*)
2 from t_percent
3 group by col1, col2;
COL1 COL2 COUNT(*)
---------- ---------- ----------
A 11 3
A 12 2
A 13 5
B 11 2
B 12 3
B 13 4
B 14 1
7 rows selected.2.加上ROLLUP关键字,从而能使用函数GROUPING(),该函数获得统计分组的层次
SQL> select grouping(col1),grouping(col2),col1,col2,count(*)
2 from t_percent
3 group by rollup(col1,col2);
GROUPING(COL1) GROUPING(COL2) COL1 COL2 COUNT(*)
-------------- -------------- ---------- ---------- ----------
0 0 A 11 3
0 0 A 12 2
0 0 A 13 5
0 1 A 10
0 0 B 11 2
0 0 B 12 3
0 0 B 13 4
0 0 B 14 1
0 1 B 10
1 1 20
10 rows selected.
可以看到,在STEP1查询结构的基础上,新增加了3个输出结果,分别是COL1=A AND COL2 IS ALL/COL1=B AND COL2 IS ALL/COL1 IS ALL AND COL2 IS ALL.这三个结果是在STEP1结果上的扩展统计.3.通过使用GROUPING()并结合DECODE()函数,我们就能获得更容易阅读的统计结果:
SQL> select decode(grouping(col1),1,'all col1', col1),
2 decode(grouping(col2),1,'all col2', col2),count(*)
3 from t_percent
4 group by rollup(col1,col2);
DECODE(GRO DECODE(GRO COUNT(*)
---------- ---------- ----------
A 11 3
A 12 2
A 13 5
A all col2 10
B 11 2
B 12 3
B 13 4
B 14 1
B all col2 10
all col1 all col2 2010 rows selected.