db2中的用户定义聚合函数.doc

db2 中的用户定义聚合函数 作者:佚名 时间:2005-08-30 16:33 出处:互连网 责编:小渔Knut Stolze信息集成部门, IBM 德国2003 年 10 月 DB2 提供了几种内建的聚合 (或者字段)函数，但没有提供直接实现用户定义聚合运算的方式。本文提出了一种实现指定聚合运算的方法。简介DB2 Universal Database 支持提供了几种内建的聚合函数 1。这些内建函数包括 AVG、COUNT、MIN、MAX、SUM，还有其他一些。然而，当使用用户定义类型的时候，有时会遇到需要聚合计算的情况。目前还没有直接实现您自己的用户定义聚合函数的方法。本文提出了一种实现指定聚合运算的方法。该技术使用了内建的聚合函数 MAX 完成实际的聚合操作，并使用几个标量用户定义函数（UDF）的特性来满足聚合运算特殊的需要。我将使用复数的例子来解释和阐明这种技术。 您可以在数据库的表中管理复数。定义一个结构化的类型来封装复数，如 清单 1 所示。新的数据类型 Complex 在表 complexNumbers 中作为一个字段的类型使用。复数数据类型还提供了方法 add ，该方法允许两个复数相加，其结果是一个新的复数。构造函数 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 - - +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从清单 3 给出的例子查询中我们可以推出需要用在 DB2 数据库中注册 UDF 的 SQL 语句。 清单 4 给出了这个语句。请注意，出于篇幅原因，转换组ComplexTransform 的定义在这里省略掉了。请参阅 下载部分，在那里可以找到完整的 DDL。 清单 4. 注册 BuildComplexSum 函数的语句CREATE FUNCTION BuildComplexSum(number Complex) RETURNS VARCHAR(128) FOR BIT DATA SPECIFIC BuildComplexSum EXTERNAL NAME ComplexAggr.buildComplexSum LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NOT DETERMINISTIC NOT FENCED RETURNS NULL ON NULL INPUT NO SQL STATIC DISPATCH EXTERNAL ACTION SCRATCHPAD 200 FINAL CALL DISALLOW PARALLEL NO DBINFO TRANSFORM GROUP ComplexTransform 有趣的部分是函数本身的实现。它以一个结构化的类型作为输入，访问 scratchpad 得到前面的中间结果，计算新的中间结果，然后以一个使用二进制编码的标量值返回。而且，新的结果也存储在 scratchpad 中。 清单 5 给出了 Java 方法 buildComplexSum 的基本逻辑。 斜体 标出的语句用于维护在二进制编码的值之前的计数器。计数器在以后要用来在 DB2 引擎中排序以找到最后的中间结果。中间结果本身以 加粗字体标出。这些语句依赖于实际需要完成的的聚合运算，在这里它们只计算输入中提供的复数与前一个中间结果的和。如果有不同的聚合运算，比如说求平均值，则需要调整这些语句。余下的代码负责二进制编码/解码以及返回值和 scratchpad 的设置。 图 5. 计算所有中间结果的 Java 代码public void buildComplexSum( double real, double img, Blob intermResult) throws Exception / test for SQL NULLs in the input parameters and / the structured value itself if (isNull(1) | isNull(2) | isNull(4) return; / access the scratchpad and decode the previous / intermediate result stored there byte scratchpad = getScratchpad(); ByteArrayInputStream scratchIn = new ByteArrayInputStream(scratchpad); DataInputStream dataIn = new DataInputStream(scratchIn); / initialize variables int counter = 0; double scratchReal = 0.0; double scratchI = 0.0; switch (getCallType() case SQLUDF_FIRST_CALL: / initialize the entire scratchpad for (int i = 0; i scratchpad.length; i+) scratchpadi = 0x00; break; case SQLUDF_NORMAL_CALL: / readInt reads an integer in big-endian format counter = dataIn.readInt(); scratchReal = dataIn.readDouble(); scratchI = dataIn.readDouble(); break; default: / nothing to do in FINAL call return; / compute new intermediate result counter+; scratchReal += real; scratchI += img; / perform a binary encoding for new result, which is / also stored on the scratchpad ByteArrayOutputStream scratchOut = new ByteArrayOutputStream(); DataOutputStream dataOut = new DataOutputStream(scratchOut); dataOut.writeInt(counter); dataOut.writeDouble(scratchReal); dataOut.writeDouble(scratchI); / construct new scratchpad data and store it byte newScratchpad = scratchOut.toByteArray(); for (int i = 0; i newScratchpad.length; i+) scratchpadi = newScratchpadi; setScratchpad(scratchpad); / set output parameter for new intermediate result / (VARCHAR FOR BIT DATA is mapped to Blob class) intermResult = Lob.newBlob(); OutputStream intermOut = intermResult.getOutputStream(); intermOut.write(newScratchpad); set(3, intermResult); Java 代码现在可以直接使用“javac”编译器编译成 Java 字节码。产生的类文件需要复制到您的 DB2 实例的 sqllib/function/ 目录下。在完成类型创建和 BuildComplexSum 函数注册以后，就可以调用它来返回中间结果了。 清单 6 给出了一个快速检验 UDF 的功能是否正确的例子。 清单 6. 测试 BuildComplexSum 函数 SELECT BuildComplexSum(number) FROM complexNumbers 1 - x 00000001 0000000000000000 0000000000000000 x 00000002 4034666666666666 0000000000000000 x 00000003 403C666666666666 400C000000000000 3 record(s) selected. 从结果中可以看到，前四个字节（以 斜体 表示）包含计数器的递增值。接着的8个字节（以 加粗字体表示）存储中间结果的复数的实部，余下的8个字节是虚部，也是以 IEEE 754 格式表示的一个双精度值。 MAXDB2 聚合函数 MAX 用来执行实际的聚合运算以及确定最后的中间结果，最后的中间结果又编码最后结果。存储在所有中间结果中的计数器信息用于找出最后一个中间结果。由于计数器是递增的，所以最后的中间结果具有最大的计数器。在清单 6 给出的结果中可以看到，计数器信息是编码在最显眼的前四个字节中的。在 DB2 应用 MAX 函数进行排序的时候只有那四个字节才是重要的。 清单 7 说明了最后一个中间结果确实是编码后的计数器的值为（十六进制）0x00000003 的那一个。 清单 7. 查找最后的中间结果SELECT MAX(BuildComplexSum(number) FROM complexNumbers 1 - x00000003403C666666666666400C000000000000 1 record(s) selected. GetAggrResult既然最后的中间结果是经过二进制编码的，所以剩下的惟一任务就是构造出一个新的复数，也就是以 Complex 类型的值表示的最后结果。这个新的值就是聚合运算的最后结果。函数 GetAggrResult 就是负责这个任务的。 清单 8 给出了用于在数据库中注册 UDF 的 SQL 语句。 清单 8. 用于注册 GetAggrResult 函数的 SQL 语句CREATE FUNCTION GetAggrResult( intermResult VARCHAR(128) FOR BIT DATA) RETURNS Complex SPECIFIC GetAggrResult EXTERNAL NAME ComplexAggr.getAggregateResult LANGUAGE JAVA PARAMETER STYLE DB2GENERAL DETERMINISTIC NOT FENCED RETURNS NULL ON NULL INPUT NO SQL STATIC DISPATCH NO EXTERNAL ACTION NO SCRATCHPAD NO FINAL CALL ALLOW PARALLEL NO DBINFO TRANSFORM GROUP ComplexTransform 这个函数的实现很简单。它访问存储在中间结果中的信息，包括实部和虚部，然后只是简单地返回它们。这里不再需要计数器信息，因此可以将其丢弃。然后转换组 ComplexTransform 的 TO SQL 转换功能隐式地产生结构化的值。 清单 9 给出了这个函数的 Java 代码。 清单 9. 从最后的中间结果得到最后结果的 Java 代码public void getAggregateResult( Blob intermResult, double real, double img) throws Exception / test for SQL NULLs in the input parameter if (isNull(1) return; InputStream intermIn = intermResult.getInputStream(); DataInputStream dataIn = new DataInputStream(intermIn); / get data from intermediate result int counter = dataIn.readInt(); / not needed double intermReal = dataIn.readDouble(); double intermI = dataIn.readDouble(); / set output parameters set(2, intermReal); set(3, intermI); set(4); / null indicator for structured value 斜体 标出的语句提取实部和虚部，然后简单地返回它们。注意，您还必须为总体结构化值设置空指示器，这在 粗体标出的语句中完成。如果您遗漏了这个，DB2 就会为结构化的值返回一个 SQL null。 有了第二个函数的实现，我们现在就可以执行 清单 3 中的 SQL 语句并得到预期的结果。 清单 10再次给出了这个步骤。 清单 10. 用户定义聚合运行的结果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. 请注意，在查询中使用子选择语句只是为了有一个地方进行聚合计算（在子选择语句内部）。查询的外部部分的惟一作用是访问结构化类型的不同属性以及在单独的列中表现它们。如果您不需要从 DB2 返回结果到客户机，那么您可以根本不需要子选择语句 3。另外一个替代解决方法是使用转换函数将复数值转换为标量值，例如一个字符串表示。这个方法留给有兴趣的读者自己去实现。 消除限制我提出的实现用户定义聚合函数的方法有一些限制，用户和开发人员需要知道这些限制。在这里给出了比较重要的考虑，并且在可能的地方我会简要地给出消除这些限制的一个方案。一致的中间结果具有一致的中间结果是很重要的。也就是说，您必须保证没有对中间结果进行并行计算。否则，您能有两组中间结果，其中一组中的前三个数被聚合，而另外一组中余下的数被聚合。每一组的结果在其他组中都被遗漏，但是最后的中间结果需要组合所有的输入值。因此，函数 BuildComplexSum 必须使用语句 DISALLOW PARALLEL 和 EXTERNAL ACTION 声明。那样 DB2 将总会在同一主机系统上使用同一个进程来调用函数，并且几乎会一次就读入所有需要进入聚合的行。 根据聚合运算的不同，您可能还必须指定语句 NOT DETERMINISTIC。例如，考虑序列1、5、5的平均值的计算。如果 DB2 假设结果是确定的，它可能会优化语句执行并将第二次调用（第一个5）时计算出的相同结果用于第三次调用（第二个5）。然后平均值就会是 3 而不是正确的 5.5。使用语句 NOT DETERMINISTIC 告诉 DB2 对于相同的输入其结果可能会不同。内存密集型结果求复数的和的例子很简单。其目的是给出实现用户定义聚合运算的基本技术。这可能不能直接地应用到更加复杂的数据结构中。例如，如果您需要像 DB2 Spatial Extender 3 的 Union Aggregate 的实现一样来计算几个多边形的聚合的并集，那么在 scratchpad 中提供的内存区域以及为中间结果提供的内存区域就太小了。这样就需要另外一种跟踪中间结果的方法。 在调用一个 UDF 时通用的使用大块内存的方法是在 scratchpad 中存储一个指向那些区域的指针。指针只需要几个字节，但是它却可以引用几 MB 或者甚至几 GB 的大缓冲区。指针可以指向私有的或者共享的内存。如果一个指针存储在 scratchpad 中（以及中间结果中），那么就还需要一些附加信息。要成功地解除一个在位于 GetAggrResult 内部的 BuildComplexSum 函数中分配的指针的引用，就必须得保证满足以下条件： GetAggrResult 与 BuildComplexSum 要在同一主机上执行。 除非使用共享内存，否则 GetAggrResult 和 BuildComplexSum 要在同一进程中执行。 一个直接的后果是分区数据库（partitioned databases）不被聚合运算支持，因为不能保证 GetAggrResult 函数一定运行在同一个分区。 为了确保相同的主机和进程，您需要在中间结果中存储主机名和/或 IP 地址与进程 ID。 GetAggrResult 函数在试图解除指针的引用之前会检查主机名和进程 ID。如果使用共享内存，就不需要进程 ID，取而代之的关键是要找出正确的共享内存段。 当然另外一个选择是使用网络通信来访问和管理位于一台主机上的中央位置的中间结果。通信可以跨越机器的界限。分组支持方法 BuildComplexSum 和 GetAggrResult 并没有集成在 DB2 引擎中。也就是说，如果上下文发生变化，比如说在查询中使用 GROUP BY 子句的情况下，