ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ROLLUP和CUBE

ROLLUP和CUBE

原创 Linux操作系统 作者:kvsion 时间:2009-08-29 22:41:57 0 删除 编辑

Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。


SQL> create table t as select * from dba_indexes;

表已创建。

SQL> select index_type, status, count(*) from t group by index_type, status;

INDEX_TYPE                  STATUS     COUNT(*)
--------------------------- -------- ----------
LOB                         VALID            51
NORMAL                      N/A              25
NORMAL                      VALID           479
CLUSTER                     VALID            11

下面来看看ROLLUP和CUBE语句的执行结果。

SQL> select index_type, status, count(*) from t group by rollup(index_type, status);

INDEX_TYPE                  STATUS     COUNT(*)
--------------------------- -------- ----------
LOB                         VALID            51
LOB                                          51
NORMAL                      N/A              25
NORMAL                      VALID           479
NORMAL                                      504
CLUSTER                     VALID            11
CLUSTER                                      11
                                            566

已选择8行。

SQL> select index_type, status, count(*) from t group by cube(index_type, status);

INDEX_TYPE                  STATUS     COUNT(*)
--------------------------- -------- ----------
                                            566
                            N/A              25
                            VALID           541
LOB                                          51
LOB                         VALID            51
NORMAL                                      504
NORMAL                      N/A              25
NORMAL                      VALID           479
CLUSTER                                      11
CLUSTER                     VALID            11

已选择10行。

查询结果不是很一目了然,下面通过Oracle提供的函数GROUPING来整理一下查询结果。

SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)
  2  from t group by rollup(index_type, status) order by 1, 2;

     G_IND       G_ST INDEX_TYPE                  STATUS     COUNT(*)
---------- ---------- --------------------------- -------- ----------
         0          0 LOB                         VALID            51
         0          0 NORMAL                      N/A              25
         0          0 NORMAL                      VALID           479
         0          0 CLUSTER                     VALID            11
         0          1 LOB                                          51
         0          1 NORMAL                                      504
         0          1 CLUSTER                                      11
         1          1                                             566

已选择8行。

这个查询结果就直观多了,和不带ROLLUP语句的GROUP BY相比,ROLLUP增加了对INDEX_TYPE的GROUP BY统计和对所有记录的GROUP BY统计。

也就是说,如果是ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。

下面看看CUBE语句。

SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)
  2  from t group by cube(index_type, status) order by 1, 2;

     G_IND       G_ST INDEX_TYPE                  STATUS     COUNT(*)
---------- ---------- --------------------------- -------- ----------
         0          0 LOB                         VALID            51
         0          0 NORMAL                      N/A              25
         0          0 NORMAL                      VALID           479
         0          0 CLUSTER                     VALID            11
         0          1 LOB                                          51
         0          1 NORMAL                                      504
         0          1 CLUSTER                                      11
         1          0                             N/A              25
         1          0                             VALID           541
         1          1                                             566

已选择10行。

ROLLUP相比,CUBE又增加了对STATUS列的GROUP BY统计。

如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。

除了使用GROUPING函数,还可以使用GROUPING_ID来标识GROUP BY结果。

SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*)
  2  from t group by rollup(index_type, status) order by 1;

     G_IND INDEX_TYPE                  STATUS     COUNT(*)
---------- --------------------------- -------- ----------
         0 LOB                         VALID            51
         0 NORMAL                      N/A              25
         0 NORMAL                      VALID           479
         0 CLUSTER                     VALID            11
         1 LOB                                          51
         1 NORMAL                                      504
         1 CLUSTER                                      11
         3                                             566

已选择8行。

SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*)
  2  from t group by cube(index_type, status) order by 1;

     G_IND INDEX_TYPE                  STATUS     COUNT(*)
---------- --------------------------- -------- ----------
         0 LOB                         VALID            51
         0 NORMAL                      N/A              25
         0 NORMAL                      VALID           479
         0 CLUSTER                     VALID            11
         1 LOB                                          51
         1 NORMAL                                      504
         1 CLUSTER                                      11
         2                             N/A              25
         2                             VALID           541
         3                                             566

已选择10行。

转自:http://yangtingkun.itpub.net/post/468/22547

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

请登录后发表评论 登录
全部评论

注册时间:2008-09-20

  • 博文量
    17
  • 访问量
    23037