聚合分析相关的语句、函数往往是数据仓库项目中最常用的知识,而使用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个字段作为参数时,grouping和grouping_id的返回值相同。
它们的不同点是:
- grouping函数只能带1个字段作为参数,grouping_id没有限制;
- 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原创文章,转载请注明出处