group by中rollup的以及grouping含義解析

rollup是對group by分組子句的擴充和延伸，最終的結果就是求小計和總計，其實是從多角度來分析和統計資料，是對dw業務的支援。[@more@]

下面看幾個rollup使用的例子就清楚了：

SQL> select status,owner,object_type,count(*) from dba_objects
2 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
3 group by rollup (status,owner,object_type)
4 order by status,owner,object_type
5 ;

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
INVALID PUBLIC SYNONYM 19
INVALID PUBLIC 19
INVALID TEST VIEW 1
INVALID TEST 1
INVALID TEST1 PROCEDURE 1
INVALID TEST1 1
INVALID 21
VALID A TABLE 1
VALID A 1
VALID OUTLN INDEX 3
VALID OUTLN LOB 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID OUTLN PROCEDURE 1
VALID OUTLN TABLE 3
VALID OUTLN 8
VALID PUBLIC SYNONYM 2491
VALID PUBLIC 2491
VALID TEST TABLE 3
VALID TEST 3
VALID TSMSYS INDEX 1
VALID TSMSYS LOB 1
VALID TSMSYS TABLE 1
VALID TSMSYS 3

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID 2506
2527

24 rows selected.

理解好下面幾句話其實就理解rollup了，上面查詢中status,owner,object_type3個欄位分組之後的小計其實是按照status,owner分組的結果；status,owner2個欄位分組之後的小計是按照status單獨分組的結果；最後總計就是不分組統計的結果或者是按照status分組之後的結果的和，明白了這個道理，我們可以把上面的sql等價寫成下面的形式：

SQL> select * from (
2 select status,owner,object_type,count(*) from dba_objects
3 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
4 group by status,owner,object_type
5 union all
6 select status,owner,null,count(*) from dba_objects
7 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
8 group by status,owner
9 union all
10 select status,null,null,count(*) from dba_objects
11 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
12 group by status
13 union all
14 select null,null,null,count(*) from dba_objects
15 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
16 )
17 order by status,owner,object_type
18 ;

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
INVALID PUBLIC SYNONYM 19
INVALID PUBLIC 19
INVALID TEST VIEW 1
INVALID TEST 1
INVALID TEST1 PROCEDURE 1
INVALID TEST1 1
INVALID 21
VALID A TABLE 1
VALID A 1
VALID OUTLN INDEX 3
VALID OUTLN LOB 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID OUTLN PROCEDURE 1
VALID OUTLN TABLE 3
VALID OUTLN 8
VALID PUBLIC SYNONYM 2491
VALID PUBLIC 2491
VALID TEST TABLE 3
VALID TEST 3
VALID TSMSYS INDEX 1
VALID TSMSYS LOB 1
VALID TSMSYS TABLE 1
VALID TSMSYS 3

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID 2506
2527

24 rows selected.

rollup還可以有選擇性的計算小計：

SQL> select status,owner,object_type,count(*) from dba_objects
2 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
3 group by status,rollup(owner,object_type)
4 order by status,owner,object_type
5 ;

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
INVALID PUBLIC SYNONYM 19
INVALID PUBLIC 19
INVALID TEST VIEW 1
INVALID TEST 1
INVALID TEST1 PROCEDURE 1
INVALID TEST1 1
INVALID 21
VALID A TABLE 1
VALID A 1
VALID OUTLN INDEX 3
VALID OUTLN LOB 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID OUTLN PROCEDURE 1
VALID OUTLN TABLE 3
VALID OUTLN 8
VALID PUBLIC SYNONYM 2491
VALID PUBLIC 2491
VALID TEST TABLE 3
VALID TEST 3
VALID TSMSYS INDEX 1
VALID TSMSYS LOB 1
VALID TSMSYS TABLE 1
VALID TSMSYS 3

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID 2506

23 rows selected.

SQL>

如果在rollup的時侯不包含status欄位，那麼表示的意思就是不對status單獨分組之後的結果求合計，status單獨分組之後的合計其實就是總計，所以上面查詢的結果少了總計。

SQL> select status,owner,object_type,count(*) from dba_objects
2 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
3 group by status,owner,rollup(object_type)
4 order by status,owner,object_type
5 ;

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
INVALID PUBLIC SYNONYM 19
INVALID PUBLIC 19
INVALID TEST VIEW 1
INVALID TEST 1
INVALID TEST1 PROCEDURE 1
INVALID TEST1 1
VALID A TABLE 1
VALID A 1
VALID OUTLN INDEX 3
VALID OUTLN LOB 1
VALID OUTLN PROCEDURE 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID OUTLN TABLE 3
VALID OUTLN 8
VALID PUBLIC SYNONYM 2491
VALID PUBLIC 2491
VALID TEST TABLE 3
VALID TEST 3
VALID TSMSYS INDEX 1
VALID TSMSYS LOB 1
VALID TSMSYS TABLE 1
VALID TSMSYS 3

21 rows selected.

SQL>
上面查詢rollup裡只包含了object_type欄位，那麼表示不對status,owner分組之後的結果求小計，status,owner2個欄位分組的小計是按照status分組的結果，沒有status分組的結果也就不存在總計，所以少了3行記錄。

SQL> select status,owner,object_type,count(*) from dba_objects
2 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
3 group by status,owner,object_type
4 order by status,owner,object_type
5 ;

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
INVALID PUBLIC SYNONYM 19
INVALID TEST VIEW 1
INVALID TEST1 PROCEDURE 1
VALID A TABLE 1
VALID OUTLN INDEX 3
VALID OUTLN LOB 1
VALID OUTLN PROCEDURE 1
VALID OUTLN TABLE 3
VALID PUBLIC SYNONYM 2491
VALID TEST TABLE 3
VALID TSMSYS INDEX 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID TSMSYS LOB 1
VALID TSMSYS TABLE 1

13 rows selected.

SQL>

最後沒有rollup的時侯就是正常的分組了，就很好理解了。

--====================================

明白了rollup，我們再來看看grouping函式，grouping函式是為了清楚的區分或者是給我們標識小計和總計的：

SQL> select grouping(status),status,owner,object_type,count(*) from dba_objects
2 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
3 group by rollup(status,owner,object_type)
4 order by status,owner,object_type
5 ;

GROUPING(STATUS) STATUS OWNER OBJECT_TYPE COUNT(*)
---------------- ------- ------------------------------ ------------------- ----------
0 INVALID PUBLIC SYNONYM 19
0 INVALID PUBLIC 19
0 INVALID TEST VIEW 1
0 INVALID TEST 1
0 INVALID TEST1 PROCEDURE 1
0 INVALID TEST1 1
0 INVALID 21
0 VALID A TABLE 1
0 VALID A 1
0 VALID OUTLN INDEX 3
0 VALID OUTLN LOB 1

GROUPING(STATUS) STATUS OWNER OBJECT_TYPE COUNT(*)
---------------- ------- ------------------------------ ------------------- ----------
0 VALID OUTLN PROCEDURE 1
0 VALID OUTLN TABLE 3
0 VALID OUTLN 8
0 VALID PUBLIC SYNONYM 2491
0 VALID PUBLIC 2491
0 VALID TEST TABLE 3
0 VALID TEST 3
0 VALID TSMSYS INDEX 1
0 VALID TSMSYS LOB 1
0 VALID TSMSYS TABLE 1
0 VALID TSMSYS 3

GROUPING(STATUS) STATUS OWNER OBJECT_TYPE COUNT(*)
---------------- ------- ------------------------------ ------------------- ----------
0 VALID 2506
1 2527

24 rows selected.

SQL>

上面查詢裡grouping(status)表示的意思是對按照status分組之後的小計標識出來(grouping(status)對應結果是1),沒有標識出來的顯示為0。

SQL> select grouping(owner),status,owner,object_type,count(*) from dba_objects
2 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
3 group by rollup(status,owner,object_type)
4 order by status,owner,object_type
5 ;

GROUPING(OWNER) STATUS OWNER OBJECT_TYPE COUNT(*)
--------------- ------- ------------------------------ ------------------- ----------
0 INVALID PUBLIC SYNONYM 19
0 INVALID PUBLIC 19
0 INVALID TEST VIEW 1
0 INVALID TEST 1
0 INVALID TEST1 PROCEDURE 1
0 INVALID TEST1 1
1 INVALID 21
0 VALID A TABLE 1
0 VALID A 1
0 VALID OUTLN INDEX 3
0 VALID OUTLN LOB 1

GROUPING(OWNER) STATUS OWNER OBJECT_TYPE COUNT(*)
--------------- ------- ------------------------------ ------------------- ----------
0 VALID OUTLN PROCEDURE 1
0 VALID OUTLN TABLE 3
0 VALID OUTLN 8
0 VALID PUBLIC SYNONYM 2491
0 VALID PUBLIC 2491
0 VALID TEST TABLE 3
0 VALID TEST 3
0 VALID TSMSYS INDEX 1
0 VALID TSMSYS LOB 1
0 VALID TSMSYS TABLE 1
0 VALID TSMSYS 3

GROUPING(OWNER) STATUS OWNER OBJECT_TYPE COUNT(*)
--------------- ------- ------------------------------ ------------------- ----------
1 VALID 2506
1 2527

24 rows selected.

SQL>

上面grouping(owner)表示的意思是把status以及status，owner分組之後的小計標識出來，status分組之後的小計是總計，status，owner分組之後的小計是按照status分組的結果。

SQL> select grouping(object_type),status,owner,object_type,count(*) from dba_objects
2 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
3 group by rollup(status,owner,object_type)
4 order by status,owner,object_type
5 ;

GROUPING(OBJECT_TYPE) STATUS OWNER OBJECT_TYPE COUNT(*)
--------------------- ------- ------------------------------ ------------------- ----------
0 INVALID PUBLIC SYNONYM 19
1 INVALID PUBLIC 19
0 INVALID TEST VIEW 1
1 INVALID TEST 1
0 INVALID TEST1 PROCEDURE 1
1 INVALID TEST1 1
1 INVALID 21
0 VALID A TABLE 1
1 VALID A 1
0 VALID OUTLN INDEX 3
0 VALID OUTLN LOB 1

GROUPING(OBJECT_TYPE) STATUS OWNER OBJECT_TYPE COUNT(*)
--------------------- ------- ------------------------------ ------------------- ----------
0 VALID OUTLN PROCEDURE 1
0 VALID OUTLN TABLE 3
1 VALID OUTLN 8
0 VALID PUBLIC SYNONYM 2491
1 VALID PUBLIC 2491
0 VALID TEST TABLE 3
1 VALID TEST 3
0 VALID TSMSYS INDEX 1
0 VALID TSMSYS LOB 1
0 VALID TSMSYS TABLE 1
1 VALID TSMSYS 3

GROUPING(OBJECT_TYPE) STATUS OWNER OBJECT_TYPE COUNT(*)
--------------------- ------- ------------------------------ ------------------- ----------
1 VALID 2506
1 2527

24 rows selected.

SQL>

上面查詢grouping(object_type)表示的意思是把status,owner,object_type3個欄位分組之後的小計表示出來，3個欄位分組的小計是按照status,owner分組的結果，同時要把status,owner分組的小計標識出來，status,owner分組的小計是按照status分組，同時把status分組的小計標識出來，最後還要把status分組的小計標識出來，status分組的小計就是總計了。

至此grouping函式的意思也清楚了。