ROLLUP和CUBE

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行。