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>