group by中cube含义解析

最新推荐文章于 2021-05-17 19:05:16 发布

clt3617

最新推荐文章于 2021-05-17 19:05:16 发布

阅读量424

点赞数

文章标签：数据库

理解了rollup，那么cube相对就好理解了，cube是对rollup的深度使用。cube是group by中几个字段的组合分组，其实要表示的典型的意思是dw中转取的概念，每一个group by中要分组的字段本身就是dw中的一个维度，而cube的出现把dw的含义体现的淋漓尽致.

rollup和grouping总结如下：

http://warehouse.itpub.net/post/777/524442

[@more@]

SQL> select status,owner,object_type,count(*) from dba_objects
2 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
3 group by cube(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 PROCEDURE 1
INVALID SYNONYM 19
INVALID VIEW 1
INVALID 21
VALID A TABLE 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID A 1
VALID OUTLN INDEX 3
VALID OUTLN LOB 1
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

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
VALID TSMSYS LOB 1
VALID TSMSYS TABLE 1
VALID TSMSYS 3
VALID INDEX 4
VALID LOB 2
VALID PROCEDURE 1
VALID SYNONYM 2491
VALID TABLE 8
VALID 2506
A TABLE 1
A 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
OUTLN INDEX 3
OUTLN LOB 1
OUTLN PROCEDURE 1
OUTLN TABLE 3
OUTLN 8
PUBLIC SYNONYM 2510
PUBLIC 2510
TEST TABLE 3
TEST VIEW 1
TEST 4
TEST1 PROCEDURE 1

STATUS OWNER OBJECT_TYPE COUNT(*)
------- ------------------------------ ------------------- ----------
TEST1 1
TSMSYS INDEX 1
TSMSYS LOB 1
TSMSYS TABLE 1
TSMSYS 3
INDEX 4
LOB 2
PROCEDURE 2
SYNONYM 2510
TABLE 8
VIEW 1

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

56 rows selected.

SQL>

上面查询是分组字段status,owner,object_type的组合分组之后的结果，status,owner,object_type这3个字段的组合结果是：（status,owner,object_type），（status,owner），（status），（status,object_type），（owner,object_type），（object_type),另外还要注意的是每次分组之后都有小计（rollup），理解了这个意思，那么上面查询的等价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 rollup(status,owner,object_type)
5 union
6 select status,null,object_type,count(*) from dba_objects
7 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
8 group by rollup(status,object_type)
9 union
10 select null,owner,object_type,count(*) from dba_objects
11 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
12 group by rollup(owner,object_type)
13 union
14 select null,null,object_type,count(*) from dba_objects
15 where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP','WMSYS')
16 group by rollup(object_type)
17 )
18 order by status,owner,object_type
19 ;