Tag Archives: aggregation

About Aggregation in Oracle and DB2

聚合分析相关的语句、函数往往是数据仓库项目中最常用的知识,而使用Oracle来进行OLTP项目的人往往不需要了解这些。Oracle 10.2把这些相关的内容放在Data Warehousing Guide文档中,专门使用了一个章节进行了详细的介绍;DB2 9.5则将其放在数据库基本知识SQL中,分散在Functions和Queries两个章节。相比之下,本人更喜欢Oracle的文档结构。

总体介绍

除了grouping_id和group_id这两个函数外,Oracle有的特性,在DB2中都有,而且作用和用法都是一样的:

Aggregation Feature Oracle DB2
cube extension
rollup extension
grouping() function
grouping_id() function  
group_id() function  
grouping sets expression
with clause

关于以上特性,我有一个常用的查询:

select decode(grouping(a.subscription_id),1,’All’,subscription_id) subscription_id,
            decode(grouping(a.service_num),1,’All’,service_num) service_num,
            decode(grouping(a.subject_id),1,’All Subject’,a.subject_id) subject_id,
            decode(grouping(b.subject_name),1,’All Subject’,b.subject_name) subject_name,
            sum(a.total_amount)/1000 pre_fee,
            sum(a.disc_total_amount)/1000 disc_fee,
            sum(a.total_amount-a.disc_total_amount)/1000 bill_fee
from zjuinv1o.acc_charge_201001 a
            left join zjuinv1o.acc_subject b
            on a.subject_id=b.subject_id
where a.account_id=109600235
group by rollup((a.subscription_id,a.service_num),
            (a.subject_id,b.subject_name));

该查询的返回结果如下:

图中红色框中的记录就是聚合分析的效果。

聚合分析特性中,grouping_id和group_id这两个函数,DB2不支持,那么这两个函数到底是干什么用的呢?

grouping_id函数

grouping_id函数,我的理解,它是从grouping函数延伸而来的,它们的相同点是:

  1. 当只带1个字段作为参数时,grouping和grouping_id的返回值相同。

它们的不同点是:

  1. grouping函数只能带1个字段作为参数,grouping_id没有限制;
  2. grouping函数只能返回1或0,而grouping_id返回值有更多可能。

Oracle官方文档中有个表格,使用BitVector的方式说明了grouping_id值的生成:

Table 20-2 GROUPING_ID Example for CUBE(a, b)

Aggregation Level Bit Vector GROUPING_ID
a, b 0 0 0
a 0 1 1
b 1 0 2
Grand Total 1 1 3

我要补充的是,bit vector是看grouping_id的参数而不是cube、rollup或grouping sets的参数,也就是说grouping_id的值与其后的参数个数、顺序有关系,对于cube(a,b),相同的记录中,grouping_id(a,b) 和grouping_id(b,a)、grouping_id(a) 、grouping_id(b)的值都不尽相同。

group_id函数

group_id函数,它的作用其实很简单,就是用来判断group是否重复,用来剔除重复的记录。如果是重复记录,它的返回值会大于0,第一次重复返回1,第二次重复返回2,etc.一般情况下,如果查询中使用了grouping sets和rollup或cube,有可能会出现重复记录,我一般这么用:

select …
where …
having group_id() = 0
group by grouping sets(…)


Data Warehouse For Ever原创文章,转载请注明出处