Oracle统计函数使用之ROLLUP,CUBE,GROUPING,GROUPING SETS,GROUPING_ID

 

Oracle统计函数使用之ROLLUP与CUBE
    Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行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_id()可以美化效果
    实例如下：
SQL> desc t_order_item;
Name       Type       Nullable Default Comments 
---------- ---------- -------- ------- -------- 
ORDER_ID   NUMBER(12) Y                         
PROD_ID    NUMBER(12) Y                         
PROD_VALUE NUMBER(12) Y                         
ORDER_DATE DATE       Y  

SQL> select order_id,prod_id,prod_value,to_char(order_date,'yyyymmdd') order_date from t_order_item ;

     ORDER_ID       PROD_ID    PROD_VALUE ORDER_DATE
------------- ------------- ------------- ----------
          101          2001            12 20110208
          100          2001            12 20110209
          100          2002            13 20110212
          102          2002            13 20110221
          103          2001            12 20110219
          104          2003            14 20110223

6 rows selected

SQL> select order_id,prod_id,count(*) cnt
     from t_order_item
    group by cube(order_id,prod_id);

     ORDER_ID  PROD_ID        CNT
------------- ------------- ----------
                                           6
                         2001          3
                         2002          2
                         2003          1
          100                           2
          100          2001          1
          100          2002          1
          101                            1
          101          2001          1
          102                            1
          102          2002          1
          103                            1
          103          2001          1
          104                            1
          104          2003          1

15 rows selected

SQL> select order_id,prod_id,count(*) cnt
     from t_order_item
    group by rollup(order_id,prod_id);

     ORDER_ID       PROD_ID        CNT
------------- ------------- ----------
          100          2001          1
          100          2002          1
          100                            2
          101          2001          1
          101                            1
          102          2002          1
          102                            1
          103          2001          1
          103                            1
          104          2003          1
          104                            1
                                            6

12 rows selected

    在使用rollup和cube函数时，可以结合grouping函数进行使用。
    grouping函数返回1时表示本条统计结果记录中该列未列入统计条件;返回值为1时表示该列被列入统计条件。
    
SQL> select case when grouping(order_id)=1 then '总计'
                else order_id||''
           end order_id,
           case when grouping(order_id)=1 and grouping(prod_id)=1 then ''
                when grouping(prod_id)=1 then '小计'
                else prod_id||''
           end prod_id,count(*) cnt
     from t_order_item
    group by rollup(order_id,prod_id);

ORDER_ID                                 PROD_ID                                         CNT
---------------------------------------- ---------------------------------------- ----------
100                                      2001                                              1
100                                      2002                                              1
100                                      小计                                               2
101                                      2001                                              1
101                                      小计                                               1
102                                      2002                                              1
102                                      小计                                               1
103                                      2001                                              1
103                                      小计                                               1
104                                      2003                                              1
104                                      小计                                               1
总计                                                                                           6

    此外，可以通过使用grouping sets((list),(list)...)函数实现有选择性的对某些列或者列组合进行单独统计，当需要总计值时，则加上(NULL)选项

SQL> select order_id,prod_id,count(*),grouping(order_id) order_grp,grouping(prod_id) prod_grp
     from t_order_item
    group by grouping sets((NULL),(order_id,prod_id),(order_id));

     ORDER_ID       PROD_ID   COUNT(*)  ORDER_GRP   PROD_GRP
------------- ------------- ---------- ---------- ----------
          100          2001          1          0          0
          100          2002          1          0          0
          100                            2          0          1
          101          2001          1          0          0
          101                            1          0          1
          102          2002          1          0          0
          102                            1          0          1
          103          2001          1          0          0
          103                            1          0          1
          104          2003          1          0          0
          104                            1          0          1
                                            6          1          1

12 rows selected

SQL>

    grouping_id()函数接受一列或多列统计条件，通过与having条件子句结合可以对统计记录进行过滤，将不包含小计或者总计的记录过滤掉，grouping_id()函数返回grouping()位向量的十进制值，GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来。
    下例给出了每个统计结果值的grouping()位向量值：
SQL> select order_id,prod_id,count(*) cnt,grouping(order_id) order_grp,grouping(prod_id) prod_grp,grouping_id(order_id,prod_id) grp_value
 from t_order_item 
group by rollup(order_id,prod_id);

     ORDER_ID       PROD_ID        CNT  ORDER_GRP   PROD_GRP  GRP_VALUE
------------- ------------- ---------- ---------- ---------- ----------
          100          2001          1          0          0          0
          100          2002          1          0          0          0
          100                            2          0          1          1
          101          2001          1          0          0          0
          101                            1          0          1          1
          102          2002          1          0          0          0
          102                            1          0          1          1
          103          2001          1          0          0          0
          103                            1          0          1          1
          104          2003          1          0          0          0
          104                            1          0          1          1
                                            6          1          1          3

12 rows selected

SQL>

    下例给出了不包含小计值记录的统计结果：

SQL> select order_id,prod_id,count(*) cnt,grouping(order_id) order_grp,grouping(prod_id) prod_grp,grouping_id(order_id,prod_id) grp_value
     from t_order_item
    group by rollup(order_id,prod_id)
    having grouping_id(order_id,prod_id) not in (1,2) ;

     ORDER_ID       PROD_ID        CNT  ORDER_GRP   PROD_GRP  GRP_VALUE
------------- ------------- ---------- ---------- ---------- ----------
          100          2001          1          0          0          0
          100          2002          1          0          0          0
          101          2001          1          0          0          0
          102          2002          1          0          0          0
          103          2001          1          0          0          0
          104          2003          1          0          0          0
                                             6          1          1          3

7 rows selected

SQL>