How can I create a custom aggregate function in MS SQL Server? An example would help a lot.
如何在MS SQL Server中创建自定义聚合函数?举个例子会有很大帮助。
1 个解决方案
#1
1
SQL Server 2000 doesn't officially support custom aggregate functions. However, I recently needed that functionality as well, and I found this article enlightening:
http://weblogs.sqlteam.com/jeffs/articles/1490.aspx
SQL Server 2000并没有正式支持自定义聚合函数。然而，我最近也需要这个功能，我发现这篇文章很有启发性:http://weblogs.sqlteam.com/jeffs/articles/1490.aspx
It's a bit hack-ish, though: it requires access to the sp_OA___ extended procedures.
不过，这有点像黑客:它需要访问sp_OA___扩展过程。
The summary is that you can simulate an aggregate function with a series of four wrapper functions, each of which performs one of the following tasks:
总结是，您可以使用一系列由4个包装器函数组成的聚合函数来模拟聚合函数，每个包装器函数执行以下任务之一:
- Create an ActiveX object that can hold state within the query. Call this before running the query.
- Do the actual aggregation using the ActiveX object.
- 使用ActiveX对象执行实际的聚合。
- Clear the ActiveX object state on GROUP BY boundries
- 通过boundries清除组上的ActiveX对象状态
- Destroy the object. Call this after running the query and during error handling.
- 破坏的对象。在运行查询和错误处理期间调用此函数。
- 创建可以在查询中保持状态的ActiveX对象。在运行查询之前调用它。使用ActiveX对象执行实际的聚合。通过boundries清除组上的ActiveX对象状态，销毁对象。在运行查询和错误处理期间调用此函数。
You then include items 2 and 3 in the select list for your query, and item 2 must also be wrapped in an existing no-effect aggregate function like MAX() or MIN(). You can also use this technique for cumulative functions to do things like row numbers.
然后，在查询的选择列表中包含项目2和3，并且项目2还必须包装在现有的无效果聚合函数中，比如MAX()或MIN()。您还可以使用这种技术来处理累积函数，比如行号。
Some of the comments suggest that the optimizer may try to negate the aggregation effects by optimizing away the calls in some circumstances, though I expect that would be a very rare case indeed. However, I found this question because I took those warnings seriously enough to continue searching for something better.
有些评论认为，优化器可能试图通过优化在某些情况下的调用来消除聚合效应，但我认为这确实是非常罕见的情况。然而，我发现了这个问题，因为我认真地对待这些警告，继续寻找更好的东西。
#1
1
SQL Server 2000 doesn't officially support custom aggregate functions. However, I recently needed that functionality as well, and I found this article enlightening:
http://weblogs.sqlteam.com/jeffs/articles/1490.aspx
SQL Server 2000并没有正式支持自定义聚合函数。然而，我最近也需要这个功能，我发现这篇文章很有启发性:http://weblogs.sqlteam.com/jeffs/articles/1490.aspx
It's a bit hack-ish, though: it requires access to the sp_OA___ extended procedures.
不过，这有点像黑客:它需要访问sp_OA___扩展过程。
The summary is that you can simulate an aggregate function with a series of four wrapper functions, each of which performs one of the following tasks:
总结是，您可以使用一系列由4个包装器函数组成的聚合函数来模拟聚合函数，每个包装器函数执行以下任务之一:
- Create an ActiveX object that can hold state within the query. Call this before running the query.
- Do the actual aggregation using the ActiveX object.
- 使用ActiveX对象执行实际的聚合。
- Clear the ActiveX object state on GROUP BY boundries
- 通过boundries清除组上的ActiveX对象状态
- Destroy the object. Call this after running the query and during error handling.
- 破坏的对象。在运行查询和错误处理期间调用此函数。
- 创建可以在查询中保持状态的ActiveX对象。在运行查询之前调用它。使用ActiveX对象执行实际的聚合。通过boundries清除组上的ActiveX对象状态，销毁对象。在运行查询和错误处理期间调用此函数。
You then include items 2 and 3 in the select list for your query, and item 2 must also be wrapped in an existing no-effect aggregate function like MAX() or MIN(). You can also use this technique for cumulative functions to do things like row numbers.
然后，在查询的选择列表中包含项目2和3，并且项目2还必须包装在现有的无效果聚合函数中，比如MAX()或MIN()。您还可以使用这种技术来处理累积函数，比如行号。
Some of the comments suggest that the optimizer may try to negate the aggregation effects by optimizing away the calls in some circumstances, though I expect that would be a very rare case indeed. However, I found this question because I took those warnings seriously enough to continue searching for something better.
有些评论认为，优化器可能试图通过优化在某些情况下的调用来消除聚合效应，但我认为这确实是非常罕见的情况。然而，我发现了这个问题，因为我认真地对待这些警告，继续寻找更好的东西。