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

最新推荐文章于 2023-09-27 11:19:42 发布

clt3617

最新推荐文章于 2023-09-27 11:19:42 发布

阅读量271

点赞数

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(*)
------- ------------------------------ ------------------- ----------
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(*)
------- ------------------------------ ------------------- ----------
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函数的意思也清楚了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1055793/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1055793/