DB2 9.0 GROUPING SETS & ROLLUP & CUBE 比较

 

DB2 9.0 GROUPING SETS & ROLLUP & CUBE 比较

GROUPING SETS

-------------------------------------------------------------------------

GROUP BY GROUPING SETS((),())--(NESTED FLAG,GROUP BY UNION ALL) 

GROUPING SETS((A,B,C))     

                   =GROUP BY A,B,C

GROUPING SETS(A,(B,C))     

                   =GROUP BY A UNION ALL GROUP BY B,C


GROUPING SETS(A,B,C)       

                   =GROUP BY A UNION ALL GROUP BY B UNION ALL GROUP BY C
-------------------------------------------------------------------------

GROUP BY GROUPING SETS(),GROUPING SETS()  = GROUP BY (),()
GROUPING SETS(A),GROUPING SETS(B),GROUPING SETS(C)

                   =GROUP BY A,B,C

GROUPING SETS(A),GROUPING SETS((B,C))                          

                   =GROUP BY A,B,C

GROUPING SETS(A),GROUPING SETS(B,C)                             

                   =GROUP BY A,B UNION ALL GROUP BY A,C
-------------------------------------------------------------------------
GROUP BY (),GROUPING SETS() = GROUP BY (),()

GROUP BY A,B,GROUPING SETS((B,C))  

                   = GROUP BY A,B,B,C
                   = GROUP BY A,B,C

GROUP BY A,B,GROUPING SETS(B,C)    

                   = GROUP BY A,B,B UNION ALL GROUP BY A,B,C 
                   = GROUP BY A,B   UNION ALL GROUP BY A,B,C

-------------------------------------------------------------------------

ROLLUP
-------------------------------------------------------------------------

GROUP BY ROLLUP(A,B,C)                                       

                  =GROUPING SETS((A,B,C),(A,B),(A),())

 

GROUP BY ROLLUP(A,(B,C))                 

                  =GROUPING SETS((A,B,C),(A),())

 

GROUP BY ROLLUP(A),ROLLUP(B,C)   

                  =GROUPING SETS(A,())*GROUPING SETS((B,C),(B),())
                  =GROUPING SETS((A,B,C),(A,B),(A),(B,C),(B),())  

-------------------------------------------------------------------------

CUBE
-------------------------------------------------------------------------
GROUP BY CUBE(A,B,C)                       

                =GROUPING SETS((A,B,C),(A,B),(A,C),(A),(B,C),(B),(C),())


GROUP BY CUBE(A,(B,C))                     

                =GROUPING SETS((A,B,C),(A),(B,C),())


GROUP BY CUBE(A,B),CUBE(B,C)         

                =GROUPING SETS((A,B),(A),(B),())*GROUPING SETS((B,C),(B),(C),())
                =GROUPING SETS(

                                                                (A,B,B,C),(A,B,B),(A,B,C),(A,B),
                                                                (A,B,C),(A,B),(A,C),(A),
                                                                (B,B,C),(B,B),(B,C),(B),
                                                                (B,C),(B),(C),())                       

                 =GROUPING SETS(
                                                                (A,B,C),(A,B),(A,B,C),(A,B),
                                                                (A,B,C),(A,B),(A,C),(A),
                                                                (B,C),(B),(B,C),(B),
                                                                (B,C),(B),(C),())