group by rollup 和cube

• RULLUP (a,b,c...n)  

group by (a,b,c)->(a,b)->(a)->全表group by   共分组n+1次

• CUBE(a,b,c...n)

group by(a,b,c)->(a,b)->(a,c)->(a)->(b,c)-(b)-(c)->全表group by  共分组2n次方

• grouping   grouping_id()可以美化效果

例：

select grouping(index_type) g_idx,grouping(status) g_sts,index_type,status,count(*) from grouptest group by cube (index_type,status);

  G_IDX      G_STS INDEX_TYPE                  STATUS     COUNT(*)
---------- ---------- --------------------------- -------- ----------
         1          1                                            2438
         1          0                             N/A              64
         1          0                             VALID          2374
         0          1 LOB                                         565
         0          0 LOB                         N/A               1
         0          0 LOB                         VALID           564
         0          1 BITMAP                                       15
         0          0 BITMAP                      N/A               7
         0          0 BITMAP                      VALID             8
         0          1 DOMAIN                                        1
         0          0 DOMAIN                      VALID             1
         0          1 NORMAL                                     1715
         0          0 NORMAL                      N/A              56
         0          0 NORMAL                      VALID          1659
         0          1 CLUSTER                                      10
         0          0 CLUSTER                     VALID            10
         0          1 IOT - TOP                                   114
         0          0 IOT - TOP                   VALID           114
         0          1 FUNCTION-BASED DOMAIN                         1
         0          0 FUNCTION-BASED DOMAIN       VALID             1
         0          1 FUNCTION-BASED NORMAL                        17
         0          0 FUNCTION-BASED NORMAL       VALID            17

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

转载于:http://blog.itpub.net/23669478/viewspace-666188/