db2 中的用户定义聚合函数
作者:佚名 时间:2005-08-30 16:33 出处:互连网 责编:小渔
Knut Stolze
信息集成部门, IBM 德国 2003 年 10 月
DB2 提供了几种内建的聚合 (或者字段)函数，但没有提供直接实现用户定义聚合运算的方式。本文提出了一种实现指定聚合运算的方法。 简介
DB2? Universal Database? 支持提供了几种内建的聚合函数 。这些内建函数包括 AVG、COUNT、MIN、MAX、SUM，还有其他一些。然而，当使用用户定义类型的时候，有时会遇到需要聚合计算的情况。目前还没有直接实现您自己的用户定义聚合函数的方法。本文提出了一种实现指定聚合运算的方法。该技术使用了内建的聚合函数 MAX 完成实际的聚合操作，并使用几个标量用户定义函数（UDF）的特性来满足聚合运算特殊的需要。我将使用复数的例子来解释和阐明这种技术。
您可以在数据库的表中管理复数。定义一个结构化的类型来封装复数，如 清单 1 所示。新的数据类型 Complex 在表 complexNumbers 中作为一个字段的类型使用。复数数据类型还提供了方法 add ，该方法允许两个复数相加，其结果是一个新的复数。构造函数
1
complex 以复数的实部和虚部作为输入参数，构造出一个可以存储到表中的新值。另外还
定义了其他方法，出于篇幅原因在这里省略掉了。清单中最后的 INSERT 语句使用三行数据填充表，每一行包含一个不同的复数。 清单 1. 定义和使用复数
CREATE TYPE Complex AS ( real DOUBLE, i DOUBLE ) INSTANTIABLE WITHOUT COMPARISONS
NOT FINAL MODE DB2SQL
WITH FUNCTION ACCESS@ ALTER TYPE Complex
ADD METHOD add(number Complex) RETURNS Complex
SPECIFIC complexAdd LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION SELF AS RESULT CONTAINS SQL@ CREATE METHOD add(number Complex) RETURNS Complex FOR complex
RETURN SELF..real(SELF..real + number..real).. i(SELF..i + number..i)@
CREATE FUNCTION complex(real DOUBLE, i DOUBLE) RETURNS Complex
SPECIFIC complexConstr DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURN Complex()..real(real)..i(i)@ CREATE TABLE complexNumbers (
id INTEGER NOT NULL PRIMARY KEY, number Complex )@ INSERT
INTO complexNumbers VALUES ( 1, complex(0, 0) ), ( 2, complex(20.4, 0) ), ( 3, complex(8, 3.5) )@
现在我们假设您需要计算字段 number 中所有复数的和。内建的 SUM 函数不能理解您的用户定义类型。因此，您不得不自己使用应用逻辑或者递归查询来计算总和。 清单 2 说明了这样一个递归查询的大概样子。该查询相当简单，并且不涉及任何其他条件 。 清单 2. 使用递归查询计算总和
2
WITH sumT(cnt, sum) AS
( VALUES (0, complex(0, 0) ) UNION ALL
SELECT id, sum..add(number) FROM complexNumbers, sumT WHERE id = cnt+1 ) SELECT sum..real, sum..i FROM sumT
WHERE cnt >= ALL ( SELECT cnt FROM sumT )@
1 2
------------------------ ------------------------ +2.84000000000000E+001 +3.50000000000000E+000 1 record(s) selected.
显然这样一个查询并不是我们所需要的。因此这里提出的用户定义聚合计算方法可以使您避免递归查询，并且可能会带来性能提升，而且也可以大大简化查询本身。 清单 3 给出的是使用下面描述的方法的查询，得到的查询结果与清单 2 中的结果相同。 清单 3. 计算总和
SELECT sum..real, sum..i FROM ( SELECT
GetAggrResult(MAX(BuildComplexSum(number))) FROM complexNumbers ) AS t(sum) 1 2