问题描述
如何在MS SQL Server中创建自定义聚合函数？
How can I create a custom aggregate function in MS SQL Server? An example would help a lot.
推荐答案
SQL Server 2000并不正式支持自定义聚合函数。但是，最近我也需要该功能，并且发现这篇文章很有启发性：
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
虽然有点黑，但是：需要访问 sp_OA ___ 扩展过程。
It's a bit hack-ish, though: it requires access to the sp_OA___ extended procedures.
摘要是您可以模拟具有一系列四个包装函数的聚合函数，每个包装函数均执行以下任务之一：
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:
- 创建可以保存ActiveX对象查询中的状态。在运行查询之前，先调用 。
- 使用ActiveX对象执行实际的聚合。
- 清除GROUP BY边界上的ActiveX对象状态
- 销毁对象。在运行查询后以及在错误处理过程中，称为。
- 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.
- Clear the ActiveX object state on GROUP BY boundries
- Destroy the object. Call this after running the query and during error handling.
然后在查询的选择列表中包括项目2 和 3，并且项目2还必须包装在现有的无效集合函数（如MAX（）或MIN（））中。您还可以将这种技术用于累积函数来执行行号之类的操作。
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.
一些评论表明，优化器可能会尝试通过优化优化来消除聚合效应。在某些情况下会打电话，尽管我希望那确实是非常罕见的情况。但是，我发现了这个问题，因为我认真对待了这些警告，以继续寻找更好的东西。
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.
这篇关于MS SQL Server中的自定义聚合函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！