最近在制作报表时,遇到一个稍稍复杂一点的报表需求,即用户希望报表能对统计的数据进行分类小计,如下:
而我之前的统计都是使用Group By来对数据进行简单的汇总,如下:
所以对这样的新的需求觉得一时无从下手,询问了同事后才知道,可以使用Rollup统计函数(也称分析函数)来解决这个问题,查询资料后一试,发现果然可以,在此简单介绍一下。
Rollup运算符(在生成包含小计和合计的报表时很有用)是Select和Group By子句的一部分,它生成的结果集中,即包括每个项目的明细行也包括每个组的汇总行,汇总行显示了该组的聚合合计。Group By子句可用于生成只包含各组的聚合而不包含其明细行的结果。使用方法为:Select A, B, sum(fee) From T Group By A, B with Rollup。
下面介绍一下Rollup的统计规则,比如如果要对(A,B,C)三个字段进行Rollup汇总统计的话,那么它会先对(A,B,C)三个字段进行汇总统计,然后对(A,B)字段汇总统计并空出字段C对应的位置,然后对(A)字段进行汇总统计并空出B、C字段对应的位置,而空出的位置我们可以使用Grouping函数来进行判断,如果为“1”,则说明该空值为Rollup制造,否则为“0”。
通过上面的描述,可以看出,当将Rollup的字段从上往下列出时,它会每次将最后一个字段去掉,然后取前面的字段进行汇总统计,所以称为上卷统计,例:
select case when grouping(spName)=1 then '合计' else spName end,
case when grouping(opName)=1 and grouping(spName)=0 then '小计' else opName end,
sum(sumfare)
from TB_WAP_STAT_FARE
group by spName, opName with rollup
说到了Rollup就不得不说下Cube,因为Cube与Rollup功能相似但更为复杂,他们的使用方法都是一样的,但是生成结果集却有所不同。再以上面的对(A,B,C)三个字段进行Cube汇总统计为例,那么它会先对(A,B,C)三个字段进行汇总统计,然后分别对(A,B)、(A,C)、(B,C)字段进行汇通统计并空出未进行汇总统计的字段对应的值,然后分别对(A)、(B)、(C)进行汇总统计并空出未进行汇总统计的字段对应的值。
也就是说,Cube是将进行统计的所有字段的所有可能的组合进行汇总统计,这就是它区别于Rollup的地方。