信息集成部门, IBM 德国
2003 年 10 月
DB2 提供了几种内建的聚合 (或者字段)函数，但没有提供直接实现用户定义聚合运算的方式。本文提出了一种实现指定聚合运算的方法。
简介
DB2® Universal Database™ 支持提供了几种内建的聚合函数 1。这些内建函数包括 AVG、COUNT、MIN、MAX、SUM，还有其他一些。然而，当使用用户定义类型的时候，有时会遇到需要聚合计算的情况。目前还没有直接实现您自己的用户定义聚合函数的方法。本文提出了一种实现指定聚合运算的方法。该技术使用了内建的聚合函数 MAX 完成实际的聚合操作，并使用几个标量用户定义函数（UDF）的特性来满足聚合运算特殊的需要。我将使用复数的例子来解释和阐明这种技术。
您可以在数据库的表中管理复数。定义一个结构化的类型来封装复数，如 清单 1 所示。新的数据类型 Complex 在表 complexNumbers 中作为一个字段的类型使用。复数数据类型还提供了方法 add ，该方法允许两个复数相加，其结果是一个新的复数。构造函数 complex 以复数的实部和虚部作为输入参数，构造出一个可以存储到表中的新值。另外还定义了其他方法，出于篇幅原因在这里省略掉了。清单中最后的 INSERT 语句使用三行数据填充表，每一行包含一个不同的复数。
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。
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 ------------------------ ------------------------ +2.84000000000000E+001 +3.50000000000000E+000 1 record(s) selected.
本文其余部分将解释怎样实现函数 GetAggrResult 和 BuildComplexSum ，并说明它们怎样与内建 MAX 函数共同来产生最后结果。在提供有关函数交互的概览之后，我将进入两个函数的实现细节，然后描述这里所提出的用户定义聚合运算的一些限制。
请注意，复数决不是这里提出的技术的惟一用途。使用这种技术您还可以像 DB2 Spatial Extender [3] 中提供的“Union Aggregate”和“MBR Aggregate”一样来计算几何学中的交/并边界矩形。或者您可以完成任何其他用户定义聚合运算，可以实现一个加权 SUM 聚合函数，其中使用额外的一个参数指定每个相加的值的权值。还可以发现用户定义聚合的很多其他用途。
解决方案概览
对于用户定义聚合函数有两个主要问题：
- 首先是计算并跟踪中间结果。
- 其次，必须找到和返回最后结果，也就是最后的中间结果。
第一个问题，计算和跟踪中间结果很容易解决。DB2 UDB 提供的用户定义函数支持一个所谓的 scratchpad 来携带信息——比如说中间结果——将信息从对一个 UDF 的调用携带到下一个调用。在我们的例子中，scratchpad 是由函数 BuildComplexSum 使用的。
为了找到聚合运算的最后结果，我们使用递增的数字来标识每一个中间结果。第一个中间结果的标识符为 1，下一个为 2，接着是 3，依此类推。因此，标识符最大的结果就是最后结果。所以完成用户定义聚合运算的任务可以归纳为对标识符进行聚合运算的任务。DB2 内建聚合函数 MAX 可用于完成这个任务。为了提取最后结果，我们必须去除标识符并完成任何的最后转换或者必要的计算。由 UDF 实现的最后工作叫做 GetAggrResult 。
由于需要找出具有最大标识符的中间结果，以及将一个函数调用的返回结果传给下一个函数调用，因此所有带有标识符的中间结果需要在两个地方维护：
- 函数 BuildComplexSum 返回的每一条结果——每次计算返回一条结果。
- 它们维护在函数 BuildComplexSum 的 scratchpad 中。
图 1 说明了不同函数之间以及它们与 DB2 数据库引擎之间的交互。该图还给出了在聚合计算过程中所完成的不同步骤的顺序，以及这些不同的函数如何与 DB2 引擎协同工作。
图 1. 用户定义聚合的处理序列分析这些函数
我们已经提到过，中间结果需要由标量函数 BuildComplexSum 返回，而且它们还需要存储在 scratchpad 之中。注意，在 scratchpad 之中只需要最后的中间结果，所有前面得到的其他中间结果都不需要。这个从聚合运算的结果得来的简化过程可以迭代计算。例如，多个复数相加的方法是首先两个数相加，然后其结果再与第三个数相加，其结果再与下一个数相加，依此类推。所以我们只需要前一次的结果和下一个数就可以得到下一个结果，并且我们不需要知道任何更多的历史信息。
有了这些知识，我们就可以设计 BuildComplexSum 函数，定义中间结果表示在 scratchpad 中的格式，及其从 BuildComplexSum 函数返回之后由 DB2 内建 MAX 聚合函数处理的格式。
BuildComplexSum
