使用group by rollup和group by cube后的辅助函数

本文主要介绍,报表在使用group by rollup和group by cube后的辅助函数。

CREATE TABLE TEST8
   "ID" NUMBER,     
   "ORDERID" NUMBER, 
   "PRODUCTID" NUMBER, 
   "PRICE" NUMBER(10,2), 
   "QUANTITY" NUMBER
insert into TEST8 (ID, ORDERID, PRODUCTID, PRICE, QUANTITY) values (1, 1, 1, 3, 10);
insert into TEST8 (ID, ORDERID, PRODUCTID, PRICE, QUANTITY) values (2, 1, 2, 4, 5);
insert into TEST8 (ID, ORDERID, PRODUCTID, PRICE, QUANTITY) values (3, 1, 3, 10, 2);
insert into TEST8 (ID, ORDERID, PRODUCTID, PRICE, QUANTITY) values (4, 2, 1, 3, 6);
insert into TEST8 (ID, ORDERID, PRODUCTID, PRICE, QUANTITY) values (5, 2, 2, 4, 6);

基础数据

1、GROUPING函数

使用GROUPING函数处理汇总结果(在使用group by rollup和group by cube后的结果集)中的空值。

select orderid,productid,count(orderid) ordercount from test8 group by cube(orderid,productid) order by orderid;

结果集按照orderid和productid进行汇总之后,出现了很多的空值,具体原因参考 哥前面的随笔 ,so,必须解决这个问题,因为null值对报表结果来说没什么用,我们大致都会用0,'空','NULL'之类,来表示空值,所以GROUPING函数就是来干这个的.看代码:

select GROUPING(orderid) orderid,productid,count(orderid) ordercount from test8 group by cube(orderid,productid) order by orderid;

对比上面的没有使用GROUPING函数的结果集我们发现

当orderid为null值的时候grouping(orderid)的值为1,反之为0

所以:根据这个特点我们可以推断出GROUPING函数的用法:

GROUPING(字段),如果字段值为null,GROUPING(字段)返回1,反之返回0。

通过GROUPING(字段名)的这个特点,在结果case when then else end函数,就可以将null值修改为任何字段名类型的值了,代码如下:

select case GROUPING(orderid) when 1 then 0 else orderid end orderid,productid,count(orderid) ordercount from test8 group by cube(orderid,productid) order by orderid;

null值全部改为了0

2、GROUPING  SETS函数

和 哥前面的随笔 一样,不知道它的功能没关系,先试一试,在根据结果集推出来他的功能,不多说,上代码:

i、现在需要求出每个订单下每个产品的订单数

(1)group by解决方法:

select orderid,productid,count(orderid) from test8 group by (orderid,productid) order by orderid 

(2)group by grouping sets解决方案

这边因为不知道他的功能,那么就直接上代码猜:

a、猜想一:grouping sets(orderid,productid)

select orderid,productid,count(orderid) from test8 group by grouping sets(orderid,productid) order by orderid 

根据结果集很容易的发现,group by grouping sets(orderid,productid)的结果集等于group by orderid 和group by productid的合集,

下面验证猜想：

select orderid,null productid,count(orderid) ordercount from test8 group by(orderid) 
union
select null orderid,productid,count(orderid) ordercount from test8 group by(productid) 

ok,我们的猜想是正确,但是grouping sets(orderid,productid)并不能解i的需求,于是继续猜

b、猜想二:group by grouping sets(orderid,productid)

select orderid,productid,count(orderid) ordercount from test8 group by grouping sets((orderid,productid)) order by orderid 

ok,猜想二符合i提出的需求

ii、总结grouping sets的功能

根据上面的猜想大致可以推出grouping sets的功能:grouping by是group by的集合

GROUP BY GROUPING SETS (A,B,C)  等价与  GROUP BY A  
                                        UNION ALL  
                                        GROUP BY B  
                                        UNION ALL  
                                        GROUP BY C  

注意:grouping sets的特殊用法,grouping sets内部的最小单位是单个字段,其次是一个多个字段的几个用(字段1,字段2,......)表示,但是不支持嵌套括号,也没有必要,因为大多数情况下的报表都是2维的。

GROUP BY GROUPING SETS ((A,B,C))  等价与  GROUP BY A,B,C  
GROUP BY GROUPING SETS (A,(B,C))  等价与  GROUP BY A  
                                          UNION ALL  
                                          GROUP BY B,C  

我们还可以混合使用，如下：

GROUP BY A                     等价于  GROUP BY A  
        ,B                                     ,B  
        ,GROUPING SETS ((B,C))                 ,C  
GROUP BY A                    等价于  GROUP BY A,B,C  
        ,B                            UNION ALL  
        ,GROUPING SETS (B,C)          GROUP BY A,B